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
.........
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
.........
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
........
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.