SQL Exercise: Find the final four teams in the tournament

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

30. From the following tables, write a SQL query to find the final four teams in the tournament. Return country name.

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:

-- This query selects the country names of teams that participated in the 'S' (presumably, group stage) matches.

SELECT 
    country_name -- Selecting the country names of the teams
FROM 
    match_details a -- Specifying the match_details table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_details table based on the team_id
	-- Filtering matches that are in the 'S' (presumably, group stage)
WHERE 
    play_stage = 'S';

Sample Output:

 country_name
--------------
 France
 Germany
 Portugal
 Wales
(4 rows)

Code Explanation:

The said query in SQL that retrieves the country names of teams that have played in the "Semi-Finals" of a soccer tournament.
The JOIN clause joins the 'match_details' table and the 'soccer_country' table using the "team_id" and the "country_id" columns from the respective tables.
The WHERE clause retrieves data for teams that have played in the "Semi-Finals" of a soccer tournament.

Alternative Solutions:

Using EXISTS Clause:

-- This query selects the country names of teams that participated in the 'S' (presumably, group stage) matches.

SELECT 
    country_name -- Selecting the country names of the teams
FROM 
    soccer_country b -- Specifying the soccer_country table with an alias 'b'
WHERE 
    EXISTS ( -- Checking for the existence of at least one record that satisfies the condition within the subquery
        SELECT 1 -- Selecting 1 as a placeholder value (could be any value since we're using EXISTS)
        FROM 
            match_details a -- Specifying the match_details table with an alias 'a' for the subquery
        WHERE 
            a.team_id = b.country_id -- Matching the team ID between the outer query and the subquery
            AND a.play_stage = 'S' -- Filtering matches that are in the 'S' (presumably, group stage)
    );

Explanation:

This query uses the EXISTS clause with a subquery to check if there exists a match in the 'S' play stage for each country in the soccer_country table. If a match is found, the country name is selected.

Using INNER JOIN with Subquery:

-- This query selects the country names of teams that participated in the 'S' (presumably, group stage) matches.

SELECT 
    b.country_name -- Selecting the country names of the teams
FROM 
    soccer_country b -- Specifying the soccer_country table with an alias 'b'
JOIN 
    (
        -- Subquery to select distinct team IDs participating in the group stage matches
        SELECT DISTINCT 
            team_id -- Selecting distinct team IDs from the match_details table
        FROM 
            match_details -- Specifying the match_details table for the subquery
        WHERE 
            play_stage = 'S' -- Filtering matches that are in the 'S' (presumably, group stage)
			-- Joining the main query with the subquery based on the team_id
    ) a ON b.country_id = a.team_id;

Explanation:

This query first uses a subquery to find distinct team_ids playing in the 'S' play stage. It then joins this subquery with the soccer_country table based on the team_id, resulting in the selection of corresponding country names.

Relational Algebra Expression: