w3resource

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 Expression: List the name of referees with their countries for each match.

Relational Algebra Tree:

Relational Algebra Tree: List the name of referees with their countries for each match.

Practice Online


Sample Database: soccer

soccer database relationship structure

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.



Follow us on Facebook and Twitter for latest update.

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

 





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