SQL Exercise: Find the maximum number of penalty shots taken

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

26. From the following tables, write a SQL query to find the maximum number of penalty shots taken by the players. Return country name, player name, jersey number and number of penalty shots.

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 : 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 c.country_name,a.player_name, a.jersey_no,COUNT(b.*) shots 
FROM player_mast a, penalty_shootout b, soccer_country c
WHERE b.player_id=a.player_id
AND b.team_id=c.country_id
GROUP BY c.country_name,a.player_name,a.jersey_no
HAVING COUNT(b.*)=(
SELECT MAX(shots) FROM (
SELECT COUNT(*) shots 
FROM penalty_shootout
GROUP BY player_id) inner_result);

Sample Output:

 country_name |      player_name      | jersey_no | shots
--------------+-----------------------+-----------+-------
 Poland       | Jakub Blaszczykowski  |        16 |     2
 Poland       | Arkadiusz Milik       |         7 |     2
 Poland       | Robert Lewandowski    |         9 |     2
 Poland       | Kamil Glik            |        15 |     2
(4 rows)

Code Explanation:

The said query in SQL that retrieves data about penalty shootout performance of soccer players from different countries.
It joins the player_mast, penalty_shootout, and soccer_country tables.
Filters the results by matching player IDs in the player_mast and penalty_shootout tables and matching team IDs in the penalty_shootout and soccer_country tables.
Groups the results by country name, player name, and jersey number.
Filters the results by selecting only the players with the maximum number of shots taken during a penalty shootout. The subquery counts the number of shots taken by each player and returns the maximum number of shots taken. The outer query then selects only the players with the maximum number of shots taken.

Alternative Solution:

Using JOIN and Subqueries:


SELECT c.country_name, a.player_name, a.jersey_no, COUNT(b.*) AS shots 
FROM player_mast a
JOIN penalty_shootout b ON a.player_id = b.player_id
JOIN soccer_country c ON b.team_id = c.country_id
GROUP BY c.country_name, a.player_name, a.jersey_no
HAVING COUNT(b.*) = (
    SELECT MAX(shots)
    FROM (
        SELECT COUNT(*) AS shots 
        FROM penalty_shootout
        GROUP BY player_id
    ) AS inner_result
);

Explanation:

This query uses JOIN operations to connect the player_mast, penalty_shootout, and soccer_country tables based on their respective IDs. It then applies GROUP BY and HAVING clauses to count the shots for each player and country and find the player with the maximum shots.

Using Subqueries with JOINs:


SELECT c.country_name, a.player_name, a.jersey_no, COUNT(b.*) AS shots 
FROM player_mast a
JOIN penalty_shootout b ON b.player_id = a.player_id
JOIN soccer_country c ON b.team_id = c.country_id
WHERE (
    SELECT COUNT(*) 
    FROM penalty_shootout d
    WHERE d.player_id = a.player_id
) = (
    SELECT MAX(shots)
    FROM (
        SELECT COUNT(*) AS shots 
        FROM penalty_shootout
        GROUP BY player_id
    ) AS inner_result
)
GROUP BY c.country_name, a.player_name, a.jersey_no;

Explanation:

This query uses subqueries with joins to first calculate the shots count for each player. It then applies a WHERE clause to filter the players with the maximum shots and uses GROUP BY to count the shots for each player and country.

Go to:

PREV : Find the maximum penalty shots taken by the teams.
NEXT : Find the match where highest number of penalty taken.

Practice Online