SQL Exercise: List the referees with their countries for each match
SQL soccer Database: Joins Exercise-48 with Solution
48. From the following table, write a SQL query to find the name of referees for each match. Sort the result-set on match number. Return match number, country name, referee name.
Sample table: match_mast
Sample table: referee_mast
Sample table: soccer_country
Sample Solution:
SQL Code:
SELECT a.match_no,
b.country_name,
c.referee_name
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
ORDER BY a.match_no;
Sample Output:
match_no | country_name | referee_name ----------+----------------+------------------------- 1 | Hungary | Viktor Kassai 2 | Spain | Carlos Velasco Carballo 3 | Norway | Svein Oddvar Moen 4 | Italy | Nicola Rizzoli 5 | Sweden | Jonas Eriksson 6 | Romania | Ovidiu Hategan 7 | England | Martin Atkinson 8 | Poland | Szymon Marciniak 9 | Serbia | Milorad Mazic 10 | England | Mark Clattenburg 11 | France | Clement Turpin 12 | Turkey | Cuneyt Cakir 13 | Slovenia | Damir Skomina 14 | Russia | Sergei Karasev 15 | Scotland | William Collum 16 | Germany | Felix Brych 17 | Czech Republic | Pavel Kralovec 18 | Netherlands | Bjorn Kuipers 19 | Hungary | Viktor Kassai 20 | England | Mark Clattenburg 21 | Serbia | Milorad Mazic 22 | Turkey | Cuneyt Cakir 23 | Russia | Sergei Karasev 24 | Italy | Nicola Rizzoli 25 | Czech Republic | Pavel Kralovec 26 | Slovenia | Damir Skomina 27 | Sweden | Jonas Eriksson 28 | Spain | Carlos Velasco Carballo 29 | Norway | Svein Oddvar Moen 30 | France | Clement Turpin 31 | Scotland | William Collum 32 | Netherlands | Bjorn Kuipers 33 | Poland | Szymon Marciniak 34 | England | Martin Atkinson 35 | Romania | Ovidiu Hategan 36 | Germany | Felix Brych 37 | England | Mark Clattenburg 38 | England | Martin Atkinson 39 | Spain | Carlos Velasco Carballo 40 | Italy | Nicola Rizzoli 41 | Poland | Szymon Marciniak 42 | Serbia | Milorad Mazic 43 | Turkey | Cuneyt Cakir 44 | Slovenia | Damir Skomina 45 | Germany | Felix Brych 46 | Slovenia | Damir Skomina 47 | Hungary | Viktor Kassai 48 | Netherlands | Bjorn Kuipers 49 | Sweden | Jonas Eriksson 50 | Italy | Nicola Rizzoli 51 | England | Mark Clattenburg (51 rows)
Code Explanation:
The said query in SQL that selects the match number, country name, and referee name for each match in the match_mast table.
The JOIN clause in this query then joins the match_mast and referee_mast tables based on the referee_id column and joins the results with soccer_country tables based on the country_id column.
The query then orders the results by match number in ascending order using the ORDER BY clause.
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: Countries where assistant referees assist most matches.
Next SQL Exercise: The number of matches each referee has managed.
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