Question

1.Which players have lost more matches than the average number of losses?   No duplicates should be listed.   Order by player number.   Insert your screen shot here.

2.How many players from each town served on the committee in any capacity? Display the town as ‘Town’ and the number served as ‘Committee Service’.   Insert your screenshot here.

3.How many members come from each town? Display the town as ‘Town’ and the number of members as ‘Number’. Insert your screenshot here.

4.Who has served on the committee more than once? Display the player number as ‘Number’, concatenate the player initial and name as ‘Name’, town as ‘Town’, and number served as ‘Terms’. Insert your screenshot here.

Below I will include the tennis database

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

CREATE and OPEN the TENNIS Base

******************************************************************* */

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

Once the required tables are created and data are inserted, in order to find the penalty details based on gender, we will have to join the tables "players" and "Penalties"

The table "players" contains the column "gender" and the table "Penalties" contains the details of penalty amounts of the players.

Points to remember:

1. To maintain the uniqueness of rows and check the gender, we join both the tables based on the condition of the column "playerno" as it is the primary key in the table "players" and reference key in the table "Penalties".

Let us first fetch the rows required to obtain our result. The below query will join the tables and fetch all the records that are involved in this case for us to get our desired output

select
pl.playerno,
amount,
gender
from
players pl join Penalties pn
on pn.playerno = pl.playerno;

The result will be,

Result: Number of Records: 8 playerno amount gender 100 75

2. From the above result, we have an overall idea of what our desired output should be.

Now, since we need the details based on genders, we will have to group the results based on the column "gender"

3. Alias names can be given to columns as required using the "AS" keyword and mentioning the names next to it and usually it is not advisable to include spaces in alias names, but since we have space, it is better to mention them inside " " as there may be chances of getting system errors if the alias name matches with any of SQL Commands.

For example, "Count" is a SQL command but we have to use it as alias name for the column "Penalty Count"

Keeping these in mind and selecting the desired columns with mentioned alias names, the final SQL query can be constructed as follows:

select
gender as Gender,
count(amount) as "Penalty Count",
sum(amount) as "Penalty Sum",
avg(amount) as "Penalty Average",
min(amount) as "Minimum Penalty",
max(amount) as "Maximum Penalty"
from
players pl join Penalties pn
on pn.playerno = pl.playerno
group by gender;

Screenshot of final output attached below:

Result: Number of Records: 2 Gender Penalty Count Penalty Sum Penalt Penalty Average Minimum Penalty Maximum Penalty 250 62.5