w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: List the name of referees with their countries for each match

SQL soccer Database: Joins Exercise-48 with Solution

48. Write a query in SQL to list the name of referees with their countries for each match.

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)

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to find the countries from where the assistant referees assist most of the matches.
Next: Write a query in SQL to find the referees of each country managed number of matches.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming