SQL Exercise: Referee who assisted in the final match
11. From the following tables, write a SQL query to find the referee who assisted the referee in the final match. Return associated referee name, country name.
Sample table: asst_referee_mast
ass_ref_id | ass_ref_name | country_id
------------+--------------------------+------------
80034 | Tomas Mokrusch | 1205
80038 | Martin Wilczek | 1205
80004 | Simon Beck | 1206
80006 | Stephen Child | 1206
80007 | Jake Collin | 1206
80014 | Mike Mullarkey | 1206
80026 | Frederic Cano | 1207
80028 | Nicolas Danos | 1207
80005 | Mark Borsch | 1208
80013 | Stefan Lupp | 1208
80016 | Gyorgy Ring | 1209
80020 | Vencel Toth | 1209
80033 | Damien McGraith | 1215
80008 | Elenito Di Liberatore | 1211
80019 | Mauro Tonolini | 1211
80021 | Sander van Roekel | 1226
80024 | Erwin Zeinstra | 1226
80025 | Frank Andas | 1229
80031 | Kim Haglund | 1229
80012 | Tomasz Listkiewicz | 1213
80018 | Pawel Sokolnicki | 1213
80029 | Sebastian Gheorghe | 1216
80036 | Octavian Sovre | 1216
80030 | Nikolay Golubev | 1217
80032 | Tikhon Kalugin | 1217
80037 | Anton Averyanov | 1217
80027 | Frank Connor | 1228
80010 | Dalibor Durdevic | 1227
80017 | Milovan Ristic | 1227
80035 | Roman Slysko | 1218
80001 | Jure Praprotnik | 1225
80002 | Robert Vukan | 1225
80003 | Roberto Alonso Fernandez | 1219
80023 | Juan Yuste Jimenez | 1219
80011 | Mathias Klasenius | 1220
80022 | Daniel Warnmark | 1220
80009 | Bahattin Duran | 1222
80015 | Tarik Ongun | 1222
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
1210 | ISL | Iceland
1211 | ITA | Italy
1212 | NIR | Northern Ireland
1213 | POL | Poland
1214 | POR | Portugal
1215 | IRL | Republic of Ireland
1216 | ROU | Romania
1217 | RUS | Russia
1218 | SVK | Slovakia
1219 | ESP | Spain
1220 | SWE | Sweden
1221 | SUI | Switzerland
1222 | TUR | Turkey
1223 | UKR | Ukraine
1224 | WAL | Wales
1225 | SLO | Slovenia
1226 | NED | Netherlands
1227 | SRB | Serbia
1228 | SCO | Scotland
1229 | NOR | Norway
Sample table: match_details
match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
1 | G | 1207 | W | N | 2 | | 80016 | 160140
1 | G | 1216 | L | N | 1 | | 80020 | 160348
2 | G | 1201 | L | N | 0 | | 80003 | 160001
2 | G | 1221 | W | N | 1 | | 80023 | 160463
3 | G | 1224 | W | N | 2 | | 80031 | 160532
3 | G | 1218 | L | N | 1 | | 80025 | 160392
4 | G | 1206 | D | N | 1 | | 80008 | 160117
4 | G | 1217 | D | N | 1 | | 80019 | 160369
5 | G | 1222 | L | N | 0 | | 80011 | 160486
5 | G | 1204 | W | N | 1 | | 80022 | 160071
6 | G | 1213 | W | N | 1 | | 80036 | 160279
6 | G | 1212 | L | N | 0 | | 80029 | 160256
7 | G | 1208 | W | N | 2 | | 80014 | 160163
7 | G | 1223 | L | N | 0 | | 80006 | 160508
8 | G | 1219 | W | N | 1 | | 80018 | 160416
8 | G | 1205 | L | N | 0 | | 80012 | 160093
9 | G | 1215 | D | N | 1 | | 80017 | 160324
9 | G | 1220 | D | N | 1 | | 80010 | 160439
10 | G | 1203 | L | N | 0 | | 80004 | 160047
10 | G | 1211 | W | N | 2 | | 80007 | 160231
11 | G | 1202 | L | N | 0 | | 80026 | 160024
11 | G | 1209 | W | N | 2 | | 80028 | 160187
12 | G | 1214 | D | N | 1 | | 80009 | 160302
12 | G | 1210 | D | N | 1 | | 80015 | 160208
13 | G | 1217 | L | N | 1 | | 80001 | 160369
13 | G | 1218 | W | N | 2 | | 80002 | 160392
14 | G | 1216 | D | N | 1 | | 80030 | 160348
14 | G | 1221 | D | N | 1 | | 80032 | 160463
15 | G | 1207 | W | N | 2 | | 80033 | 160140
15 | G | 1201 | L | N | 0 | | 80027 | 160001
16 | G | 1206 | W | N | 2 | | 80005 | 160117
16 | G | 1224 | L | N | 1 | | 80013 | 160531
17 | G | 1223 | L | N | 0 | | 80035 | 160508
17 | G | 1212 | W | N | 2 | | 80034 | 160256
18 | G | 1208 | D | N | 0 | | 80021 | 160163
18 | G | 1213 | D | N | 0 | | 80024 | 160278
19 | G | 1211 | W | N | 1 | | 80016 | 160231
19 | G | 1220 | L | N | 0 | | 80020 | 160439
20 | G | 1205 | D | N | 2 | | 80004 | 160093
20 | G | 1204 | D | N | 2 | | 80007 | 160071
21 | G | 1219 | W | N | 3 | | 80017 | 160416
21 | G | 1222 | L | N | 0 | | 80010 | 160486
22 | G | 1203 | W | N | 3 | | 80009 | 160047
22 | G | 1215 | L | N | 0 | | 80015 | 160324
23 | G | 1210 | D | N | 1 | | 80030 | 160208
23 | G | 1209 | D | N | 1 | | 80032 | 160187
24 | G | 1214 | D | N | 0 | | 80008 | 160302
24 | G | 1202 | D | N | 0 | | 80019 | 160024
25 | G | 1216 | L | N | 0 | | 80035 | 160348
25 | G | 1201 | W | N | 1 | | 80034 | 160001
26 | G | 1221 | D | N | 0 | | 80001 | 160463
26 | G | 1207 | D | N | 0 | | 80002 | 160140
27 | G | 1217 | L | N | 0 | | 80011 | 160369
27 | G | 1224 | W | N | 3 | | 80022 | 160531
28 | G | 1218 | D | N | 0 | | 80003 | 160392
28 | G | 1206 | D | N | 0 | | 80023 | 160117
29 | G | 1223 | L | N | 0 | | 80031 | 160508
29 | G | 1213 | W | N | 1 | | 80025 | 160278
30 | G | 1212 | L | N | 0 | | 80026 | 160256
30 | G | 1208 | W | N | 1 | | 80028 | 160163
31 | G | 1205 | L | N | 0 | | 80033 | 160093
31 | G | 1222 | W | N | 2 | | 80027 | 160486
32 | G | 1204 | W | N | 2 | | 80021 | 160071
32 | G | 1219 | L | N | 1 | | 80024 | 160416
33 | G | 1210 | W | N | 2 | | 80018 | 160208
33 | G | 1202 | L | N | 1 | | 80012 | 160024
34 | G | 1209 | D | N | 3 | | 80014 | 160187
34 | G | 1214 | D | N | 3 | | 80006 | 160302
35 | G | 1211 | L | N | 0 | | 80036 | 160233
35 | G | 1215 | W | N | 1 | | 80029 | 160324
36 | G | 1220 | L | N | 0 | | 80005 | 160439
36 | G | 1203 | W | N | 1 | | 80013 | 160047
37 | R | 1221 | L | P | 1 | 4 | 80004 | 160463
37 | R | 1213 | W | P | 1 | 5 | 80007 | 160278
38 | R | 1224 | W | N | 1 | | 80014 | 160531
38 | R | 1212 | L | N | 0 | | 80006 | 160256
39 | R | 1204 | L | N | 0 | | 80003 | 160071
39 | R | 1214 | W | N | 1 | | 80023 | 160302
40 | R | 1207 | W | N | 2 | | 80008 | 160140
40 | R | 1215 | L | N | 1 | | 80019 | 160324
41 | R | 1208 | W | N | 3 | | 80018 | 160163
41 | R | 1218 | L | N | 0 | | 80012 | 160392
42 | R | 1209 | L | N | 0 | | 80017 | 160187
42 | R | 1203 | W | N | 4 | | 80010 | 160047
43 | R | 1211 | W | N | 2 | | 80009 | 160231
43 | R | 1219 | L | N | 0 | | 80015 | 160416
44 | R | 1206 | L | N | 1 | | 80001 | 160117
44 | R | 1210 | W | N | 2 | | 80002 | 160208
45 | Q | 1213 | L | P | 1 | 3 | 80005 | 160278
45 | Q | 1214 | W | P | 1 | 5 | 80013 | 160302
46 | Q | 1224 | W | N | 3 | | 80001 | 160531
46 | Q | 1203 | L | N | 1 | | 80002 | 160047
47 | Q | 1208 | W | P | 1 | 6 | 80016 | 160163
47 | Q | 1211 | L | P | 1 | 5 | 80020 | 160231
48 | Q | 1207 | W | N | 5 | | 80021 | 160140
48 | Q | 1210 | L | N | 2 | | 80024 | 160208
49 | S | 1214 | W | N | 2 | | 80011 | 160302
49 | S | 1224 | L | N | 0 | | 80022 | 160531
50 | S | 1207 | W | N | 2 | | 80008 | 160140
50 | S | 1208 | L | N | 1 | | 80019 | 160163
51 | F | 1214 | W | N | 1 | | 80004 | 160302
51 | F | 1207 | L | N | 0 | | 80007 | 160140
Sample Solution:
SQL Code:
-- Selecting ass_ref_name and country_name
SELECT a.ass_ref_name, b.country_name
-- From clause with JOINs between asst_referee_mast, soccer_country, and match_details
FROM asst_referee_mast a
JOIN soccer_country b ON a.country_id = b.country_id
JOIN match_details c ON a.ass_ref_id = c.ass_ref
-- Filtering the results to include only matches in the final play stage
WHERE play_stage = 'F';
Sample Output:
ass_ref_name | country_name --------------+-------------- Simon Beck | England Jake Collin | England (2 rows)
Code Explanation:
The provided query in SQL retrieves information about the assistant referee and country for matches in the final stage from the tables asst_referee_mast, soccer_country, and match_details.
The JOIN keyword is used to join the tables based on columns specified in the ON clause. The asst_referee_mast and soccer_country are joined on the country_id column, while asst_referee_mast and match_details are joined on the ass_ref_id column.
The condition filters rows for matches in the final stage, where play_stage is equal to 'F', will be selected.
Alternative Solutions:
Using Subquery:
-- Selecting ass_ref_name and country_name
SELECT a.ass_ref_name, b.country_name
-- From clause with JOIN between asst_referee_mast and soccer_country using country_id
FROM asst_referee_mast a
JOIN soccer_country b ON a.country_id = b.country_id
-- Filtering the results to include only assistant referees with ass_ref_id found in the subquery
WHERE a.ass_ref_id IN (
-- Subquery to select ass_ref from match_details for matches in the final play stage
SELECT c.ass_ref
-- From clause to select from match_details
FROM match_details c
-- Condition for filtering match_details based on play_stage
WHERE c.play_stage = 'F'
);
Explanation:
This query uses a subquery in the WHERE clause to filter the results based on the play_stage condition.
Using EXISTS:
-- Selecting ass_ref_name and country_name
SELECT a.ass_ref_name, b.country_name
-- From clause with JOIN between asst_referee_mast and soccer_country using country_id
FROM asst_referee_mast a
JOIN soccer_country b ON a.country_id = b.country_id
-- Using EXISTS to filter results based on a subquery condition
WHERE EXISTS (
-- Subquery to check if there is at least one match in the final play stage for the assistant referee
SELECT 1
-- From clause to select from match_details
FROM match_details c
-- Conditions for joining match_details based on ass_ref_id and play_stage
WHERE a.ass_ref_id = c.ass_ref
AND c.play_stage = 'F'
);
Explanation:
This query uses the EXISTS clause to check if there is at least one match with the play_stage condition for each assistant referee.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Referee who assisted in the opening match.
Next SQL Exercise: City where the opening match of EURO cup 2016 played.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.