SQL Exercise: Find captains for all the matches in the tournament
32. From the following tables, write a SQL query to find the captains of all the matches in the tournament. Return match number, 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
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: 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, jersey number, and position to play for all match captains, ordering the results by match number.
SELECT
match_no, -- Selecting the match number from the match_captain table aliased as 'a'
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
-- Ordering the results by match number
ORDER BY
match_no;
Sample Output:
match_no | country_name | player_name | jersey_no | posi_to_play
----------+---------------------+-------------------------+-----------+--------------
1 | France | Hugo Lloris | 1 | GK
1 | Romania | Vlad Chiriches | 6 | DF
2 | Albania | Lorik Cana | 5 | MF
2 | Switzerland | Stephan Lichtsteiner | 2 | DF
3 | Slovakia | Martin Skrtel | 3 | DF
3 | Wales | Ashley Williams | 6 | DF
4 | England | Wayne Rooney | 10 | FD
4 | Russia | Vasili Berezutski | 14 | DF
5 | Turkey | Arda Turan | 10 | MF
5 | Croatia | Darijo Srna | 11 | DF
6 | Poland | Robert Lewandowski | 9 | FD
6 | Northern Ireland | Steven Davis | 8 | MF
7 | Germany | Manuel Neuer | 1 | GK
7 | Ukraine | Vyacheslav Shevchuk | 13 | DF
8 | Spain | Sergio Ramos | 15 | DF
8 | Czech Republic | TomasRosicky | 10 | MF
9 | Republic of Ireland | John OShea | 4 | DF
9 | Sweden | Zlatan Ibrahimovic | 10 | FD
10 | Italy | Gianluigi Buffon | 1 | GK
10 | Belgium | Eden Hazard | 10 | MF
11 | Austria | Christian Fuchs | 5 | DF
11 | Hungary | Balazs Dzsudzsak | 7 | FD
12 | Portugal | Cristiano Ronaldo | 7 | FD
12 | Iceland | Aron Gunnarsson | 17 | MF
13 | Russia | Vasili Berezutski | 14 | DF
13 | Slovakia | Martin Skrtel | 3 | DF
14 | Romania | Vlad Chiriches | 6 | DF
14 | Switzerland | Stephan Lichtsteiner | 2 | DF
15 | France | Hugo Lloris | 1 | GK
15 | Albania | Ansi Agolli | 7 | DF
16 | Wales | Ashley Williams | 6 | DF
16 | England | Wayne Rooney | 10 | FD
17 | Ukraine | Vyacheslav Shevchuk | 13 | DF
17 | Northern Ireland | Steven Davis | 8 | MF
18 | Poland | Robert Lewandowski | 9 | FD
18 | Germany | Manuel Neuer | 1 | GK
19 | Sweden | Zlatan Ibrahimovic | 10 | FD
19 | Italy | Gianluigi Buffon | 1 | GK
20 | Croatia | Darijo Srna | 11 | DF
20 | Czech Republic | TomasRosicky | 10 | MF
21 | Turkey | Arda Turan | 10 | MF
21 | Spain | Sergio Ramos | 15 | DF
22 | Republic of Ireland | John OShea | 4 | DF
22 | Belgium | Eden Hazard | 10 | MF
23 | Iceland | Aron Gunnarsson | 17 | MF
23 | Hungary | Balazs Dzsudzsak | 7 | FD
24 | Portugal | Cristiano Ronaldo | 7 | FD
24 | Austria | Christian Fuchs | 5 | DF
25 | Albania | Ansi Agolli | 7 | DF
25 | Romania | Vlad Chiriches | 6 | DF
26 | Switzerland | Stephan Lichtsteiner | 2 | DF
26 | France | Hugo Lloris | 1 | GK
27 | Russia | Roman Shirokov | 15 | MF
27 | Wales | Ashley Williams | 6 | DF
28 | Slovakia | Martin Skrtel | 3 | DF
28 | England | Gary Cahill | 5 | DF
29 | Ukraine | Ruslan Rotan | 14 | MF
29 | Poland | Robert Lewandowski | 9 | FD
30 | Northern Ireland | Steven Davis | 8 | MF
30 | Germany | Manuel Neuer | 1 | GK
31 | Czech Republic | Petr Cech | 1 | GK
31 | Turkey | Arda Turan | 10 | MF
32 | Croatia | Darijo Srna | 11 | DF
32 | Spain | Sergio Ramos | 15 | DF
33 | Iceland | Aron Gunnarsson | 17 | MF
33 | Austria | Christian Fuchs | 5 | DF
34 | Hungary | Balazs Dzsudzsak | 7 | FD
34 | Portugal | Cristiano Ronaldo | 7 | FD
35 | Republic of Ireland | Seamus Coleman | 2 | DF
35 | Italy | Leonardo Bonucci | 19 | DF
36 | Sweden | Zlatan Ibrahimovic | 10 | FD
36 | Belgium | Eden Hazard | 10 | MF
37 | Poland | Robert Lewandowski | 9 | FD
37 | Switzerland | Stephan Lichtsteiner | 2 | DF
38 | Wales | Ashley Williams | 6 | DF
38 | Northern Ireland | Steven Davis | 8 | MF
39 | Croatia | Darijo Srna | 11 | DF
39 | Portugal | Cristiano Ronaldo | 7 | FD
40 | Republic of Ireland | Seamus Coleman | 2 | DF
40 | France | Hugo Lloris | 1 | GK
41 | Germany | Manuel Neuer | 1 | GK
41 | Slovakia | Martin Skrtel | 3 | DF
42 | Belgium | Eden Hazard | 10 | MF
42 | Hungary | Balazs Dzsudzsak | 7 | FD
43 | Spain | Sergio Ramos | 15 | DF
43 | Italy | Gianluigi Buffon | 1 | GK
44 | Iceland | Aron Gunnarsson | 17 | MF
44 | England | Wayne Rooney | 10 | FD
45 | Portugal | Cristiano Ronaldo | 7 | FD
45 | Poland | Robert Lewandowski | 9 | FD
46 | Wales | Ashley Williams | 6 | DF
46 | Belgium | Eden Hazard | 10 | MF
47 | Italy | Gianluigi Buffon | 1 | GK
47 | Germany | Manuel Neuer | 1 | GK
48 | Iceland | Aron Gunnarsson | 17 | MF
48 | France | Hugo Lloris | 1 | GK
49 | Portugal | Cristiano Ronaldo | 7 | FD
49 | Wales | Ashley Williams | 6 | DF
50 | France | Hugo Lloris | 1 | GK
50 | Germany | Bastian Schweinsteiger | 7 | MF
51 | France | Hugo Lloris | 1 | GK
51 | Portugal | Cristiano Ronaldo | 7 | FD
(102 rows)
Code Explanation:
The given query in SQL that retrieves data from the tables match_captain, soccer_country, and player_mast - using JOIN operations, and returns the columns match_no, country_name, player_name, jersey_no, and posi_to_play for each row of data.
The 'match_captain' table an alias of 'a' is joined with the 'soccer_country' table alias as 'b' based on the common column "team_id" in 'a' and "country_id" in 'b'. Then, the resulting table is joined with the 'player_mast' table alias as 'c' based on the common column "player_captain" in 'a' and "player_id" in 'c'.
The result set then sorted by the "match_no" column in ascending order.
Alternative Solution:
Using JOINs with Subquery and ORDER BY:
-- This query selects the match number, country name, player name, jersey number, and position to play for all match captains, ordering the results by match number.
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 subquery aliased as 'b'
c.player_name, -- Selecting the player name from the player_mast subquery aliased as 'c'
c.jersey_no, -- Selecting the jersey number from the player_mast subquery aliased as 'c'
c.posi_to_play -- Selecting the position to play from the player_mast subquery aliased as 'c'
FROM
match_captain a -- Specifying the match_captain table with an alias 'a'
JOIN
(
-- Subquery to select country ID and country name from the soccer_country table
SELECT
country_id, -- Selecting the country ID
country_name -- Selecting the country name
FROM
soccer_country -- Specifying the soccer_country table for the subquery
) b ON a.team_id = b.country_id -- Joining the main query with the soccer_country subquery based on the country_id
JOIN
(
-- Subquery to select player ID, player name, jersey number, and position to play from the player_mast table
SELECT
player_id, -- Selecting the player ID
player_name, -- Selecting the player name
jersey_no, -- Selecting the jersey number
posi_to_play -- Selecting the position to play
FROM
player_mast -- Specifying the player_mast table for the subquery
) c ON a.player_captain = c.player_id -- Joining the main query with the player_mast subquery based on the player_captain
-- Ordering the results by match number
ORDER BY
a.match_no;
Explanation:
This query uses subqueries to first select necessary columns from soccer_country and player_mast tables. It then performs JOINs and applies an ORDER BY clause to sort the results by match_no.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Captains of the top four teams in the semifinals.
NEXT : All the captains and goalkeepers for all the teams.
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.