SQL Assignment SolutionsCMSC 424 - Database DesignFall 2007Part A1. List the names of all the players in the database.select Player.namefrom Player2. List the names of all players who have ever been assigned a seed for any tournament (doublesor singles).select distinct p.namefrom Player p, Registration r, PlayedIn piwhere p.pid = r.pid AND r.registrnum = pi.registrnum AND pi.seed is not null3. List the pairs of players who played doubles tennis at Wimbledon in 2007. Do not includeduplicate entries (ex: hpersonA, personBi as well as hpersonB, personAi).select p1.name, p2.namefrom player p1, player p2, registration r1, registration r2, playedin piwhere p1.pid = r1.pid AND p2.pid = r2.pid AND r1.registrnum = r2.registrnum ANDr1.registrnum = pi.registrnum AND r1.pid != r2.pid AND p1.pid < p2.pid ANDpi.tid in (select tidfrom tournament twhere to_char(t.startdate, ’YYYY’) = ’2007’ ANDt.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.namefrom registration rroger, registration r, match m, matchresults mr,(select p.pid pidfrom Player pwhere name=’Roger Federer’) roger, tournament t, player pwhere rroger.pid = roger.pid AND m.mid = mr.mid ANDmr.winner = rroger.registrnum AND( (rroger.registrnum = m.registrnum1 AND r.registrnum = m.registrnum2) OR(rroger.registrnum = m.registrnum2 AND r.registrnum = m.registrnum1) ) ANDt.tid = m.tid AND t.numrounds = m.round AND t.ttype = ’Singles’ ANDp.pid = r.pid1*** Note that we do not have to actually check for rroger.registrnum equal to one of thematch registration numbers, because we assume the winner is always one of the registrationnumbers, but we do it here for good practice. If we chose to omit the check, we would needto constrain rroger.registrnum != r.registrnum5. For all final round single matches, list the winner and loser of matches that were betweentwo seeded players, as well as their seeds. Modify the titles of the columns to be somethinguseful, like WinnerName, WinnerSeed, LoserName, LoserSeed.select winner.playername winnername, winner.seed winnerseed,loser.playername losername, loser.seed loserseedfrom match m, tournament t, matchresults mr,(select r.registrnum, pi.seed seed, p.name playerNamefrom player p, registration r, playedin piwhere p.pid = r.pid AND r.registrnum = pi.registrnum ANDpi.seed is not null) winner,(select r.registrnum, pi.seed seed, p.name playerNamefrom player p, registration r, playedin piwhere p.pid = r.pid AND r.registrnum = pi.registrnum ANDpi.seed is not null) loserwhere m.tid = t.tid AND t.ttype = ’Singles’ AND m.round = t.numrounds ANDm.mid = mr.mid AND(m.registrnum1 = winner.registrnum OR m.registrnum2 = winner.registrnum) AND(m.registrnum1 = loser.registrnum OR m.registrnum2 = loser.registrnum) ANDwinner.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.namefrom tournament t1, tournament t2, registration r1, registration r2,playedin pi1, playedin pi2,(select p.pid pid, p.namefrom Player p, CountryCodes ccwhere p.ccode = cc.code AND cc.country = ’United States’) usplayerswhere t1.tid = pi1.tid AND pi1.registrnum = r1.registrnum ANDt2.tid = pi2.tid AND pi2.registrnum = r2.registrnum ANDr1.pid = usplayers.pid AND r2.pid = usplayers.pid ANDr1.registrnum != r2.registrnum ANDto_char(t1.startdate,’YYYY’) = ’2007’ ANDto_char(t2.startdate,’YYYY’) = ’2007’7. List all tournaments having more than 5 rounds. Print the name of the tournament, thetournament type, the start and end dates, and the number of rounds.select t.name, t.startdate, t.enddate, t.ttype, t.numroundsfrom tournament twhere t.numrounds > 528. List all doubles matches that were won because one of the teams retired. Include the winner’snames, the loser’s names, the tournament name, the year of the tournament, and the roundnumber of the match.select p1.name winner1, p2.name winner2, p3.name loser1, p4.name loser2,matchinfo.name tournament_name, matchinfo.year, matchinfo.round roundnumfrom 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 winnerfrom tournament t, match m, retiredmatch rm, matchresults mrwhere t.tid = m.tid AND m.mid = rm.mid AND t.ttype = ’Doubles’ ANDmr.mid = m.mid) matchinfowhere p1.pid < p2.pid AND p3.pid < p4.pid ANDp1.pid = r1.pid AND r1.registrnum = pi1.registrnum ANDr1.registrnum = matchinfo.winner ANDp2.pid = r2.pid AND r2.registrnum = pi2.registrnum ANDr2.registrnum = matchinfo.winner ANDp3.pid = r3.pid AND r3.registrnum = pi3.registrnum ANDr3.registrnum != matchinfo.winner AND(r3.registrnum = matchinfo.rn1 OR r3.registrnum = matchinfo.rn2) ANDp4.pid = r4.pid AND r4.registrnum = pi4.registrnum ANDr4.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 thisquestion.9. Find all singles matches where the loser retired after playing at least one complete set. Includethe winner’s name, the loser’s name, the tournament name, the year of the tournament, andthe round number of the match.select p1.name winner, p2.name loser, matchinfo.tname,matchinfo.year, matchinfo.roundnumfrom 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 winnerfrom match m, tournament t, retiredmatch rm, matchresults mrwhere m.tid = t.tid AND rm.mid = m.mid AND t.ttype = ’Singles’ ANDmr.mid = m.mid AND mr.numsets > 1) matchinfowhere p1.pid = r1.pid AND r1.registrnum = matchinfo.winner ANDp2.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 thenumber of rounds it has. Sort the results in descending order by the
View Full Document
Unlocking...