SQL Exercise: Player of portugal taken the 7th kick against poland

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

From the following table, write a SQL query to find the player of ‘Portugal’ who took the seventh kick against ‘Poland’. Return match number, player name and kick number.

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: 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 a.match_no,
       b.player_name,
       a.kick_no
FROM penalty_shootout a,
     player_mast b
WHERE a.player_id=b.player_id
  AND kick_no=7
  AND match_no=
    (SELECT match_no
     FROM penalty_shootout
     WHERE team_id =
         (SELECT country_id
          FROM soccer_country
          WHERE country_name='Portugal' )
     GROUP BY match_no)
GROUP BY match_no,
         player_name,
         kick_id;

Sample Output:

 match_no | player_name | kick_no
----------+-------------+---------
       45 | Nani        |       7
(1 row)

Code Explanation:

The said query in SQL that selects the match number, player name, and kick number for penalty shootout kicks that occurred in the 7th round of penalty kicks for a specific match
from penalty_shootout and player_mast table in which Portugal was one of the teams playing.
The first condition joins the two tables based on the player_id column.
The second condition specified in the query should only retrieve data for the 7th kick in the penalty shootout.
The third condition retrieves data for a specific match in which Portugal was one of the teams playing. This condition uses a subquery to find the match_no for the specified match. The GROUP BY clause groups the data by match_no, player_name, and kick_id.

Alternative Solution:

Subquery with MAX and HAVING:


SELECT a.match_no, b.player_name, a.kick_no
FROM penalty_shootout a
JOIN player_mast b ON a.player_id = b.player_id
WHERE a.kick_no = 7
  AND a.match_no = (
    SELECT match_no
    FROM penalty_shootout
    WHERE team_id = (
      SELECT country_id
      FROM soccer_country
      WHERE country_name = 'Portugal'
    )
    GROUP BY match_no
    HAVING COUNT(*) > 0
  )
GROUP BY a.match_no, b.player_name, a.kick_no;

JOIN with Subquery:


SELECT a.match_no, b.player_name, a.kick_no
FROM penalty_shootout a
JOIN player_mast b ON a.player_id = b.player_id
JOIN (
  SELECT match_no
  FROM penalty_shootout
  WHERE team_id = (
    SELECT country_id
    FROM soccer_country
    WHERE country_name = 'Portugal'
  )
  GROUP BY match_no
  HAVING COUNT(*) > 0
) c ON a.match_no = c.match_no
WHERE a.kick_no = 7
GROUP BY a.match_no, b.player_name, a.kick_no;

Using EXISTS:


SELECT a.match_no, b.player_name, a.kick_no
FROM penalty_shootout a
JOIN player_mast b ON a.player_id = b.player_id
WHERE a.kick_no = 7
  AND EXISTS (
    SELECT 1
    FROM penalty_shootout p
    WHERE p.match_no = a.match_no
      AND p.team_id = (
        SELECT country_id
        FROM soccer_country
        WHERE country_name = 'Portugal'
      )
  )
GROUP BY a.match_no, b.player_name, a.kick_no;

Using JOIN with Subquery:


SELECT a.match_no, b.player_name, a.kick_no
FROM penalty_shootout a
JOIN player_mast b ON a.player_id = b.player_id
JOIN (
  SELECT match_no
  FROM penalty_shootout
  WHERE team_id = (
    SELECT country_id
    FROM soccer_country
    WHERE country_name = 'Portugal'
  )
  GROUP BY match_no
  HAVING COUNT(*) > 0
) c ON a.match_no = c.match_no
WHERE a.kick_no = 7
GROUP BY a.match_no, b.player_name, a.kick_no;

Go to:

PREV : Match highest number of penalty shots had been taken.
NEXT : Find where the penalty kick number 23 had been taken.

Practice Online