Question

6. List the players that have either won more than 2 matches or lost more than 2 matches. List the player number, name, initials, won, and lost. Insert your snip here.

7. Which players are captains of a team and have ever served as a committee members? Display the player number, player name, initials, team number, and committee member position. This will require 3 tables. Insert your snip here.

8. Using a subquery, have any other players been penalized the same amount as player 8? Let MySQL do the work for you. Do not explicitly query for $25. Display the player number, player name, initials, and amount. Insert your snip here.

9. Who has played more than 2 matches? Display the player number as ‘Number’, concatenate the player name (initials, space, name) as ‘Name’, and the number of matches played as ‘Number of Matches’. Insert your snip here.

10. Sum the number of match wins and losses by player number. Display the player number as ‘Number’, concatenate the player name (initials, space, name) as ‘Name’, wins as ‘Total Wins’, and losses and ‘Total Losses’. Insert your snip here.

***********************************************************************************************************************************************

***********************************************************************************************************************************************

***********************************************************************************************************************************************

Create database tennis;

USE tennis;

#--Create table players and fill it--------------------------

Create table players
(
playerno   int       not null    primary key,
name       varchar(15)   not null,
initials   varchar(3),
birth_date   date,
gender       char(1),
joined       int   not null,
street       varchar(15)   not null,
houseno       varchar(4),
zip       char(6),
town       varchar(10)   not null,
phoneno       char(10),
leagueno   char(4)
);

Insert into players values
(2,'Everett','R','1988-01-09','M',2000,'Stoney Road','43','3575NH','Stratford','070-237893','2411'),
(6,'Paramenter','R','1984-06-25','M',2002,'Haseltine Lane','80','1234KK','Stratford','070-476547','8467'),
(7,'Wise','GWS','1983-05-11','M',2006,'Edgecombe Way','39','9758VB','Stratford','070-347689',Null),
(8,'Newcastle','B','1982-07-08','F',2005,'Station Road','4','6584RO','Inglewood','070-458458','2983'),
(27,'Collins','DD','1990-05-10','F',2008,'Long Drive','804','8457DK','Eltham','079-234857','2513'),
(28,'Collins','C','1983-06-22','F',2008,'Old Main 28','10','1294QK','Midhurst','071-659599',Null),
(39,'Bishop','D','1986-10-29','M',2005,'Eaton Square','78','9629CD','Stratford','070-393435',Null),
(44,'Baker','E','1983-09-01','M',2010,'Lewis Street','23','4444LJ','Inglewood','070-368753','1124'),
(57,'Brown','M','1981-08-17','M',2007,'Edgecombe Way','16','4377CB','Stratford','070-473458','6409'),
(83,'Hope','PK','1976-11-11','M',2009,'Magdalene Road','16A','1812UP','Stratford','070-353548','1608'),
(94,'Miller','P','1993-05-14','M',2013,'High Street','33A','5746OP','Douglas','070-867564',Null),
(100,'Parmenter','P','1983-02-28','M',2012,'Haseltine Lane','80','1234KK','Stratford','070-494593','6524'),
(104,'Moorman','D','1990-05-10','F',2014,'Stout Street','65','9437AO','Eltham','079-987571','7060'),
(112,'Bailey','IP','1983-10-01','F',2014,'Vixen Road','8','6392LK','Plymouth','010-548745','1319');

#--Create the table committee_members and fill it--------------------

Create table committee_members
(
playerno   int   not null,
begin_date   date   not null,
end_date   date,
position   varchar(20),
primary key(playerno, begin_date)
);

Insert into committee_members values
(2,'2010-01-01','2012-12-31','Chairman'),
(2,'2014-01-01',Null,'General Member'),
(6,'2010-01-01','2010-12-31','Secretary'),
(6,'2011-01-01','2012-12-31','General Member'),
(6,'2012-01-01','2013-12-31','Treasurer'),
(6,'2013-01-01',Null,'Chairman'),
(8,'2010-01-01','2010-12-31','Treasurer'),
(8,'2011-01-01','2011-12-31','Secretary'),
(8,'2013-01-01','2013-12-31','General Member'),
(8,'2014-01-01',Null,'General Member'),
(27,'2010-01-01','2010-12-31','General Member'),
(27,'2011-01-01','2011-12-31','Treasurer'),
(27,'2013-01-01','2013-12-31','Treasurer'),
(57,'2012-01-01','2012-12-31','Secretary'),
(94,'2014-01-01',Null,'Treasurer'),
(112,'2012-01-01','2012-12-31','General Member'),
(112,'2014-01-01',Null,'Secretary');

#--Create the table matches and Fill it-------------------

Create table matches
(
matchno       int   not null   Primary Key,
teamno       int   not null   references teams(teamno),
playerno   int   not null   references players(playerno),
won       int,
lost       int
);

Insert into matches values
(1,1,6,3,1),
(2,1,6,2,3),
(3,1,6,3,0),
(4,1,44,3,2),
(5,1,83,0,3),
(6,1,2,1,3),
(7,1,57,3,0),
(8,1,8,0,3),
(9,2,27,3,2),
(10,2,104,3,2),
(11,2,112,2,3),
(12,2,112,1,3),
(13,2,8,0,3);

#--Create Table Penalties and Fill it-------------------------------------

create table Penalties
(
paymentno   int   not null   Primary Key,
playerno   int   not null   references players(playerno),
payment_Date   date   not null,
amount   decimal(10,2)   not null
);

Insert into Penalties values
(1,6,'2010-12-08',100.00),
(2,44,'2011-05-05',75.00),
(3,27,'2013-09-10',100.00),
(4,104,'2014-07-08',50.00),
(5,44,'2010-12-08',25.00),
(6,8,'2010-12-08' ,25.00),
(7,44,'2012-12-30',30.00),
(8,27,'2014-08-12',75.00);

#--Create Table Teams and Fill it----------------------------------------------

Create table teams
(
teamno   int   Primary Key   Not Null,
playerno   int   Not Null   references players(playerno),
division   varchar(6)
);

Insert into teams values
(1,6,'first'),
(2,27,'second');

/* ********************************************************************************************
End of loading the database
******************************************************************************************** */

Answer 1

SQL query is in the order of the question asked

Query1

select p.playerno, p.name, p.initials, m.won, m.lost

from players as p , matches as m

where m.playerno = p.playerno and (m.won >2 or m.lost >3)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Query2

select p.playerno, p.name, p.initials, t.teamno, c.position

from players as p , teams as t, committee_members as c

where t.playerno = p.playerno and c.playerno = p.playerno;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Query3

select p.playerno, p.name, p.initials, pen.amount from players as p,

(select * from Penalties where amount = (select amount from Penalties where playerno=8) and playerno !=8) as pen

where p.playerno in (select playerno from Penalties where amount =

(select amount from Penalties where playerno=8) and playerno !=8)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Query4

select p.playerno as "Number" , CONCAT(p.initials, ' ', p.name) as "Name" , m.sum as "Number of Matches" from players as p ,
(select playerno, sum(won) + sum(lost) as "sum" from matches group by playerno having sum(won)+ sum(lost) >2) as m
where p.playerno = m.playerno;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Query5

select p.playerno as "Number" , CONCAT(p.initials, ' ', p.name) as "Name", m.won as "Total Wins", m.lost as "Total Losses"

from players as p ,

(select playerno, sum(won) as "won", sum(lost) as "lost" from matches group by playerno) as m

where m              .playerno = p.playerno