SQL Exercise: German player who did not concede a goal in group stage

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

23. From the following table, write a SQL query to find the Germany goalkeeper who did not concede any goals in their group stage matches. Return goalkeeper name, jersey number.

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

 match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
        1 | G          |    1207 | W        | N          |          2 |               |   80016 |    160140
        1 | G          |    1216 | L        | N          |          1 |               |   80020 |    160348
        2 | G          |    1201 | L        | N          |          0 |               |   80003 |    160001
        2 | G          |    1221 | W        | N          |          1 |               |   80023 |    160463
        3 | G          |    1224 | W        | N          |          2 |               |   80031 |    160532
        3 | G          |    1218 | L        | N          |          1 |               |   80025 |    160392
        4 | G          |    1206 | D        | N          |          1 |               |   80008 |    160117
        4 | G          |    1217 | D        | N          |          1 |               |   80019 |    160369
        5 | G          |    1222 | L        | N          |          0 |               |   80011 |    160486
..........
       51 | F          |    1207 | L        | N          |          0 |               |   80007 |    160140

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 player_name,jersey_no 
FROM player_mast 
WHERE player_id IN(
SELECT player_gk 
FROM match_details 
WHERE  play_stage='G' and team_id IN(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Germany'));

Sample Output:

  player_name  | jersey_no
---------------+-----------
 Manuel Neuer  |         1
(1 row)

Code Explanation:

The describe query in SQL that selects the player name and jersey number of all goalkeepers who played for the German team in the group stage of a soccer match.
The innermost subquery selects the country_id from the soccer_country table where the country_name is 'Germany'.
The subquery out of the innermost selects the player_gk column from the match_details table where the play_stage is 'G' and the team_id matches in the subquery result set.
The outer query then retrieves the player_name and jersey_no columns from the player_mast table.

Alternative Solution:

Using JOIN and Subqueries:


SELECT distinct pm.player_name, pm.jersey_no
FROM player_mast pm
JOIN match_details md ON pm.player_id = md.player_gk
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE md.play_stage = 'G' AND sc.country_name = 'Germany';

Explanation:

This query uses JOIN operations to connect the player_mast table with match_details and soccer_country based on their respective IDs. It then applies the specified conditions to filter the results.

Using EXISTS:


SELECT player_name, jersey_no
FROM player_mast pm
WHERE EXISTS (
    SELECT 1
    FROM match_details md
    JOIN soccer_country sc ON md.team_id = sc.country_id
    WHERE pm.player_id = md.player_gk
      AND md.play_stage = 'G'
      AND sc.country_name = 'Germany'
);

Explanation:

This query uses the EXISTS keyword with a subquery to check if there exists a record in match_details and soccer_country that satisfies the specified conditions.

Using JOIN with Subquery:


SELECT distinct pm.player_name, pm.jersey_no
FROM player_mast pm
JOIN (
    SELECT md.player_gk
    FROM match_details md
    JOIN soccer_country sc ON md.team_id = sc.country_id
    WHERE md.play_stage = 'G'
      AND sc.country_name = 'Germany'
) AS german_goalkeepers ON pm.player_id = german_goalkeepers.player_gk;

Explanation:

This query first creates a subquery that retrieves the player IDs of goalkeepers in matches of the specified stage and for the team with the specified country name. It then joins this subquery with the player_mast table to get the names and jersey numbers of those players.

Go to:

PREV : Number of players played for france in the final.
NEXT : Find the runners-up in Football EURO cup 2016.

Practice Online