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

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: From the following table, write a SQL query to find the countries from where the assistant referees assist most of the matches. Return country name and number of matches.
Next: From the following tables, write a SQL query to count the number of matches managed by referees of each country. 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