SQL Exercise: Find the player who took the 26th penalty shot

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

37. From the following tables, write a SQL query to find the player who took the penalty shot number 26. Return match number, country name, player name.

Sample table: penalty_shootout

 kick_id | match_no | team_id | player_id | score_goal | kick_no
---------+----------+---------+-----------+------------+---------
       1 |       37 |    1221 |    160467 | Y          |       1
       2 |       37 |    1213 |    160297 | Y          |       2
       3 |       37 |    1221 |    160477 | N          |       3
       4 |       37 |    1213 |    160298 | Y          |       4
       5 |       37 |    1221 |    160476 | Y          |       5
       6 |       37 |    1213 |    160281 | Y          |       6
       7 |       37 |    1221 |    160470 | Y          |       7
       8 |       37 |    1213 |    160287 | Y          |       8
       9 |       37 |    1221 |    160469 | Y          |       9
........
      37 |       47 |    1208 |    160166 | Y          |      18

View the table

Sample table: player_mast

 player_id | team_id | jersey_no |       player_name       | posi_to_play | dt_of_bir  | age |    playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
    160001 |    1201 |         1 | Etrit Berisha           | GK           | 1989-03-10 |  27 | Lazio
    160008 |    1201 |         2 | Andi Lila               | DF           | 1986-02-12 |  30 | Giannina
    160016 |    1201 |         3 | Ermir Lenjani           | MF           | 1989-08-05 |  26 | Nantes
    160007 |    1201 |         4 | Elseid Hysaj            | DF           | 1994-02-20 |  22 | Napoli
    160013 |    1201 |         5 | Lorik Cana              | MF           | 1983-07-27 |  32 | Nantes
    160010 |    1201 |         6 | Frederic Veseli         | DF           | 1992-11-20 |  23 | Lugano
    160004 |    1201 |         7 | Ansi Agolli             | DF           | 1982-10-11 |  33 | Qarabag
    160012 |    1201 |         8 | Migjen Basha            | MF           | 1987-01-05 |  29 | Como
    160017 |    1201 |         9 | Ledian Memushaj         | MF           | 1986-12-17 |  29 | Pescara
......
    160548 |    1224 |        23 | Simon Church            | FD           | 1988-12-10 |  27 | MK Dons

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:

-- This query selects the match number, country name, and player name from the relevant tables for a specific penalty shootout kick.

SELECT 
    match_no, -- Selecting the match number
    country_name, -- Selecting the country name
    player_name -- Selecting the player name
FROM 
    penalty_shootout a -- Specifying the penalty_shootout table with an alias 'a'
JOIN 
    player_mast b ON a.player_id = b.player_id -- Joining the penalty_shootout table with the player_mast table based on the player_id
JOIN 
    soccer_country c ON b.team_id = c.country_id -- Joining the player_mast table with the soccer_country table based on the team_id
	-- Filtering for a specific penalty shootout kick (kick_id = 26)
WHERE 
    kick_id = 26;

Sample Output:

 match_no | country_name |  player_name
----------+--------------+----------------
       47 | Italy        | Graziano Pelle
(1 row)

Code Explanation:

The said query in SQL that retrieves the match number, country name, and player name from the penalty_shootout table, player_mast table, and soccer_country table respectively, for the kick with kick_id 26.
The JOIN statements joins the penalty_shootout table aliased as "a", and the player_mast table aliased as "b" based on the player_id column, and then join the result with the soccer_country table aliased as "c" based on the team_id and country_id columns.
The WHERE clause filters the results to only include rows where the kick_id column is equal to 26.

Alternative Solution:

Using INNER JOINs with Subquery:

-- This query selects the match number, country name, and player name for a penalty shootout kick with a specific kick_id.

SELECT 
    a.match_no, -- Selecting the match number from the penalty_shootout table aliased as 'a'
    c.country_name, -- Selecting the country name from the soccer_country table aliased as 'c'
    b.player_name -- Selecting the player name from the player_mast subquery aliased as 'b'
FROM 
    penalty_shootout a -- Specifying the penalty_shootout table with an alias 'a'
JOIN 
    (
        -- Subquery to select player information from the player_mast table
        SELECT 
            player_id, -- Selecting the player ID
            player_name, -- Selecting the player name
            team_id -- Selecting the team ID
        FROM 
            player_mast -- Specifying the player_mast table for the subquery
    ) b ON a.player_id = b.player_id -- Joining the subquery with the penalty_shootout table based on the player_id
JOIN 
    soccer_country c ON b.team_id = c.country_id -- Joining the soccer_country table with the player_mast subquery based on the team_id
	-- Filtering for a specific penalty shootout kick (kick_id = 26)
WHERE 
    a.kick_id = 26;

Explanation:

This query uses a subquery to first select necessary columns from player_mast. It then performs INNER JOINs to retrieve the desired information and applies the specified condition for kick_id = 26.

Relational Algebra Expression: