SQL Exercise: Liverpool players in the England squad at 2016 EURO

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

19. From the following table, write a SQL query to find the Liverpool players who were part of England's squad at the 2016 Euro Cup. Return player name, jersey number, and position to play, age.

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, posi_to_play, and age from the player_mast table
SELECT player_name, jersey_no, posi_to_play, age
-- Filtering by playing_club and team_id
FROM player_mast 
-- Subquery to find country_id of England
WHERE playing_club='Liverpool'
AND team_id=(
    -- Selecting country_id of England
    SELECT country_id
    FROM soccer_country
    WHERE country_name='England'
);

Sample Output:

    player_name    | jersey_no | posi_to_play | age
-------------------+-----------+--------------+-----
 James Milner      |         4 | MF           |  30
 Adam Lallana      |         8 | MF           |  28
 Nathaniel Clyne   |        12 | DF           |  25
 Jordan Henderson  |        14 | MF           |  26
 Daniel Sturridge  |        15 | FD           |  26
(5 rows)

Code Explanation:

The said query in SQL that retrieves the player name, jersey number, position to play, and age for all players in the 'player_mast' table who play for the club "Liverpool" and whose team ID matches the ID of England in the 'soccer_country' table.
The subquery returns the ID of the England team in the 'soccer_country' table, and the outer query filters the 'player_mast' table to only include players who play for that team and for the "Liverpool" club.

Alternative Solution:

Using JOIN and Subquery:


-- Selecting player_name, jersey_no, posi_to_play, and age from the player_mast table
SELECT pm.player_name, pm.jersey_no, pm.posi_to_play, pm.age
-- 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 playing_club and country_name
WHERE pm.playing_club = 'Liverpool'
  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 players from the club 'Liverpool' playing for England.

Using EXISTS with Subquery:


-- Selecting player_name, jersey_no, posi_to_play, and age from the player_mast table
SELECT player_name, jersey_no, posi_to_play, age
-- Filtering by playing_club 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 playing_club = 'Liverpool'
  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 part of the club 'Liverpool'.

Go to:

PREV : Goalkeepers for the England squad for 2016 EURO cup.
NEXT : Who scored the last goal in the 2nd semi-final.

Practice Online