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.