SQL Exercise: Referee who assisted in the final match

Last update on January 15 2026 12:41:41 (UTC/GMT +8 hours)

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

 referee_id |      referee_name       | country_id
------------+-------------------------+------------
      70001 | Damir Skomina           |       1225
      70002 | Martin Atkinson         |       1206
      70003 | Felix Brych             |       1208
      70004 | Cuneyt Cakir            |       1222
      70005 | Mark Clattenburg        |       1206
      70006 | Jonas Eriksson          |       1220
      70007 | Viktor Kassai           |       1209
      70008 | Bjorn Kuipers           |       1226
      70009 | Szymon Marciniak        |       1213
.......
      70018 | Clement Turpin          |       1207

View the table

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
......
       1229 | NOR          | Norway

View the table

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
.........
       51 | F          |    1207 | L        | N          |          0 |               |   80007 |    160140

View the table

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: