ORACLE

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 players who registered for the 2007 Australian Open

singles.

select distinct germans.name

from registration r, playedin pi, tournament t,

(select p.pid pid, p.name name

from player p, countrycodes cc

where p.ccode = cc.code AND

p.gender = ’M’ AND cc.country = ’Germany’) germans

where germans.pid = r.pid AND r.registrnum = pi.registrnum AND

pi.tid = t.tid AND t.name = ’Australian Open’ AND

t.ttype = ’Singles’ AND to_char(t.startdate,’YYYY’) = ’2007’

Part B

1. For all singles quarterfinal, semifinal, and final round matches that only took 3 sets, list

the Tournament Name, Year, Winner of the Match, and the Match score (For the match

score, print two columns per set, displaying the number of games each player won. Call these

columns something meaningful, and ignore any tiebreaker results.).

select t.name, to_char(t.startdate, ’YYYY’) year, p.name winner,

ss1.winnergames Set1Win, ss1.losergames Set1Lose,

ss2.winnergames Set2Win, ss2.losergames Set2Lose,

ss3.winnergames Set3Win, ss3.losergames Set3Lose

from Tournament t, Match m, MatchResults mr,

Player p, Registration r,

SetScore ss1, SetScore ss2, SetScore ss3

where t.ttype = ’Singles’ AND t.tid = m.tid AND

m.mid = mr.mid AND mr.numsets = 3 AND

m.round >= t.numrounds - 2 AND

p.pid = r.pid AND r.registrnum = mr.winner AND

ss1.mid = m.mid AND ss1.setnum = 1 AND

ss2.mid = m.mid AND ss2.setnum = 2 AND

ss3.mid = m.mid AND ss3.setnum = 3

4

2. List the names of all triples of players that have played doubles with each other. Only list

each triple once.

select distinct p1.name, p2.name, p3.name

from player p1, player p2, player p3, registration r1a, registration r1b,

registration r2a, registration r2b, registration r3a, registration r3b

where r1a.pid = r1b.pid AND r2a.pid = r2b.pid AND r3a.pid = r3b.pid AND

r1a.pid = p1.pid AND r2a.pid = p2.pid AND r3a.pid = p3.pid AND

r1a.registrnum = r2b.registrnum AND

r2a.registrnum = r3b.registrnum AND

r3a.registrnum = r1b.registrnum AND

p1.pid < p2.pid AND p2.pid < p3.pid

3. List the names of all pairs of players that have played against each other in both singles and

doubles.

select distinct p1.name, p2.name

from Match m_singles, Tournament t_singles,

Match m_doubles, Tournament t_doubles,

Player p1, Registration r1s, Registration r1d,

Player p2, Registration r2s, Registration r2d

where m_singles.tid = t_singles.tid AND t_singles.ttype = ’Singles’ AND

m_doubles.tid = t_doubles.tid AND t_doubles.ttype = ’Doubles’ AND

r1s.registrnum = m_singles.registrnum1 AND p1.pid = r1s.pid AND

r2s.registrnum = m_singles.registrnum2 AND p2.pid = r2s.pid AND

(r1d.registrnum = m_doubles.registrnum1 OR

r1d.registrnum = m_doubles.registrnum2) AND

(r2d.registrnum = m_doubles.registrnum2 OR

r2d.registrnum = m_doubles.registrnum1) AND

p1.pid = r1d.pid AND p2.pid = r2d.pid AND p1.pid != p2.pid

4. Find all singles matches won by James Blake containing a love set (i.e the score of a set was

either 6-0 or 0-6). Print the name of the tournament, the year of the tournament, the round

of the tournament, and the name of the player James Blake defeated.

select winning_matches.tname, winning_matches.tyear,

m.round, loser.name

from Player loser, registration rloser, Match m,

(select t.name tname, to_char(t.startdate,’YYYY’) tyear, m.mid mid

from Player blake, Registration r, Match m, Tournament t,

MatchResults mr, SetScore ss

where blake.name = ’James Blake’ AND r.pid = blake.pid AND

r.registrnum = mr.winner AND mr.mid = m.mid AND

t.tid = m.tid AND ss.mid = m.mid AND t.ttype = ’Singles’ AND

((ss.winnergames = 0 AND ss.losergames = 6) OR

(ss.winnergames = 6 AND ss.losergames = 0))) winning_matches

where winning_matches.mid = m.mid AND rloser.pid = loser.pid AND

(rloser.registrnum = m.registrnum1 OR

rloser.registrnum = m.registrnum2) AND

loser.name != ’James Blake’

5

5. List the names of all partners of a player in doubles tennis who also was seeded in a singles

tournament at some point. This means that we want all players x who have partners y where

partner y was seeded in men’s singles.

select distinct pairs.name2

from player p, registration r, playedin pi, tournament t,

(select p1.pid pid1, p2.name name2

from player p1, player p2,

registration r1, registration r2,

playedin pi, tournament t

where

p1.pid != p2.pid AND

p1.pid = r1.pid AND r1.registrnum = pi.registrnum AND

p2.pid = r2.pid AND r2.registrnum = pi.registrnum AND

pi.tid = t.tid AND t.ttype = ’Doubles’) pairs

where

p.pid = pairs.pid1 AND r.pid = p.pid AND pi.registrnum = r.registrnum AND

t.tid = pi.tid AND t.ttype = ’Singles’ AND pi.seed is not null

6. List the name of the player who has registered for the most tournaments, as well as the

number of tournaments he has registered for. NOTE: If a player has registered for both

singles and doubles play in a particular tournament, this counts as two tournaments.

select p.name, count(r.registrnum) registered_tournaments

from Player p, Registration r

where p.pid not in

(select count1.pid

from

(select p.pid pid, count(r.registrnum) numreg

from Registration r, player p

where r.pid = p.pid

group by p.pid) count1,

(select p.pid, count(r.registrnum) numreg

from Registration r, player p

where r.pid = p.pid

group by p.pid) count2

where count1.numreg < count2.numreg)

AND p.pid = r.pid

group by p.name

7. List the names of all Russian, Chilean, and US players Tommy Haas has played against.

Include results from both doubles and singles matches.

select distinct p.name

from CountryCodes cc, Player p, Registration r,

Player haas, Registration haas_r, Match m

where haas.name = ’Tommy Haas’ AND haas_r.pid = haas.pid AND

(haas_r.registrnum = m.registrnum1 OR

6

haas_r.registrnum = m.registrnum2) AND

(r.registrnum = m.registrnum1 OR r.registrnum = m.registrnum2) AND

r.pid = p.pid AND p.pid != haas.pid AND p.ccode = cc.code AND

(cc.country = ’United States’ OR cc.country = ’Chile’ OR

cc.country = ’Russia’)

8. List the names of all players who have made it to at least the quarter finals of the four Grand

Slam tournaments (Wimbledon, US Open, French Open, and Australian Open) in 2007 (they

could have made it to the finals in either doubles or singles for each tournament considered).

select distinct p.name

from Tournament wimt, Tournament ust,

Tournament frt, Tournament aut,

Player p, Registration wimr, Registration usr,

Registration frr, Registration aur,

Match wimm, Match usm, Match frm, Match aum

where wimt.name = ’Wimbledon’ AND ust.name = ’US Open’ AND

frt.name = ’French Open’ AND aut.name = ’Australian Open’ AND

p.pid = wimr.pid AND p.pid = usr.pid AND

p.pid = frr.pid AND p.pid = aur.pid AND

(wimr.registrnum = wimm.registrnum1 OR

wimr.registrnum = wimm.registrnum2) AND

(usr.registrnum = usm.registrnum1 OR

usr.registrnum = usm.registrnum2) AND

(frr.registrnum = frm.registrnum1 OR

frr.registrnum = frm.registrnum2) AND

(aur.registrnum = aum.registrnum1 OR

aur.registrnum = aum.registrnum2) AND

wimm.tid = wimt.tid AND usm.tid = ust.tid AND

frm.tid = frt.tid AND aum.tid = aut.tid AND

wimm.round >= wimt.numrounds - 2 AND

usm.round >= ust.numrounds - 2 AND

frm.round >= frt.numrounds - 2 AND

aum.round >= aut.numrounds - 2