Question

Q.1] Write SQL statements based on the tennis database schema (practice homework 5) Get all the different town names from the PLAYERS table. 2. For each town, find the number of players. 3. For each team, get the team number, the number of matches that has been played 'for that team, and the total number of sets won. 4. For each team that is captained by a player resident in "Eltham", get the team number and the number of matches that has been plaved for that team 1. In this homework assignment, you will create tennis club database named "tennis" and write SQ statements to create database tables, load data to the tables, and run MySQL queries. Referential integrity constraints are not considered for this assignment. The tennis database consists of the following tables: PLAYERS, TEAMS, MATCHES, PENALTIES, COMMITTEE MEMBERS The tennis club database schema is shown in the following Figure. PENALTIES table PLAYERS table TEAMS table PAYMENTNO PLAYERNO PLAYERNO NAME PLAYERNO PAYMENT DATE INITIALS DIVISION AMOUNT BIRTH DATE (NULL) SEX JOINED STREET MATCHES table COMMITTEE MEMBER table HOUSENO (NULL) MATCHNGO PLAYERNO POSTCODE (NULL) TEAMNO BEGIN DATE TOWN PLAYERNO END DATE (NULL) PHONENO (NULL) WON POSITION (NULL) LEAGUENO (NULL) LOST A double-headed arrow at the side of a column (or combination of columns) indicates the primary key of each table based on our UoD (Universe of Discourse). PLAYERNO of PLAYERS TEAMNO of TEAMS MATCHNO of MATCHES PAYMENTNO of PENALTIES PLAYERNO plus BEGIN DATE of COMMITTEE MEMBERS There are several foreign keys as you can see from the schema diagram, but we do not consider referential integrity constraints for creating database tables for this assignment. In the next assignment, we will study how to create database and populate database tables with foreign key constraints.

Answer 1

--1 Distinct is key word helps to get unique records.

Select Distinct Town From Players;

--2 Group by helps to get num of players for each town.

Select Town,count(PlayerNo) As NumPlayer from Players Group by Town;

--3

Select teamno,count(matchno),sum(won) As SetsWon From Matches Group by teamno;

in above query im im assuming Won column will have num of sets match won. If no sets won then it will be zero.

--4

select teamNo,Count(MatchNo) As NumMatches From Matches Group by TeamNo Having PlayerNo IN (select playerno from players where town='Eltham' and playerno in (select playerno from commitee_member where position='captain'));

Using multiple subquery we are filtering data.