SQL Exercise: Find the player of each team who wear jersey number 10

Last update on January 15 2026 12:41:17 (UTC/GMT +8 hours)

From the following tables, write a SQL query to find those players on each team who wore jersey number 10. Return country name, player name, position to play, age and playing club.

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 the country name, player name, position to play, age, and playing club
SELECT 
    country_name, -- Selecting the name of the country
    player_name, -- Selecting the name of the player
    posi_to_play, -- Selecting the position to play
    age, -- Selecting the age of the player
    playing_club -- Selecting the club the player is currently playing for
FROM 
    player_mast a -- Selecting from the player_mast table with alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE 
    jersey_no = 10 -- Filtering records where the jersey number is 10
	-- Ordering the results by country name
ORDER BY 
    country_name;

Sample Output:

    country_name     |     player_name      | posi_to_play | age |  playing_club

---------------------+----------------------+--------------+-----+-----------------
 Albania             | Armando Sadiku       | FD           |  25 | Vaduz
 Austria             | Zlatko Junuzovic     | MF           |  28 | Bremen
 Belgium             | Eden Hazard          | MF           |  25 | Chelsea
 Croatia             | Luka Modric          | MF           |  30 | Real Madrid
 Czech Republic      | TomasRosicky         | MF           |  35 | Arsenal
 England             | Wayne Rooney         | FD           |  30 | Man. United
 France              | Andre-Pierre Gignac  | FD           |  30 | Tigres
 Germany             | Lukas Podolski       | FD           |  31 | Galatasaray
 Hungary             | Zoltan Gera          | FD           |  37 | Ferencvaros
 Iceland             | Gylfi Sigurdsson     | MF           |  26 | Swansea
 Italy               | Thiago Motta         | MF           |  33 | Paris
 Northern Ireland    | Kyle Lafferty        | FD           |  28 | Birmingham
 Poland              | Grzegorz Krychowiak  | MF           |  26 | Sevilla
 Portugal            | Joao Mario           | MF           |  23 | Sporting CP
 Republic of Ireland | Robbie Keane         | FD           |  35 | LA Galaxy
 Romania             | Nicolae Stanciu      | MF           |  23 | Steaua
 Russia              | Fedor Smolov         | FD           |  26 | Krasnodar
 Slovakia            | Miroslav Stoch       | MF           |  26 | Bursaspor
 Spain               | Cesc Fabregas        | MF           |  29 | Chelsea
 Sweden              | Zlatan Ibrahimovic   | FD           |  34 | Paris
 Switzerland         | Granit Xhaka         | MF           |  23 | Monchengladbach
 Turkey              | Arda Turan           | MF           |  29 | Barcelona
 Ukraine             | Yevhen Konoplyanka   | MF           |  26 | Sevilla
 Wales               | Aaron Ramsey         | MF           |  25 | Arsenal
(24 rows)

Code Explanation:

The said query in SQL that retrieves information about soccer players who wear jersey number 10, including their country of origin, name, position to play, age, and current club. The results are sorted by country name from the player_mast table.
The WHERE clause that specified must be met for a player to be included in the results that they must have a jersey number of 10.
The results is sorted by country name, in ascending order.

Alternative Solutions:

Using a Subquery with IN Clause:

-- Selecting the country name, player name, position to play, age, and playing club
SELECT 
    country_name, -- Selecting the name of the country
    player_name, -- Selecting the name of the player
    posi_to_play, -- Selecting the position to play
    age, -- Selecting the age of the player
    playing_club -- Selecting the club the player is currently playing for
FROM 
    player_mast a -- Selecting from the player_mast table with alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE 
    a.jersey_no = 10 -- Filtering records where the jersey number is 10
    AND a.team_id IN ( -- Subquery to ensure team ID exists in soccer_country table
        SELECT country_id -- Selecting the country ID
        FROM soccer_country -- Selecting from the soccer_country table
    )
	-- Ordering the results by country name
ORDER BY 
    country_name;

Explanation:

This query uses a subquery with the IN clause to filter the results based on the country_id. It ensures that the country_id is present in the soccer_country table.

Using EXISTS Clause:

-- Selecting the country name, player name, position to play, age, and playing club
SELECT 
    country_name, -- Selecting the name of the country
    player_name, -- Selecting the name of the player
    posi_to_play, -- Selecting the position to play
    age, -- Selecting the age of the player
    playing_club -- Selecting the club the player is currently playing for
FROM 
    player_mast a -- Selecting from the player_mast table with alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining with the soccer_country table with alias 'b' based on team ID
WHERE 
    a.jersey_no = 10 -- Filtering records where the jersey number is 10
    AND EXISTS ( -- Checking existence of records in the subquery
        SELECT 1 -- Selecting 1 to check existence
        FROM soccer_country c -- Selecting from the soccer_country table with alias 'c'
        WHERE a.team_id = c.country_id -- Matching team ID between the main query and subquery
    )
	-- Ordering the results by country name
ORDER BY 
    country_name;

Explanation:

This query uses the EXISTS clause to check for the existence of a matching country_id in the soccer_country table.

Relational Algebra Expression: