SQL Exercise: All the captains and goalkeepers for all the teams
33. From the following tables, write a SQL query to find the captain and goalkeeper of all the matches. Return match number, Captain, Goal Keeper and country name.
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: 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: match_details
match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
1 | G | 1207 | W | N | 2 | | 80016 | 160140
1 | G | 1216 | L | N | 1 | | 80020 | 160348
2 | G | 1201 | L | N | 0 | | 80003 | 160001
2 | G | 1221 | W | N | 1 | | 80023 | 160463
3 | G | 1224 | W | N | 2 | | 80031 | 160532
3 | G | 1218 | L | N | 1 | | 80025 | 160392
4 | G | 1206 | D | N | 1 | | 80008 | 160117
4 | G | 1217 | D | N | 1 | | 80019 | 160369
5 | G | 1222 | L | N | 0 | | 80011 | 160486
.......
51 | F | 1207 | L | N | 0 | | 80007 | 160140
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, captain's name, goalkeeper's name, and country name for each match.
SELECT
a.match_no, -- Selecting the match number from the match_captain table aliased as 'a'
c.player_name as "Captain", -- Selecting the player name from the player_mast table aliased as 'c', labeled as "Captain"
d.player_name as "Goal Keeper", -- Selecting the player name from the player_mast table aliased as 'd', labeled as "Goal Keeper"
e.country_name -- Selecting the country name from the soccer_country table aliased as 'e'
FROM
match_captain a -- Specifying the match_captain table with an alias 'a'
NATURAL JOIN
match_details b -- Joining the match_captain table with the match_details table based on common columns (matching columns automatically), aliased as 'b'
JOIN
soccer_country e ON b.team_id = e.country_id -- Joining the soccer_country table with the match_details 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, aliased as 'c'
-- Joining the player_mast table with the match_details table based on the player_gk, aliased as 'd'
JOIN
player_mast d ON b.player_gk = d.player_id;
Sample Output:
match_no | Captain | Goal Keeper | country_name
----------+-------------------------+---------------------+--------------------
1 | Hugo Lloris | Hugo Lloris | France
1 | Vlad Chiriches | Ciprian Tatarusanu | Romania
2 | Lorik Cana | Etrit Berisha | Albania
2 | Stephan Lichtsteiner | Yann Sommer | Switzerland
3 | Ashley Williams | Danny Ward | Wales
3 | Martin Skrtel | MatusKozacik | Slovakia
4 | Wayne Rooney | Joe Hart | England
4 | Vasili Berezutski | Igor Akinfeev | Russia
5 | Arda Turan | Volkan Babacan | Turkey
5 | Darijo Srna | Danijel SubaSic | Croatia
6 | Robert Lewandowski | Wojciech Szczesny | Poland
6 | Steven Davis | Michael McGovern | Northern Ireland
7 | Manuel Neuer | Manuel Neuer | Germany
7 | Vyacheslav Shevchuk | Andriy Pyatov | Ukraine
8 | Sergio Ramos | David de Gea | Spain
8 | TomasRosicky | Petr Cech | Czech Republic
9 | John OShea | Darren Randolph | Republic of Ireland
9 | Zlatan Ibrahimovic | Andreas Isaksson | Sweden
10 | Eden Hazard | Thibaut Courtois | Belgium
10 | Gianluigi Buffon | Gianluigi Buffon | Italy
11 | Christian Fuchs | Robert Almer | Austria
11 | Balazs Dzsudzsak | Gabor Kiraly | Hungary
12 | Cristiano Ronaldo | Rui Patricio | Portugal
12 | Aron Gunnarsson | Hannes Halldorsson | Iceland
13 | Vasili Berezutski | Igor Akinfeev | Russia
13 | Martin Skrtel | MatusKozacik | Slovakia
14 | Vlad Chiriches | Ciprian Tatarusanu | Romania
14 | Stephan Lichtsteiner | Yann Sommer | Switzerland
15 | Hugo Lloris | Hugo Lloris | France
15 | Ansi Agolli | Etrit Berisha | Albania
16 | Wayne Rooney | Joe Hart | England
16 | Ashley Williams | Wayne Hennessey | Wales
17 | Vyacheslav Shevchuk | Andriy Pyatov | Ukraine
17 | Steven Davis | Michael McGovern | Northern Ireland
18 | Manuel Neuer | Manuel Neuer | Germany
18 | Robert Lewandowski | Lukasz Fabianski | Poland
19 | Gianluigi Buffon | Gianluigi Buffon | Italy
19 | Zlatan Ibrahimovic | Andreas Isaksson | Sweden
20 | TomasRosicky | Petr Cech | Czech Republic
20 | Darijo Srna | Danijel SubaSic | Croatia
21 | Sergio Ramos | David de Gea | Spain
21 | Arda Turan | Volkan Babacan | Turkey
22 | Eden Hazard | Thibaut Courtois | Belgium
22 | John OShea | Darren Randolph | Republic of Ireland
23 | Aron Gunnarsson | Hannes Halldorsson | Iceland
23 | Balazs Dzsudzsak | Gabor Kiraly | Hungary
24 | Cristiano Ronaldo | Rui Patricio | Portugal
24 | Christian Fuchs | Robert Almer | Austria
25 | Vlad Chiriches | Ciprian Tatarusanu | Romania
25 | Ansi Agolli | Etrit Berisha | Albania
26 | Stephan Lichtsteiner | Yann Sommer | Switzerland
26 | Hugo Lloris | Hugo Lloris | France
27 | Roman Shirokov | Igor Akinfeev | Russia
27 | Ashley Williams | Wayne Hennessey | Wales
28 | Martin Skrtel | MatusKozacik | Slovakia
28 | Gary Cahill | Joe Hart | England
29 | Ruslan Rotan | Andriy Pyatov | Ukraine
29 | Robert Lewandowski | Lukasz Fabianski | Poland
30 | Steven Davis | Michael McGovern | Northern Ireland
30 | Manuel Neuer | Manuel Neuer | Germany
31 | Petr Cech | Petr Cech | Czech Republic
31 | Arda Turan | Volkan Babacan | Turkey
32 | Darijo Srna | Danijel SubaSic | Croatia
32 | Sergio Ramos | David de Gea | Spain
33 | Aron Gunnarsson | Hannes Halldorsson | Iceland
33 | Christian Fuchs | Robert Almer | Austria
34 | Balazs Dzsudzsak | Gabor Kiraly | Hungary
34 | Cristiano Ronaldo | Rui Patricio | Portugal
35 | Leonardo Bonucci | Salvatore Sirigu | Italy
35 | Seamus Coleman | Darren Randolph | Republic of Ireland
36 | Zlatan Ibrahimovic | Andreas Isaksson | Sweden
36 | Eden Hazard | Thibaut Courtois | Belgium
37 | Stephan Lichtsteiner | Yann Sommer | Switzerland
37 | Robert Lewandowski | Lukasz Fabianski | Poland
38 | Ashley Williams | Wayne Hennessey | Wales
38 | Steven Davis | Michael McGovern | Northern Ireland
39 | Darijo Srna | Danijel SubaSic | Croatia
39 | Cristiano Ronaldo | Rui Patricio | Portugal
40 | Hugo Lloris | Hugo Lloris | France
40 | Seamus Coleman | Darren Randolph | Republic of Ireland
41 | Manuel Neuer | Manuel Neuer | Germany
41 | Martin Skrtel | MatusKozacik | Slovakia
42 | Balazs Dzsudzsak | Gabor Kiraly | Hungary
42 | Eden Hazard | Thibaut Courtois | Belgium
43 | Gianluigi Buffon | Gianluigi Buffon | Italy
43 | Sergio Ramos | David de Gea | Spain
44 | Wayne Rooney | Joe Hart | England
44 | Aron Gunnarsson | Hannes Halldorsson | Iceland
45 | Robert Lewandowski | Lukasz Fabianski | Poland
45 | Cristiano Ronaldo | Rui Patricio | Portugal
46 | Ashley Williams | Wayne Hennessey | Wales
46 | Eden Hazard | Thibaut Courtois | Belgium
47 | Manuel Neuer | Manuel Neuer | Germany
47 | Gianluigi Buffon | Gianluigi Buffon | Italy
48 | Hugo Lloris | Hugo Lloris | France
48 | Aron Gunnarsson | Hannes Halldorsson | Iceland
49 | Cristiano Ronaldo | Rui Patricio | Portugal
49 | Ashley Williams | Wayne Hennessey | Wales
50 | Hugo Lloris | Hugo Lloris | France
50 | Bastian Schweinsteiger | Manuel Neuer | Germany
51 | Cristiano Ronaldo | Rui Patricio | Portugal
51 | Hugo Lloris | Hugo Lloris | France
(102 rows)
Code Explanation:
The said query in SQL that retrieves information about the captain and goal keeper of soccer teams in various matches, including their country name, and the matches' numbers from the tables match_captain, match_details, soccer_country, and player_mast.
The 'match_captain' table alias as 'a' is naturally joined with the 'match_details' table alias as 'b' based on all common columns. Then, the resulting table is joined with the 'soccer_country' table an alias of 'e' based on the common column "team_id" in 'b' and "country_id" in 'e'.
Finally, the resulting table is joined with the 'player_mast' table twice, aliases of 'c' and 'd' based on the common column "player_captain" in 'a' and "player_id" in 'c', and "player_gk" in 'b' and "player_id" in 'd' respectively.
Go to:
PREV : Find captains for all the matches in the tournament.
NEXT : Which player won Man of the Match at EURO cup 2016?
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.