SQL Exercise: Find the results of penalty shootout matches

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

59. From the following table, write a SQL query to find the results of penalty shootout matches. Return match number, play stage, country name and penalty score.

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:

-- Selecting match information including match number, play stage, country name, and penalty score
SELECT 
match_no, -- Selecting the match number
play_stage, -- Selecting the stage of play
country_name, -- Selecting the name of the country
penalty_score -- Selecting the penalty score
FROM 
match_details a -- Selecting from the match_details table with alias 'a'
JOIN 
soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE 
decided_by = 'P' -- Filtering records where the match was decided by penalty
-- Ordering the results by match number
ORDER BY 
match_no;

Sample Output:

 match_no | play_stage | country_name | penalty_score
----------+------------+--------------+---------------
       37 | R          | Poland       |             5
       37 | R          | Switzerland  |             4
       45 | Q          | Poland       |             3
       45 | Q          | Portugal     |             5
       47 | Q          | Germany      |             6
       47 | Q          | Italy        |             5
(6 rows)

Code Explanation:

The said query in SQL that selects a list of matches that were decided by penalty shootouts, along with their relevant information such as the stage of play, the name of the country that played in the match, and the penalty score.
The JOIN clause combines match_details and soccer_country tables based on the team_id and country_id columns.
The WHERE clause filters the results to only include matches that were decided by penalties. The 'P' is an abbreviation for penalty shootouts.
The results are sorted by match number in ascending order.

Alternative Solutions:

Using a Subquery with IN Clause:

-- Selecting match information including match number, play stage, country name, and penalty score
SELECT 
match_no, -- Selecting the match number
play_stage, -- Selecting the stage of play
country_name, -- Selecting the name of the country
penalty_score -- Selecting the penalty score
FROM 
match_details a -- Selecting from the match_details table with alias 'a'
JOIN 
soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE 
decided_by = 'P' -- Filtering records where the match was decided by penalty
    AND a.team_id IN ( --Subquery to filter team IDs present in the soccer_country table
        SELECT 
country_id -- Selecting the country IDs
        FROM 
soccer_country -- Subquery selecting from the soccer_country table
    )
	-- Ordering the results by match number
ORDER BY 
match_no;

Explanation:

This query uses a subquery with the IN clause to filter the results based on the country_id. It ensures that the country_id is present in the soccer_country table.

Using EXISTS Clause:

-- Selecting match information including match number, play stage, country name, and penalty score
SELECT 
match_no, -- Selecting the match number
play_stage, -- Selecting the stage of play
country_name, -- Selecting the name of the country
penalty_score -- Selecting the penalty score
FROM 
match_details a -- Selecting from the match_details table with alias 'a'
JOIN 
soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE 
decided_by = 'P' -- Filtering records where the match was decided by penalty
    AND EXISTS ( -- Checking for existence of matching records in soccer_country table
        SELECT 
            1 -- Dummy column value to indicate existence
        FROM 
soccer_country c -- Subquery selecting from the soccer_country table with alias 'c'
        WHERE 
a.team_id = c.country_id -- Matching team IDs
    )
	-- Ordering the results by match number
ORDER BY 
match_no;

Explanation:

This query uses the EXISTS clause to check for the existence of a matching country_id in the soccer_country table.

Relational Algebra Expression: