SQL Exercise: Each match assistant referee and their country
SQL soccer Database: Joins Exercise-45 with Solution
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
Sample table: asst_referee_mast
Sample table: soccer_country
Sample Solution:
SQL Code:
SELECT a.match_no,
b.country_name,
c.ass_ref_name
FROM match_details a
JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id
JOIN soccer_country b ON c.country_id=b.country_id
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.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Find the most number of cards shown in the matches.
Next SQL Exercise: Assistant referees of each countries assists matches.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the best way to paginate results in SQL Server?
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
Database: SQL Server
Ref: https://bit.ly/3MGrNlk
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook