w3resource

SQL Exercise: Referee who assisted in the final match

SQL soccer Database: Joins Exercise-11 with Solution

11. From the following tables, write a SQL query to find the referee who assisted the referee in the final match. Return associated referee name, country name.

Sample table: asst_referee_mast
 ass_ref_id |       ass_ref_name       | country_id
------------+--------------------------+------------
      80034 | Tomas Mokrusch           |       1205
      80038 | Martin Wilczek           |       1205
      80004 | Simon Beck               |       1206
      80006 | Stephen Child            |       1206
      80007 | Jake Collin              |       1206
      80014 | Mike Mullarkey           |       1206
      80026 | Frederic Cano            |       1207
      80028 | Nicolas Danos            |       1207
      80005 | Mark Borsch              |       1208
      80013 | Stefan Lupp              |       1208
      80016 | Gyorgy Ring              |       1209
      80020 | Vencel Toth              |       1209
      80033 | Damien McGraith          |       1215
      80008 | Elenito Di Liberatore    |       1211
      80019 | Mauro Tonolini           |       1211
      80021 | Sander van Roekel        |       1226
      80024 | Erwin Zeinstra           |       1226
      80025 | Frank Andas              |       1229
      80031 | Kim Haglund              |       1229
      80012 | Tomasz Listkiewicz       |       1213
      80018 | Pawel Sokolnicki         |       1213
      80029 | Sebastian Gheorghe       |       1216
      80036 | Octavian Sovre           |       1216
      80030 | Nikolay Golubev          |       1217
      80032 | Tikhon Kalugin           |       1217
      80037 | Anton Averyanov          |       1217
      80027 | Frank Connor             |       1228
      80010 | Dalibor Durdevic         |       1227
      80017 | Milovan Ristic           |       1227
      80035 | Roman Slysko             |       1218
      80001 | Jure Praprotnik          |       1225
      80002 | Robert Vukan             |       1225
      80003 | Roberto Alonso Fernandez |       1219
      80023 | Juan Yuste Jimenez       |       1219
      80011 | Mathias Klasenius        |       1220
      80022 | Daniel Warnmark          |       1220
      80009 | Bahattin Duran           |       1222
      80015 | Tarik Ongun              |       1222
Sample table: soccer_country
 country_id | country_abbr |    country_name
------------+--------------+---------------------
       1201 | ALB          | Albania
       1202 | AUT          | Austria
       1203 | BEL          | Belgium
       1204 | CRO          | Croatia
       1205 | CZE          | Czech Republic
       1206 | ENG          | England
       1207 | FRA          | France
       1208 | GER          | Germany
       1209 | HUN          | Hungary
       1210 | ISL          | Iceland
       1211 | ITA          | Italy
       1212 | NIR          | Northern Ireland
       1213 | POL          | Poland
       1214 | POR          | Portugal
       1215 | IRL          | Republic of Ireland
       1216 | ROU          | Romania
       1217 | RUS          | Russia
       1218 | SVK          | Slovakia
       1219 | ESP          | Spain
       1220 | SWE          | Sweden
       1221 | SUI          | Switzerland
       1222 | TUR          | Turkey
       1223 | UKR          | Ukraine
       1224 | WAL          | Wales
       1225 | SLO          | Slovenia
       1226 | NED          | Netherlands
       1227 | SRB          | Serbia
       1228 | SCO          | Scotland
       1229 | NOR          | Norway
Sample table: match_details
 match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
        1 | G          |    1207 | W        | N          |          2 |               |   80016 |    160140
        1 | G          |    1216 | L        | N          |          1 |               |   80020 |    160348
        2 | G          |    1201 | L        | N          |          0 |               |   80003 |    160001
        2 | G          |    1221 | W        | N          |          1 |               |   80023 |    160463
        3 | G          |    1224 | W        | N          |          2 |               |   80031 |    160532
        3 | G          |    1218 | L        | N          |          1 |               |   80025 |    160392
        4 | G          |    1206 | D        | N          |          1 |               |   80008 |    160117
        4 | G          |    1217 | D        | N          |          1 |               |   80019 |    160369
        5 | G          |    1222 | L        | N          |          0 |               |   80011 |    160486
        5 | G          |    1204 | W        | N          |          1 |               |   80022 |    160071
        6 | G          |    1213 | W        | N          |          1 |               |   80036 |    160279
        6 | G          |    1212 | L        | N          |          0 |               |   80029 |    160256
        7 | G          |    1208 | W        | N          |          2 |               |   80014 |    160163
        7 | G          |    1223 | L        | N          |          0 |               |   80006 |    160508
        8 | G          |    1219 | W        | N          |          1 |               |   80018 |    160416
        8 | G          |    1205 | L        | N          |          0 |               |   80012 |    160093
        9 | G          |    1215 | D        | N          |          1 |               |   80017 |    160324
        9 | G          |    1220 | D        | N          |          1 |               |   80010 |    160439
       10 | G          |    1203 | L        | N          |          0 |               |   80004 |    160047
       10 | G          |    1211 | W        | N          |          2 |               |   80007 |    160231
       11 | G          |    1202 | L        | N          |          0 |               |   80026 |    160024
       11 | G          |    1209 | W        | N          |          2 |               |   80028 |    160187
       12 | G          |    1214 | D        | N          |          1 |               |   80009 |    160302
       12 | G          |    1210 | D        | N          |          1 |               |   80015 |    160208
       13 | G          |    1217 | L        | N          |          1 |               |   80001 |    160369
       13 | G          |    1218 | W        | N          |          2 |               |   80002 |    160392
       14 | G          |    1216 | D        | N          |          1 |               |   80030 |    160348
       14 | G          |    1221 | D        | N          |          1 |               |   80032 |    160463
       15 | G          |    1207 | W        | N          |          2 |               |   80033 |    160140
       15 | G          |    1201 | L        | N          |          0 |               |   80027 |    160001
       16 | G          |    1206 | W        | N          |          2 |               |   80005 |    160117
       16 | G          |    1224 | L        | N          |          1 |               |   80013 |    160531
       17 | G          |    1223 | L        | N          |          0 |               |   80035 |    160508
       17 | G          |    1212 | W        | N          |          2 |               |   80034 |    160256
       18 | G          |    1208 | D        | N          |          0 |               |   80021 |    160163
       18 | G          |    1213 | D        | N          |          0 |               |   80024 |    160278
       19 | G          |    1211 | W        | N          |          1 |               |   80016 |    160231
       19 | G          |    1220 | L        | N          |          0 |               |   80020 |    160439
       20 | G          |    1205 | D        | N          |          2 |               |   80004 |    160093
       20 | G          |    1204 | D        | N          |          2 |               |   80007 |    160071
       21 | G          |    1219 | W        | N          |          3 |               |   80017 |    160416
       21 | G          |    1222 | L        | N          |          0 |               |   80010 |    160486
       22 | G          |    1203 | W        | N          |          3 |               |   80009 |    160047
       22 | G          |    1215 | L        | N          |          0 |               |   80015 |    160324
       23 | G          |    1210 | D        | N          |          1 |               |   80030 |    160208
       23 | G          |    1209 | D        | N          |          1 |               |   80032 |    160187
       24 | G          |    1214 | D        | N          |          0 |               |   80008 |    160302
       24 | G          |    1202 | D        | N          |          0 |               |   80019 |    160024
       25 | G          |    1216 | L        | N          |          0 |               |   80035 |    160348
       25 | G          |    1201 | W        | N          |          1 |               |   80034 |    160001
       26 | G          |    1221 | D        | N          |          0 |               |   80001 |    160463
       26 | G          |    1207 | D        | N          |          0 |               |   80002 |    160140
       27 | G          |    1217 | L        | N          |          0 |               |   80011 |    160369
       27 | G          |    1224 | W        | N          |          3 |               |   80022 |    160531
       28 | G          |    1218 | D        | N          |          0 |               |   80003 |    160392
       28 | G          |    1206 | D        | N          |          0 |               |   80023 |    160117
       29 | G          |    1223 | L        | N          |          0 |               |   80031 |    160508
       29 | G          |    1213 | W        | N          |          1 |               |   80025 |    160278
       30 | G          |    1212 | L        | N          |          0 |               |   80026 |    160256
       30 | G          |    1208 | W        | N          |          1 |               |   80028 |    160163
       31 | G          |    1205 | L        | N          |          0 |               |   80033 |    160093
       31 | G          |    1222 | W        | N          |          2 |               |   80027 |    160486
       32 | G          |    1204 | W        | N          |          2 |               |   80021 |    160071
       32 | G          |    1219 | L        | N          |          1 |               |   80024 |    160416
       33 | G          |    1210 | W        | N          |          2 |               |   80018 |    160208
       33 | G          |    1202 | L        | N          |          1 |               |   80012 |    160024
       34 | G          |    1209 | D        | N          |          3 |               |   80014 |    160187
       34 | G          |    1214 | D        | N          |          3 |               |   80006 |    160302
       35 | G          |    1211 | L        | N          |          0 |               |   80036 |    160233
       35 | G          |    1215 | W        | N          |          1 |               |   80029 |    160324
       36 | G          |    1220 | L        | N          |          0 |               |   80005 |    160439
       36 | G          |    1203 | W        | N          |          1 |               |   80013 |    160047
       37 | R          |    1221 | L        | P          |          1 |             4 |   80004 |    160463
       37 | R          |    1213 | W        | P          |          1 |             5 |   80007 |    160278
       38 | R          |    1224 | W        | N          |          1 |               |   80014 |    160531
       38 | R          |    1212 | L        | N          |          0 |               |   80006 |    160256
       39 | R          |    1204 | L        | N          |          0 |               |   80003 |    160071
       39 | R          |    1214 | W        | N          |          1 |               |   80023 |    160302
       40 | R          |    1207 | W        | N          |          2 |               |   80008 |    160140
       40 | R          |    1215 | L        | N          |          1 |               |   80019 |    160324
       41 | R          |    1208 | W        | N          |          3 |               |   80018 |    160163
       41 | R          |    1218 | L        | N          |          0 |               |   80012 |    160392
       42 | R          |    1209 | L        | N          |          0 |               |   80017 |    160187
       42 | R          |    1203 | W        | N          |          4 |               |   80010 |    160047
       43 | R          |    1211 | W        | N          |          2 |               |   80009 |    160231
       43 | R          |    1219 | L        | N          |          0 |               |   80015 |    160416
       44 | R          |    1206 | L        | N          |          1 |               |   80001 |    160117
       44 | R          |    1210 | W        | N          |          2 |               |   80002 |    160208
       45 | Q          |    1213 | L        | P          |          1 |             3 |   80005 |    160278
       45 | Q          |    1214 | W        | P          |          1 |             5 |   80013 |    160302
       46 | Q          |    1224 | W        | N          |          3 |               |   80001 |    160531
       46 | Q          |    1203 | L        | N          |          1 |               |   80002 |    160047
       47 | Q          |    1208 | W        | P          |          1 |             6 |   80016 |    160163
       47 | Q          |    1211 | L        | P          |          1 |             5 |   80020 |    160231
       48 | Q          |    1207 | W        | N          |          5 |               |   80021 |    160140
       48 | Q          |    1210 | L        | N          |          2 |               |   80024 |    160208
       49 | S          |    1214 | W        | N          |          2 |               |   80011 |    160302
       49 | S          |    1224 | L        | N          |          0 |               |   80022 |    160531
       50 | S          |    1207 | W        | N          |          2 |               |   80008 |    160140
       50 | S          |    1208 | L        | N          |          1 |               |   80019 |    160163
       51 | F          |    1214 | W        | N          |          1 |               |   80004 |    160302
       51 | F          |    1207 | L        | N          |          0 |               |   80007 |    160140

Sample Solution:

SQL Code:

-- Selecting ass_ref_name and country_name
SELECT a.ass_ref_name, b.country_name
-- From clause with JOINs between asst_referee_mast, soccer_country, and match_details
FROM asst_referee_mast a
JOIN soccer_country b ON a.country_id = b.country_id
JOIN match_details c ON a.ass_ref_id = c.ass_ref
-- Filtering the results to include only matches in the final play stage
WHERE play_stage = 'F';

Sample Output:

 ass_ref_name | country_name
--------------+--------------
 Simon Beck   | England
 Jake Collin  | England
(2 rows)

Code Explanation:

The provided query in SQL retrieves information about the assistant referee and country for matches in the final stage from the tables asst_referee_mast, soccer_country, and match_details.
The JOIN keyword is used to join the tables based on columns specified in the ON clause. The asst_referee_mast and soccer_country are joined on the country_id column, while asst_referee_mast and match_details are joined on the ass_ref_id column.
The condition filters rows for matches in the final stage, where play_stage is equal to 'F', will be selected.

Alternative Solutions:

Using Subquery:

-- Selecting ass_ref_name and country_name
SELECT a.ass_ref_name, b.country_name
-- From clause with JOIN between asst_referee_mast and soccer_country using country_id
FROM asst_referee_mast a
JOIN soccer_country b ON a.country_id = b.country_id
-- Filtering the results to include only assistant referees with ass_ref_id found in the subquery
WHERE a.ass_ref_id IN (
    -- Subquery to select ass_ref from match_details for matches in the final play stage
    SELECT c.ass_ref
    -- From clause to select from match_details
    FROM match_details c
    -- Condition for filtering match_details based on play_stage
    WHERE c.play_stage = 'F'
);

Explanation:

This query uses a subquery in the WHERE clause to filter the results based on the play_stage condition.

Using EXISTS:

-- Selecting ass_ref_name and country_name
SELECT a.ass_ref_name, b.country_name
-- From clause with JOIN between asst_referee_mast and soccer_country using country_id
FROM asst_referee_mast a
JOIN soccer_country b ON a.country_id = b.country_id
-- Using EXISTS to filter results based on a subquery condition
WHERE EXISTS (
    -- Subquery to check if there is at least one match in the final play stage for the assistant referee
    SELECT 1
    -- From clause to select from match_details
    FROM match_details c
    -- Conditions for joining match_details based on ass_ref_id and play_stage
    WHERE a.ass_ref_id = c.ass_ref
    AND c.play_stage = 'F'
);

Explanation:

This query uses the EXISTS clause to check if there is at least one match with the play_stage condition for each assistant referee.

Relational Algebra Expression:

Relational Algebra Expression: Find the name and country of the referee who assisted the referee in the final match.

Relational Algebra Tree:

Relational Algebra Tree: Find the name and country of the referee who assisted the referee in the final match.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the name and country of the referee who assisted the referee in the final match - Duration

Rows:

Query visualization of Find the name and country of the referee who assisted the referee in the final match - Rows

Cost:

Query visualization of Find the name and country of the referee who assisted the referee in the final match - Cost

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

Previous SQL Exercise: Referee who assisted in the opening match.
Next SQL Exercise: City where the opening match of EURO cup 2016 played.

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.