SQL Exercise: Number of goals Germany scored at the tournament

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

17. From the following tables, write a SQL query to find the number of goals Germany scored at the tournament.

Sample table: goal_details

 goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
       1 |        1 |    160159 |    1207 |        57 | N         | G          | NT            |         2
       2 |        1 |    160368 |    1216 |        65 | P         | G          | NT            |         2
       3 |        1 |    160154 |    1207 |        89 | N         | G          | NT            |         2
       4 |        2 |    160470 |    1221 |         5 | N         | G          | NT            |         1
       5 |        3 |    160547 |    1224 |        10 | N         | G          | NT            |         1
       6 |        3 |    160403 |    1218 |        61 | N         | G          | NT            |         2
       7 |        3 |    160550 |    1224 |        81 | N         | G          | NT            |         2
       8 |        4 |    160128 |    1206 |        73 | N         | G          | NT            |         2
       9 |        4 |    160373 |    1217 |        93 | N         | G          | ST            |         2
.......
     108 |       51 |    160319 |    1214 |       109 | N         | F          | ET            |         2

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:


-- Counting the number of player_id occurrences
SELECT COUNT(player_id) 
-- Filtering by team_id
FROM goal_details 
-- Subquery to find country_id of Germany
WHERE team_id=(
    -- Selecting country_id of Germany
    SELECT country_id 
    FROM soccer_country
    WHERE country_name='Germany'
);

Sample Output:

 count
-------
     7
(1 row)

Code Explanation:

The said query in SQL that retrieves the number of goals scored by the German team in the goal_details table.
1. The COUNT function counts the number of rows in the goal_details table.
2. The WHERE clause filters the results to only include rows where the team_id matches the country_id of Germany.
3. The country_id of Germany is obtained from a subquery that selects it from the soccer_country table, using a filter on the country_name column.

Alternative Solution:

Using JOIN and Subquery:


-- Counting the number of occurrences of player_id
SELECT COUNT(gd.player_id) 
-- Joining goal_details and soccer_country tables based on team_id and country_id respectively
FROM goal_details gd
JOIN soccer_country sc ON gd.team_id = sc.country_id
-- Filtering by country_name 'Germany'
WHERE sc.country_name = 'Germany';

Explanation:

This query uses a JOIN operation to connect the goal_details table with the soccer_country table based on team_id. It then filters the results to only include entries for Germany and counts the number of player_id.

Using EXISTS with Subquery:


-- Counting the number of occurrences of player_id
SELECT COUNT(player_id)
-- Selecting from the goal_details table
FROM goal_details gd
-- Checking for existence of records in soccer_country table
WHERE EXISTS (
    -- Subquery to check for existence of country_name 'Germany' and matching team_id
    SELECT 1
    FROM soccer_country sc
    WHERE sc.country_name = 'Germany'
    AND sc.country_id = gd.team_id
);

Explanation:

This query uses the EXISTS keyword along with a subquery to check if there exists a country with the name Germany in the soccer_country table, and if the team_id in goal_details matches that country's country_id.

Using Scalar Subquery:


-- Selecting player_count as a subquery result
SELECT (
    -- Counting the number of player_id occurrences
    SELECT COUNT(player_id)
    -- Filtering by team_id of Germany
    FROM goal_details
    WHERE team_id = (
        -- Selecting country_id of Germany
        SELECT country_id 
        FROM soccer_country
        WHERE country_name = 'Germany'
    )
) AS player_count;

Explanation:

This query uses a scalar subquery to directly retrieve the count of player_id from goal_details for the specified country (Germany). The result is aliased as player_count.

Go to:

PREV : Italy Goalkeeper in 2016 penalty shootout with Germany.
NEXT : Goalkeepers for the England squad for 2016 EURO cup.

Practice Online