SQL Exercise: Goalkeepers for the England squad for 2016 EURO cup

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

18. From the following table, write a SQL query to find the players who were the goalkeepers of the England team that played in the 2016-EURO. Return player name, jersey number, club name

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

-- Selecting player_name, jersey_no, and playing_club from the player_mast table
SELECT player_name, jersey_no, playing_club 
-- Filtering by posi_to_play and team_id
FROM player_mast 
-- Subquery to find country_id of England
WHERE posi_to_play='GK' AND team_id=(
    -- Selecting country_id of England
    SELECT country_id 
    FROM soccer_country
    WHERE country_name='England'
);

Sample Output:

   player_name   | jersey_no | playing_club
-----------------+-----------+--------------
 Joe Hart        |         1 | Man. City
 Fraser Forster  |        13 | Southampton
 Tom Heaton      |        23 | Burnley
(3 rows)

Code Explanation:

The said query in SQL that selects the name, jersey number, and playing club of all goalkeepers who play for a club in England. It does this by first selecting the ID of the country with the name 'England' from the 'soccer_country' table using a subquery, and then selecting the player name, jersey number, and playing club from the 'player_mast' table where the player's position to play is 'GK' and the team ID matches the ID of England.

Alternative Solution:

Using JOIN and Subquery:


-- Selecting player_name, jersey_no, and playing_club from the player_mast table
SELECT pm.player_name, pm.jersey_no, pm.playing_club
-- Joining player_mast and soccer_country tables based on team_id and country_id respectively
FROM player_mast pm
JOIN soccer_country sc ON pm.team_id = sc.country_id
-- Filtering by posi_to_play and country_name
WHERE pm.posi_to_play = 'GK'
  AND sc.country_name = 'England';

Explanation:

This query uses a JOIN operation to connect the player_mast table with the soccer_country table based on team_id. It then filters the results to only include goalkeepers playing for England.

Using EXISTS with Subquery:


-- Selecting player_name, jersey_no, and playing_club from the player_mast table
SELECT player_name, jersey_no, playing_club
-- Filtering by posi_to_play and existence of a record in soccer_country table
FROM player_mast pm
-- Subquery to check for existence of team_id matching country_id for England
WHERE posi_to_play = 'GK'
  AND EXISTS (
    -- Subquery to check for existence of country_name 'England' and matching team_id
    SELECT 1
    FROM soccer_country sc
    WHERE sc.country_name = 'England'
    AND pm.team_id = sc.country_id
);

Explanation:

This query uses the EXISTS keyword along with a subquery to check if there exists a country with the name England in the soccer_country table, and if the team_id in player_mast matches that country's country_id. Additionally, it ensures that the player is a goalkeeper.

Go to:

PREV : Number of goals Germany scored at the tournament.
NEXT : Liverpool players in the England squad at 2016 EURO.

Practice Online