Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

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

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: From the following tables, write a SQL query to find those matches where most number of cards shown. Return match number, number of cards shown.
Next: From the following tables, write a SQL query to find the assistant referees of each country assists the number of matches. Sort the result-set in descending order on number of matches. Return country name, number of matches.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3