SQL Exercise: Find the number of captains who were also goalkeepers

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

40. From the following tables, write a SQL query to find the number of captains who was also the goalkeeper. Return number of captains.

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

-- This query counts the number of distinct player names who serve as match captains and play as goalkeepers.

SELECT 
    count(DISTINCT player_name) -- Counting the number of distinct player names
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 match_captain table with the soccer_country table based on the team_id
JOIN 
    player_mast c ON a.player_captain = c.player_id -- Joining the match_captain table with the player_mast table based on the player_captain
	-- Filtering the players who play as goalkeepers
AND 
    posi_to_play = 'GK';

Sample Output:

 count
-------
     4
(1 row)

Code Explanation:

The said query in SQL that retrieves the number of distinct player names who have played as goalkeepers and acted as captains in matches for their respective countries.
The JOIN clause joins the tables match_captain and the soccer_country based on the columns team_id and country_id and join the player_mast with the result set based on the player_captain column of match_captain table and player_id column of player_mast table.
The WHERE clause filters the results to only include players who have played in the goalkeeper position.

Alternative Solution:

Using INNER JOINs with Subquery and COUNT:

-- This query counts the number of distinct player names who serve as match captains and play as goalkeepers.

SELECT 
    COUNT(DISTINCT c.player_name) -- Counting the number of distinct player names
FROM 
    match_captain a -- Specifying the match_captain table with an alias 'a'
JOIN 
    ( -- Subquery to select players who play as goalkeepers
        SELECT 
            player_id, player_name, posi_to_play
        FROM 
            player_mast
        WHERE 
            posi_to_play = 'GK' -- Filtering players who play as goalkeepers
    ) c ON a.player_captain = c.player_id -- Joining the subquery aliased as 'c' with the match_captain table based on the player_captain
	-- Joining the soccer_country table aliased as 'b' with the match_captain table based on the team_id
JOIN 
    soccer_country b ON a.team_id = b.country_id;

Explanation:

This query uses a subquery to first select necessary columns from player_mast with the condition posi_to_play = 'GK'. It then performs INNER JOINs to retrieve the desired information and counts the distinct player names.

Relational Algebra Expression: