SQL Exercise: Find the runners-up in Football EURO cup 2016

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

24. From the following tables, write a SQL query to find the runners-up in Football EURO cup 2016. Return country name.

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 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 Solution:

SQL Code:

SELECT country_name 
FROM soccer_country 
WHERE country_id=(
SELECT team_id 
FROM match_details 
WHERE play_stage='F' AND win_lose='L'
AND team_id<>(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Portugal'));

Sample Output:

 country_name
--------------
 France
(1 row)

Code Explanation:

The said query in SQL that selects the names of all countries that lost to a team other than Portugal in the final stage of a soccer match.
The WHERE clause filters the results based on whether the country_id is equal to the result of the subquery.
The subquery fetches the team_id from the match_details table where the play_stage is 'F' and the win_lose column is 'L' and also filters out the team with the country_name 'Portugal'.

Alternative Solution:

Using JOIN and Subqueries:


SELECT sc1.country_name 
FROM soccer_country sc1
JOIN match_details md ON sc1.country_id = md.team_id
JOIN soccer_country sc2 ON md.team_id = sc2.country_id
WHERE md.play_stage = 'F'
  AND md.win_lose = 'L'
  AND sc2.country_name <> 'Portugal';

Explanation:

This query uses JOIN operations to connect the soccer_country table with match_details and soccer_country again based on their respective IDs. It then applies the specified conditions to filter the results.

Using EXISTS:


SELECT country_name 
FROM soccer_country sc1
WHERE EXISTS (
    SELECT 1
    FROM match_details md
    JOIN soccer_country sc2 ON md.team_id = sc2.country_id
    WHERE sc1.country_id = md.team_id
      AND md.play_stage = 'F'
      AND md.win_lose = 'L'
      AND sc2.country_name <> 'Portugal'
);

Explanation:

This query uses the EXISTS keyword with a subquery to check if there exists a record in match_details and soccer_country that satisfies the specified conditions.

Go to:

PREV : German player who did not concede a goal in group stage.
NEXT : Find the maximum penalty shots taken by the teams.

Practice Online