SQL Exercise: Find the winner of EURO cup 2016

Last update on January 16 2026 12:32:38 (UTC/GMT +8 hours)

2. From the following tables, write a SQL query to find the winner of EURO cup 2016. 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 SQL query retrieves the country names (aliased as 'Team') from the 'soccer_country' table
-- where the country ID is present in the result of a subquery.

SELECT country_name as Team 
-- Selects the 'country_name' column and aliases it as 'Team'.
FROM soccer_country 
-- 'soccer_country' is the name of the table being queried.
WHERE country_id IN (
-- The WHERE clause filters rows where 'country_id' is present in the result of the subquery.
    SELECT team_id 
    -- The subquery selects the 'team_id' column.
    FROM match_details 
    -- 'match_details' is the name of the table involved in the subquery.
    WHERE play_stage='F' and win_lose='W'
    -- Further filters rows in the subquery where 'play_stage' is 'F' and 'win_lose' is 'W'.
);

Sample Output:

   team
----------
 Portugal
(1 row)

Code Explanation:

The said query in SQL that selects the names of the winning teams from the 'soccer_country' table.
This subquery is used to retrieve the winning team IDs from the 'match_details' table, and then select the team names corresponding to the winning teams from the 'soccer_country' table based on the team IDs.
In order to determine the country_id in the soccer_country table, the WHERE clause must be included in the list of team_ids that is returned by the subquery itself.
The subquery selects the team_ids from the 'match_details' table where the play_stage is "F" and the win_lose column is "W".

Alternative Solutions:

Using INNER JOIN:


SELECT sc.country_name as Team
FROM soccer_country sc
JOIN match_details md ON sc.country_id = md.team_id
WHERE md.play_stage = 'F' AND md.win_lose = 'W';

Explanation:

This query uses an INNER JOIN to combine the soccer_country and match_details tables based on the condition that country_id matches team_id. It then filters the results to only include rows where play_stage is 'F' and win_lose is 'W'.

Using EXISTS Clause:


SELECT country_name as Team
FROM soccer_country sc
WHERE EXISTS (
    SELECT 1
    FROM match_details md
    WHERE sc.country_id = md.team_id
    AND md.play_stage = 'F'
    AND md.win_lose = 'W'
);

Explanation:

This query uses the EXISTS clause with a subquery to check if there exists a match in match_details where the team wins in the final stage. If such a match exists, the main query includes the corresponding team name.

Using INNER JOIN with Explicit JOIN Syntax:


SELECT sc.country_name as Team
FROM soccer_country sc
JOIN match_details md ON sc.country_id = md.team_id
       AND md.play_stage = 'F'
       AND md.win_lose = 'W';

Explanation:

This query uses INNER JOIN with the explicit JOIN syntax. It specifies the join condition in the ON clause, along with the conditions for play_stage and win_lose.

Using Cross Join with WHERE Clause:


SELECT sc.country_name as Team
FROM soccer_country sc, match_details md
WHERE sc.country_id = md.team_id
      AND md.play_stage = 'F'
      AND md.win_lose = 'W';

Explanation:

This query uses a cross join between soccer_country and match_details. It then applies a WHERE clause to filter the results for rows where play_stage is 'F', win_lose is 'W', and country_id matches team_id.

Go to:

PREV : Find the teams played the first match of EURO cup 2016.
NEXT : Find which was the highest audience match.

Practice Online