SQL Exercise: The team Portugal defeated in the EURO cup 2016 final

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

12. From the following tables, write a SQL query to find the team, which was defeated by Portugal in EURO cup 2016 final. Return the country name of the team.

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 from the 'soccer_country' table
-- for teams that participated in the 'F' (Finals) stage and are not Portugal.

SELECT country_name 
-- Selects the 'country_name' column.
FROM soccer_country 
-- 'soccer_country' is the name of the table being queried.
WHERE country_id = (
-- The WHERE clause filters rows where 'country_id' matches 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'
    -- Further filters rows in the subquery where 'play_stage' is 'F' (Finals).
    AND team_id <> (
    -- Further filters rows in the subquery where 'team_id' is not equal to the country ID for Portugal in the sub-subquery.
        SELECT country_id 
        -- This sub-subquery selects the 'country_id' column.
        FROM soccer_country 
        -- 'soccer_country' is the name of the table involved in the sub-subquery.
        WHERE country_name = 'Portugal'
        -- Further filters rows in the sub-subquery where 'country_name' is 'Portugal'.
    )
);

Sample Output:

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

Code Explanation:

The said query in SQL that selects the names of countries that played in the soccer final but did not have a team with the name "Portugal".
The WHERE clause of the outer query filters the rows where the country_id is equal to the result of a subquery.
The subquery selects the team_id from the match_details table where the play_stage is 'F' (final) and the team_id is not equal to the country_id of the row in the soccer_country table where the country_name is 'Portugal'.
In other words, the query is finding the country names of teams that played in the final, but not Portugal.

Alternative Solutions:

Using JOIN and WHERE:


SELECT sc.country_name
FROM soccer_country sc
JOIN match_details md ON sc.country_id = md.team_id
WHERE md.play_stage = 'F'
  AND md.team_id <> (
    SELECT country_id
    FROM soccer_country
    WHERE country_name = 'Portugal'
  );

Explanation:

This query uses JOIN to combine soccer_country and match_details based on country_id and team_id. It then applies conditions in the WHERE clause to filter for matches in the final stage (play_stage='F') and ensures that the team is not Portugal.

Using EXISTS:


SELECT country_name
FROM soccer_country sc
WHERE EXISTS (
    SELECT 1
    FROM match_details md
    WHERE md.team_id = sc.country_id
      AND md.play_stage = 'F'
      AND md.team_id <> (
        SELECT country_id
        FROM soccer_country
        WHERE country_name = 'Portugal'
      )
);

Explanation:

This query uses a correlated subquery with EXISTS to check if there exists a match in the final stage (play_stage='F') where the team is not Portugal.

Using JOIN and Subqueries:


SELECT sc.country_name
FROM soccer_country sc
JOIN match_details md ON sc.country_id = md.team_id
WHERE md.play_stage = 'F'
  AND md.team_id NOT IN (
    SELECT country_id
    FROM soccer_country
    WHERE country_name = 'Portugal'
  );

Explanation:

This query uses a subquery with NOT IN to find countries that are not Portugal. It then applies conditions to filter for matches in the final stage.

Using JOIN and Subqueries with ANY:


SELECT sc.country_name
FROM soccer_country sc
JOIN match_details md ON sc.country_id = md.team_id
WHERE md.play_stage = 'F'
  AND md.team_id <> ANY (
    SELECT country_id
    FROM soccer_country
    WHERE country_name = 'Portugal'
  );

Explanation:

This query uses the <> ANY syntax to achieve the same result as NOT IN. It filters for matches in the final stage and ensures the team is not Portugal.

Go to:

PREV : Matches, 2nd highest stoppage time in the 2nd half.
NEXT : Club that supplied the most players to the 2016 EURO.

Practice Online