SQL Exercise: Find the number of penalty shots taken by the teams

Last update on January 13 2026 12:20:21 (UTC/GMT +8 hours)

26. From the following table, write a SQL query to count the number of penalty shots taken by each team. Return country name, number of shots as "Number of Shots".

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: 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 Solution:

SQL Code:

-- This SQL query calculates the count of penalty shots for each country participating in soccer_country.

SELECT a.country_name, COUNT(b.*) as "Number of Shots"
-- Selects the country name and the count of penalty shots for each group, aliasing the count as "Number of Shots".
FROM soccer_country a, penalty_shootout b
-- Specifies the tables involved in the query, using aliases 'a' for 'soccer_country' and 'b' for 'penalty_shootout'.
WHERE b.team_id = a.country_id
-- Connects rows in 'penalty_shootout' where 'team_id' matches 'country_id' in 'soccer_country'.
GROUP BY a.country_name;
-- Groups the results by the 'country_name' column from 'soccer_country'.

Sample Output:

 country_name | Number of Shots
--------------+-----------------
 Poland       |               9
 Italy        |               9
 Germany      |               9
 Portugal     |               5
 Switzerland  |               5
(5 rows)

Code Explanation:

The said query in SQL that retrieves the number of penalty shootout shots taken by each country.
This query counts the number of rows from the penalty_shootout table and the "Number of Shots" is used as a alias to the count result.
The join condition between the two tables, linking the team_id column from penalty_shootout with the country_id column from soccer_country.
The GROUP BY clause groups the results by country_name, so that the count is calculated for each unique country in the soccer_country table.

Go to:

PREV : List of players with shot numbers in penalty shootouts.
NEXT : Within each half of play, find the number of bookings.

Practice Online