SQL Assignment Solutions CMSC 424 Database Design Fall 2007 Part A 1 List the names of all the players in the database select Player name from Player 2 List the names of all players who have ever been assigned a seed for any tournament doubles or singles select distinct p name from Player p Registration r PlayedIn pi where p pid r pid AND r registrnum pi registrnum AND pi seed is not null 3 List the pairs of players who played doubles tennis at Wimbledon in 2007 Do not include duplicate entries ex hpersonA personBi as well as hpersonB personAi select p1 name p2 name from player p1 player p2 registration r1 registration r2 playedin pi where p1 pid r1 pid AND p2 pid r2 pid AND r1 registrnum r2 registrnum AND r1 registrnum pi registrnum AND r1 pid r2 pid AND p1 pid p2 pid AND pi tid in select tid from tournament t where to char t startdate YYYY 2007 AND t ttype Doubles AND t name Wimbledon 4 List the names of all players who have lost to Roger Federer in the finals of any tournament as well as the name of the tournament they lost in Include results only for singles tennis select p name t name from registration rroger registration r match m matchresults mr select p pid pid from Player p where name Roger Federer roger tournament t player p where rroger pid roger pid AND m mid mr mid AND mr winner rroger registrnum AND rroger registrnum m registrnum1 AND r registrnum m registrnum2 OR rroger registrnum m registrnum2 AND r registrnum m registrnum1 AND t tid m tid AND t numrounds m round AND t ttype Singles AND p pid r pid 1 Note that we do not have to actually check for rroger registrnum equal to one of the match registration numbers because we assume the winner is always one of the registration numbers but we do it here for good practice If we chose to omit the check we would need to constrain rroger registrnum r registrnum 5 For all final round single matches list the winner and loser of matches that were between two seeded players as well as their seeds Modify the titles of the columns to be something useful like WinnerName WinnerSeed LoserName LoserSeed select winner playername winnername winner seed winnerseed loser playername losername loser seed loserseed from match m tournament t matchresults mr select r registrnum pi seed seed p name playerName from player p registration r playedin pi where p pid r pid AND r registrnum pi registrnum AND pi seed is not null winner select r registrnum pi seed seed p name playerName from player p registration r playedin pi where p pid r pid AND r registrnum pi registrnum AND pi seed is not null loser where m tid t tid AND t ttype Singles AND m round t numrounds AND m mid mr mid AND m registrnum1 winner registrnum OR m registrnum2 winner registrnum AND m registrnum1 loser registrnum OR m registrnum2 loser registrnum AND winner registrnum loser registrnum AND mr winner winner registrnum See note for question 4 similarly here for the two registration relations 6 List the names of all US players who have participated in at least two tournaments in 2007 Do not use any aggregating functions for this problem select distinct usplayers name from tournament t1 tournament t2 registration r1 registration r2 playedin pi1 playedin pi2 select p pid pid p name from Player p CountryCodes cc where p ccode cc code AND cc country United States usplayers where t1 tid pi1 tid AND pi1 registrnum r1 registrnum AND t2 tid pi2 tid AND pi2 registrnum r2 registrnum AND r1 pid usplayers pid AND r2 pid usplayers pid AND r1 registrnum r2 registrnum AND to char t1 startdate YYYY 2007 AND to char t2 startdate YYYY 2007 7 List all tournaments having more than 5 rounds Print the name of the tournament the tournament type the start and end dates and the number of rounds select t name t startdate t enddate t ttype t numrounds from tournament t where t numrounds 5 2 8 List all doubles matches that were won because one of the teams retired Include the winner s names the loser s names the tournament name the year of the tournament and the round number of the match select p1 name winner1 p2 name winner2 p3 name loser1 p4 name loser2 matchinfo name tournament name matchinfo year matchinfo round roundnum from player p1 registration r1 playedin pi1 player p2 registration r2 playedin pi2 player p3 registration r3 playedin pi3 player p4 registration r4 playedin pi4 select t name to char t startdate YYYY year m round round m registrnum1 rn1 m registrnum2 rn2 mr winner winner from tournament t match m retiredmatch rm matchresults mr where t tid m tid AND m mid rm mid AND t ttype Doubles AND mr mid m mid matchinfo where p1 pid p2 pid AND p3 pid p4 pid AND p1 pid r1 pid AND r1 registrnum pi1 registrnum AND r1 registrnum matchinfo winner AND p2 pid r2 pid AND r2 registrnum pi2 registrnum AND r2 registrnum matchinfo winner AND p3 pid r3 pid AND r3 registrnum pi3 registrnum AND r3 registrnum matchinfo winner AND r3 registrnum matchinfo rn1 OR r3 registrnum matchinfo rn2 AND p4 pid r4 pid AND r4 registrnum pi4 registrnum AND r4 registrnum matchinfo winner AND r4 registrnum matchinfo rn1 OR r4 registrnum matchinfo rn2 Following the notes from questions 4 and 5 we do the suggested alternative in this question 9 Find all singles matches where the loser retired after playing at least one complete set Include the winner s name the loser s name the tournament name the year of the tournament and the round number of the match select p1 name winner p2 name loser matchinfo tname matchinfo year matchinfo roundnum from player p1 registration r1 player p2 registration r2 select t name tname to char t startdate YYYY year m round roundnum m registrnum1 rn1 m registrnum2 rn2 mr winner winner from match m tournament t retiredmatch rm matchresults mr where m tid t tid AND rm mid m mid AND t ttype Singles AND mr mid m mid AND mr numsets 1 matchinfo where p1 pid r1 pid AND r1 registrnum matchinfo winner AND p2 pid r2 pid AND r2 registrnum matchinfo winner AND r2 registrnum matchinfo rn1 OR r2 registrnum matchinfo rn2 10 For all tournaments in the database list the name tournament type surface type and the number of rounds it has Sort the results in descending order by the number of rounds 3 select t name t ttype t surface t numrounds from tournament t order by t numrounds desc 11 List the names tournament types and lengths in days of all tournaments that were longer than one week select t name t ttype t enddate t startdate length from tournament t where t enddate t startdate 7 12 List the names of all male German
View Full Document
Unlocking...