SQL Exercise: Each match assistant referee and their country
45. From the following table, write a SQL query to find the assistant referees. Return match number, country name, assistant referee name.
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 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 Solution:
SQL Code:
-- Selecting the match number, country name, and assistant referee name for each match
SELECT
a.match_no, -- Selecting the match number from the match_details table
b.country_name, -- Selecting the country name from the soccer_country table
c.ass_ref_name -- Selecting the assistant referee name from the asst_referee_mast table
FROM
match_details a -- Specifying the match_details table with alias 'a'
JOIN
asst_referee_mast c -- Joining the asst_referee_mast table with alias 'c' to match_details table
ON
a.ass_ref = c.ass_ref_id -- Joining on the assistant referee ID from match_details and asst_referee_mast tables
JOIN
soccer_country b -- Joining the soccer_country table with alias 'b' to asst_referee_mast table
ON
c.country_id = b.country_id -- Joining on the country ID from asst_referee_mast and soccer_country tables
-- Ordering the results by match number
ORDER BY
a.match_no;
Sample Output:
match_no | country_name | ass_ref_name
----------+---------------------+--------------------------
1 | Hungary | Gyorgy Ring
1 | Hungary | Vencel Toth
2 | Spain | Juan Yuste Jimenez
2 | Spain | Roberto Alonso Fernandez
3 | Norway | Frank Andas
3 | Norway | Kim Haglund
4 | Italy | Mauro Tonolini
4 | Italy | Elenito Di Liberatore
5 | Sweden | Daniel Warnmark
5 | Sweden | Mathias Klasenius
6 | Romania | Octavian Sovre
6 | Romania | Sebastian Gheorghe
7 | England | Stephen Child
7 | England | Mike Mullarkey
8 | Poland | Pawel Sokolnicki
8 | Poland | Tomasz Listkiewicz
9 | Serbia | Dalibor Durdevic
9 | Serbia | Milovan Ristic
10 | England | Jake Collin
10 | England | Simon Beck
11 | France | Nicolas Danos
11 | France | Frederic Cano
12 | Turkey | Tarik Ongun
12 | Turkey | Bahattin Duran
13 | Slovenia | Robert Vukan
13 | Slovenia | Jure Praprotnik
14 | Russia | Nikolay Golubev
14 | Russia | Tikhon Kalugin
15 | Scotland | Frank Connor
15 | Republic of Ireland | Damien McGraith
16 | Germany | Mark Borsch
16 | Germany | Stefan Lupp
17 | Slovakia | Roman Slysko
17 | Czech Republic | Tomas Mokrusch
18 | Netherlands | Erwin Zeinstra
18 | Netherlands | Sander van Roekel
19 | Hungary | Gyorgy Ring
19 | Hungary | Vencel Toth
20 | England | Jake Collin
20 | England | Simon Beck
21 | Serbia | Milovan Ristic
21 | Serbia | Dalibor Durdevic
22 | Turkey | Tarik Ongun
22 | Turkey | Bahattin Duran
23 | Russia | Tikhon Kalugin
23 | Russia | Nikolay Golubev
24 | Italy | Elenito Di Liberatore
24 | Italy | Mauro Tonolini
25 | Slovakia | Roman Slysko
25 | Czech Republic | Tomas Mokrusch
26 | Slovenia | Robert Vukan
26 | Slovenia | Jure Praprotnik
27 | Sweden | Daniel Warnmark
27 | Sweden | Mathias Klasenius
28 | Spain | Juan Yuste Jimenez
28 | Spain | Roberto Alonso Fernandez
29 | Norway | Kim Haglund
29 | Norway | Frank Andas
30 | France | Nicolas Danos
30 | France | Frederic Cano
31 | Scotland | Frank Connor
31 | Republic of Ireland | Damien McGraith
32 | Netherlands | Erwin Zeinstra
32 | Netherlands | Sander van Roekel
33 | Poland | Pawel Sokolnicki
33 | Poland | Tomasz Listkiewicz
34 | England | Mike Mullarkey
34 | England | Stephen Child
35 | Romania | Octavian Sovre
35 | Romania | Sebastian Gheorghe
36 | Germany | Mark Borsch
36 | Germany | Stefan Lupp
37 | England | Simon Beck
37 | England | Jake Collin
38 | England | Stephen Child
38 | England | Mike Mullarkey
39 | Spain | Juan Yuste Jimenez
39 | Spain | Roberto Alonso Fernandez
40 | Italy | Elenito Di Liberatore
40 | Italy | Mauro Tonolini
41 | Poland | Tomasz Listkiewicz
41 | Poland | Pawel Sokolnicki
42 | Serbia | Milovan Ristic
42 | Serbia | Dalibor Durdevic
43 | Turkey | Bahattin Duran
43 | Turkey | Tarik Ongun
44 | Slovenia | Jure Praprotnik
44 | Slovenia | Robert Vukan
45 | Germany | Mark Borsch
45 | Germany | Stefan Lupp
46 | Slovenia | Jure Praprotnik
46 | Slovenia | Robert Vukan
47 | Hungary | Vencel Toth
47 | Hungary | Gyorgy Ring
48 | Netherlands | Sander van Roekel
48 | Netherlands | Erwin Zeinstra
49 | Sweden | Daniel Warnmark
49 | Sweden | Mathias Klasenius
50 | Italy | Elenito Di Liberatore
50 | Italy | Mauro Tonolini
51 | England | Simon Beck
51 | England | Jake Collin
(102 rows)
Code Explanation:
The said query in SQL that retrieves the match number, the country name of the assistant referee, and the name of the assistant referee from the tables 'match_details', 'asst_referee_mast', and 'soccer_country'.
The JOIN clause joins the 'match_details' table with the 'asst_referee_mast' table based on the "ass_ref" and the "ass_ref_id" columns respectively. It then joins the result with the 'soccer_country' table on the "country_id" column.
This orders the results by the match number in ascending order.
Alternative Solutions:
Using Subqueries:
-- Selecting the match number, country name of the assistant referee, and assistant referee name for each match
SELECT
md.match_no, -- Selecting the match number from the match_details table
( -- Subquery to fetch the country name of the assistant referee
SELECT
sc.country_name -- Selecting the country name from the soccer_country table
FROM
soccer_country sc -- Specifying the soccer_country table with alias 'sc'
WHERE
sc.country_id = ( -- Matching the country ID with the one in the asst_referee_mast table
SELECT
arm.country_id -- Selecting the country ID from the asst_referee_mast table
FROM
asst_referee_mast arm -- Specifying the asst_referee_mast table with alias 'arm'
WHERE
arm.ass_ref_id = md.ass_ref -- Matching the assistant referee ID from match_details
)
) as country_name, -- Alias for the country name subquery result
( -- Subquery to fetch the assistant referee name
SELECT
arm.ass_ref_name -- Selecting the assistant referee name from the asst_referee_mast table
FROM
asst_referee_mast arm -- Specifying the asst_referee_mast table with alias 'arm'
WHERE
arm.ass_ref_id = md.ass_ref -- Matching the assistant referee ID from match_details
) as ass_ref_name -- Alias for the assistant referee name subquery result
FROM
match_details md -- Specifying the match_details table with alias 'md'
-- Ordering the results by match number
ORDER BY
md.match_no;
Explanation:
This query uses correlated subqueries to retrieve the country name and assistant referee name based on their respective IDs. It selects the match number along with these values and orders the results by match number.
Using Subqueries with INNER JOIN:
-- Selecting the match number, country name of the assistant referee, and assistant referee name for each match
SELECT
md.match_no, -- Selecting the match number from the match_details table
( -- Subquery to fetch the country name of the assistant referee
SELECT
sc.country_name -- Selecting the country name from the soccer_country table
FROM
soccer_country sc -- Specifying the soccer_country table with alias 'sc'
JOIN
asst_referee_mast arm ON arm.country_id = sc.country_id -- Joining the asst_referee_mast table with the soccer_country table based on country ID
WHERE
arm.ass_ref_id = md.ass_ref -- Matching the assistant referee ID from match_details
) as country_name, -- Alias for the country name subquery result
( -- Subquery to fetch the assistant referee name
SELECT
arm.ass_ref_name -- Selecting the assistant referee name from the asst_referee_mast table
FROM
asst_referee_mast arm -- Specifying the asst_referee_mast table with alias 'arm'
WHERE
arm.ass_ref_id = md.ass_ref -- Matching the assistant referee ID from match_details
) as ass_ref_name -- Alias for the assistant referee name subquery result
FROM
match_details md -- Specifying the match_details table with alias 'md'
-- Ordering the results by match number
ORDER BY
md.match_no;
Explanation:
This query uses subqueries with INNER JOINs to retrieve the country name and assistant referee name. It selects the match number along with these values and orders the results by match number.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the most number of cards shown in the matches.
NEXT : Assistant referees of each countries assists matches.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.