· 9 years ago · Sep 29, 2016, 05:12 AM
11.a) Creating the MATCH table:
2
3create table MATCH
4(Draw varchar(6) not null,
5 No integer not null check (No > 0),
6 Day integer not null check (Day between 1 and 13),
7 Player1 integer not null references player,
8 Player2 integer not null references player,
9 Duration /* in minutes */ integer check (Duration between 0 and 400),
10 Winner integer references player,
11 primary key (draw,no),
12 constraint check_player check (winner = PLayer1 or winner = Player2));
13
14b) Populating the table
15
16select count(*) from match;
17127 tuples
18
19
20c) Creating the JUDGING table:
21JUDGING (Draw, Match, Official, Role)
22
23create table JUDGING
24(Draw varchar(6) not null,
25 Match integer not null,
26 Official integer not null references Official,
27 Role varchar(12) not null
28 check (Role in ('chair umpire','line umpire','referee')),
29 primary key (Draw, Match, Official),
30 foreign key (Draw, Match) references MATCH);
31
32
33
342.a) Find country with the highest number of seed players.
35
36Select country,count(*) as No_seeds
37from player
38where SeedNo is not null
39group by country
40having count(*) >= all (select count(*)
41 from player
42 where SeedNo is not null
43 group by country);
44
45Result: Spain, with 5 seed players
46
47
48b) Find countries with no female officials.
49
50select distinct country
51from official O1
52where not exists (select * from official O2
53 where gender = 'f' and O2.country = O1.country);
54
55
56(select distinct country from official)
57minus
58(select distinct country from official
59 where gender='f');
60
61
62Result: 12 tuples (Argentina, Brazil, Germany, Iran, Ireland, Morocco, Portugal, Spain,
63Sweden, Switzerland, Trinidad and Tobago.
64
65
662.c) Find seed players who lost in the first draw.
67
68(select Name
69 from player where seedNo is not null)
70minus
71(select Name
72 from player join match on id = Winner
73 where seedno is not null and draw = 'first');
74
75Result:
76Guillermo Garcia-Lopez
77Jack Sock
78Pablo Cuevas
79Tommy Rebredo
80
81
82Alternative solution:
83
84select Name
85 from player, match
86 where seedNo is not null and draw='first' and
87 (player1 = id or player2=id) and winner <> id;
88
89
902.d) (5 marks) Find matches played by players representing the same country.
91
92select Draw, No, Day, P1.Name, P2.Name
93from match, player P1, Player p2
94where Player1=P1.id and player2=p2.id and
95 p1.country = p2.country;
96
97select Draw, No
98from match
99where (select distinct country from player where id=player1) =
100 (select distinct country from player where id = player2);
101
102
103Result: 5 matches
104Pablo Andujar vs Guillermo Garcia-LOpez in first draw (match no 46)
105Kenny De Schepper vs Richard Gasquet in the second draw (match no 14)
106Gael Monfils vs Adrian Mannarino in the second draw (match no 24)
107Samuel Groth vs James Duckworth in the second draw (match no 32)
108Gael Monfils vs Gilles Simon in the third draw (match no 10)
109
110
1112.e) The MATCH table does not contain the duration and the result for the final match
112 played on Day 13, when Novak Djokovic beat Roger Federer in a match that lasted
113 176 minutes. Write a single SQL statement to update the table to add the duration
114 and the winner.
115
116update match
117set duration=176,
118 winner = (select id from player where name='Novak Djokovic')
119where draw='final';
120
121
122
1233.a) Create the PlayerStat view, which shows the id and name of each player, and
124 the number of matches he has played at the championship.
125
126create view PlayerStat
127as select id, Name, count(*) as Matches
128 from player, match
129 where (player1 = id or player2 = id)
130 group by Id, Name;
131
132
133
1343.b) Using the view, find players who were eliminated in the second draw.
135
136select Id,Name
137from PlayerStat
138where Matches = 2;
139
140Result: 32
141
142
143
1443.c) Create a trigger for the JUDGING table which whether inserts to the
145 table satisfy the rules specified in question 1.c).
146
147create or replace trigger PREVENT_JUDGING
148before insert on judging
149for each row
150when (new.role in ('referee','chair umpire'))
151 declare
152 RoleExists integer;
153 begin
154 select count(*) into RoleExists
155 from judging
156 where draw=:new.draw and match=:new.match and role=:new.role;
157
158 if roleExists = 1 then
159 raise_application_error (num=> -20055,
160 msg=> 'There can only be one chair umpire or a referee per match!');
161 end if;
162end;
163/
164
165Showing that the trigger works:
166
167insert into judging
168values('first',1,1,'chair umpire');
1691 row created.
170
171insert into judging
172values('first',1,2,'chair umpire');
173The statement is not executed, as there is already a chair umpire for that match.
174
175insert into judging
176values('first',1,2,'line umpire');
1771 row created.
178
179insert into judging
180values('first',1,2,'referee');
181Unique constraint violated (an official can only appear once per match)
182
183insert into judging
184values('first',1,3,'referee');
1851 row created.
186
187insert into judging
188values('first',1,4,'referee');
189The statement is not executed, as there is already a referee for that match.
190
191insert into judging
192values('first',1,4,'line umpire');
1931 row created.
194
195
196
1974. Optional question:
198
199Option 1:
200For each country represented at the 2015 championship, show the number of officials,
201the total number of players and the number of seed players.
202
203select NVL(T1.country,T2.country) as country, no_off, no_players, no_seeds
204from (select country, count(no) as no_off from official group by country) T1
205 full outer join
206 (select country, count(id) as No_players, count(SeedNo) as No_Seeds
207 from player group by country) T2
208 on T1.country = T2.country
209order by country;
210
211
212Option 2:
213Find countries that have the highest percentage of seed players.
214
215select country, count(id) as No_players, count(SeedNo) as No_Seeds
216from player
217group by country
218having (count(seedno) / count(id)) = (select max(count(seedno) / count(id))
219 from player
220 group by country);
221or
222
223select country
224from player
225group by country
226having (count(seedno) / count(id)) = (select max(count(seedno) / count(id))
227 from player
228 group by country);
229
230Result: 100% South Africa, Switzerland, Bulgaria, Uruguay