w3resource
Soccer Database Exercises

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

SQL soccer Database: Joins Exercise-45 with Solution

45. Write a query in SQL to list the name of assistant referees with their countries for each matches.

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)

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 most number of cards shown in the matches.
Next: Write a query in SQL to find the assistant referees of each countries assists the number of matches.

What is the difficulty level of this exercise?



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