w3resource

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

Relational Algebra Tree:

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

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



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