SQL Exercise: The captain of Portugal EURO cup 2016 winning team
21. From the following table, write a SQL query to find out who was the captain of Portugal's winning EURO cup 2016 team. Return the captain name.
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 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 Solution:
SQL Code:
-- Selecting player_name
SELECT player_name
-- Selecting from player_mast table
FROM player_mast
-- Filtering by player_id present in subquery result
WHERE player_id IN (
-- Subquery to select player_captain from match_captain table
SELECT player_captain
FROM match_captain
-- Subquery to select team_id from match_details table based on play_stage and win_lose conditions
WHERE team_id=(
SELECT team_id
FROM match_details
WHERE play_stage='F' AND win_lose='W'
)
);
Sample Output:
player_name
--------------------
Cristiano Ronaldo
(1 row)
Code Explanation:
This SQL query retrieves the names of players who were captains in the winning team of the final match. It uses subqueries to find the team ID of the winning team and the ID of the captain of that team in the 'match_details' and 'match_captain' tables, respectively.
The inner most subquery returns the ID of the winning team in the final match, the outer most subquery returns the ID of the captain of that team, and the outer most query filters the 'player_mast' table to only include players who have the ID provided by the inner most subquery.
Alternative Solution:
Using JOIN:
-- Selecting distinct player_names
SELECT DISTINCT pm.player_name
-- Joining player_mast, match_captain, and match_details tables based on player_id and player_captain
FROM player_mast pm
JOIN match_captain mc ON pm.player_id = mc.player_captain
JOIN match_details md ON mc.team_id = md.team_id
-- Filtering by play_stage and win_lose in match_details table
WHERE md.play_stage = 'F' AND md.win_lose = 'W';
Explanation:
This query uses JOIN operations to connect the player_mast table with match_captain and match_details based on their respective IDs. It then filters the results to only include players who were captains of the winning team in the final stage.
Using EXISTS:
-- Selecting player_name
SELECT player_name
-- Selecting from player_mast table
FROM player_mast pm
-- Subquery to check for existence of records in match_captain and match_details tables
WHERE EXISTS (
-- Subquery to join match_captain and match_details tables and filter based on conditions
SELECT 1
FROM match_captain mc
JOIN match_details md ON mc.team_id = md.team_id
-- Matching player_captain with player_id and filtering by play_stage and win_lose
WHERE mc.player_captain = pm.player_id
AND md.play_stage = 'F'
AND md.win_lose = 'W'
);
Explanation:
This query uses the EXISTS keyword with a subquery to check if there exists a record in match_captain and match_details where the player is the captain of the winning team in the final stage.
Using JOIN with Subquery:
-- Selecting player names from the player_mast table
SELECT pm.player_name
FROM player_mast pm
-- Joining with a subquery to find winning captains
JOIN (
-- Selecting player captains from the match_captain table
SELECT mc.player_captain
FROM match_captain mc
-- Joining with match_details to filter by play stage and win/lose status
JOIN match_details md ON mc.team_id = md.team_id
WHERE md.play_stage = 'F' AND md.win_lose = 'W'
) AS winning_captains ON pm.player_id = winning_captains.player_captain;
Explanation:
This query first creates a subquery that retrieves the player IDs of winning captains in the final stage. It then joins this subquery with the player_mast table to get the names of those players.
Go to:
PREV : Who scored the last goal in the 2nd semi-final.
NEXT : Number of players played for france in the final.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
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.