SQL Exercise: Find the captain who was also the goalkeeper
39. From the following tables, write a SQL query to find the captain who was also the goalkeeper. Return match number, country name, player name and jersey number.
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
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
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
Sample Solution:
SQL Code:
-- This query selects the match number, country name, player name, and jersey number for match captains who play as goalkeepers.
SELECT
match_no, -- Selecting the match number
country_name, -- Selecting the country name
player_name, -- Selecting the player name
jersey_no -- Selecting the jersey number
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
AND
posi_to_play = 'GK' -- Filtering the players who play as goalkeepers
-- Ordering the results by match number
ORDER BY
match_no;
Sample Output:
match_no | country_name | player_name | jersey_no
----------+----------------+-------------------+-----------
1 | France | Hugo Lloris | 1
7 | Germany | Manuel Neuer | 1
10 | Italy | Gianluigi Buffon | 1
15 | France | Hugo Lloris | 1
18 | Germany | Manuel Neuer | 1
19 | Italy | Gianluigi Buffon | 1
26 | France | Hugo Lloris | 1
30 | Germany | Manuel Neuer | 1
31 | Czech Republic | Petr Cech | 1
40 | France | Hugo Lloris | 1
41 | Germany | Manuel Neuer | 1
43 | Italy | Gianluigi Buffon | 1
47 | Germany | Manuel Neuer | 1
47 | Italy | Gianluigi Buffon | 1
48 | France | Hugo Lloris | 1
50 | France | Hugo Lloris | 1
51 | France | Hugo Lloris | 1
(17 rows)
Code Explanation:
The given query in SQL that retrieves the match number, country name, player name, and jersey number from the match_captain table aliased as "a", soccer_country table aliased as "b", and player_mast table aliased as "c" respectively. The query filters the results to only include players who play as goalkeeper (GK), and orders the results by match number.
The JOIN clause joins the match_captain table and the soccer_country table based on the team_id and country_id columns, and then join the result with the player_mast table based on the player_captain and player_id columns.
The WHERE clause filters the results to only include rows where the posi_to_play column is equal to 'GK', indicating that the player plays as a goalkeeper.
The ORDER BY statement sorts the results by match_no in ascending order.
Alternative Solution:
Using JOINs and IN Clause:
-- This query selects the match number, country name, player name, and jersey number for match captains who play as goalkeepers.
SELECT
a.match_no, -- Selecting the match number from the match_captain table aliased as 'a'
b.country_name, -- Selecting the country name from the soccer_country table aliased as 'b'
c.player_name, -- Selecting the player name from the subquery aliased as 'c'
c.jersey_no -- Selecting the jersey number from the subquery aliased as 'c'
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, jersey_no, 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
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table aliased as 'b' with the match_captain table based on the team_id
-- Ordering the results by match number
ORDER BY
a.match_no;
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.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the team that took penalty shot number 26.
NEXT : Find the number of captains who were also goalkeepers.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.