SQL Exercise: Captains of the top four teams in the semifinals

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

31. From the following tables, write a SQL query to find the captains of the top four teams that competed in the semi-finals (matches 48 and 49) of the tournament. Return country name, player name, jersey number and position to play.

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

 match_no | team_id | player_captain
----------+---------+----------------
        1 |    1207 |         160140
        1 |    1216 |         160349
        2 |    1201 |         160013
        2 |    1221 |         160467
        3 |    1224 |         160539
        3 |    1218 |         160401
        4 |    1206 |         160136
        4 |    1217 |         160373
        5 |    1222 |         160494
........
       51 |    1207 |         160140

View the table

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

SQL Code:

-- This query selects the country name, player name, jersey number, and position to play for match captains in specific matches.

SELECT 
    country_name, -- Selecting the country name from the soccer_country table aliased as 'b'
    player_name, -- Selecting the player name from the player_mast table aliased as 'c'
    jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'c'
    posi_to_play -- Selecting the position to play from the player_mast table aliased as 'c'
FROM 
    match_captain a -- Specifying the match_captain table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_captain table based on the team_id
JOIN 
    player_mast c ON a.player_captain = c.player_id -- Joining the player_mast table with the match_captain table based on the player_captain
	-- Filtering captains for matches with match numbers 48 and 49
WHERE 
    match_no IN (48, 49);

Sample Output:

 country_name |    player_name     | jersey_no | posi_to_play
--------------+--------------------+-----------+--------------
 France       | Hugo Lloris        |         1 | GK
 Iceland      | Aron Gunnarsson    |        17 | MF
 Portugal     | Cristiano Ronaldo  |         7 | FD
 Wales        | Ashley Williams    |         6 | DF
(4 rows)

Code Explanation:

The said query in SQL that retrieves the captain's name, jersey number, position to play, and country name for the teams that played in the matches with match numbers 48 and 49.
The JOIN keyword joins the 'match_captain' and the 'soccer_country' tables based on the "team_id" and the "country_id" columns and another joins with the 'player_mast' and the 'match_captain' tables based on the "player_id" and the "player_captain" columns from the respective tables.
The WHERE clause in this query filters the teams that played in the matches with match numbers 48 and 49.

Alternative Solutions:

Using INNER JOINs with Subquery:

-- This query selects the country name, player name, jersey number, and position to play for match captains in specific matches.

SELECT 
    b.country_name, -- Selecting the country name from the soccer_country table aliased as 'b'
    c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
    c.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'c'
    c.posi_to_play -- Selecting the position to play from the player_mast table aliased as 'c'
FROM 
    match_captain a -- Specifying the match_captain table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_captain table based on the team_id
JOIN 
    player_mast c ON a.player_captain = c.player_id -- Joining the player_mast table with the match_captain table based on the player_captain
WHERE 
    a.match_no IN ( -- Filtering match captains for matches with match numbers 48 and 49
        SELECT 
            match_no -- Selecting match numbers from the subquery
        FROM 
            match_captain -- Specifying the match_captain table for the subquery
        WHERE 
            match_no IN (48, 49) -- Filtering matches with match numbers 48 and 49
    );

Explanation:

This query uses a subquery to first find match numbers 48 and 49. It then performs INNER JOINs to retrieve the corresponding details of the captain and their team.

Using INNER JOINs with OR Clause:

-- This query selects the country name, player name, jersey number, and position to play for match captains in specific matches (match numbers 48 or 49).

SELECT 
    b.country_name, -- Selecting the country name from the soccer_country table aliased as 'b'
    c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
    c.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'c'
    c.posi_to_play -- Selecting the position to play from the player_mast table aliased as 'c'
FROM 
    match_captain a -- Specifying the match_captain table with an alias 'a'
JOIN 
    soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the match_captain table based on the team_id
JOIN 
    player_mast c ON a.player_captain = c.player_id -- Joining the player_mast table with the match_captain table based on the player_captain
	-- Filtering match captains for matches with match numbers 48 or 49
WHERE 
    a.match_no = 48 OR a.match_no = 49;

Explanation:

This query first uses an OR clause in the WHERE condition to specify that the match number should be either 48 or 49. It then performs INNER JOINs to retrieve the necessary details.

Relational Algebra Expression: