SQL Exercise: Find 3 Lyon players participated in the EURO Finals
29. From the following tables, write a SQL query to find those players, who were contracted to the Lyon club and participated in the final of the EURO cup 2016. Return player name, jerseyno, position to play, age, and country 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: 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_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:
-- This query selects player information such as player name, jersey number, position to play, age, and country name for players belonging to the club 'Lyon' and participating in the final stage matches.
SELECT
a.player_name, -- Selecting the player name from the player_mast table aliased as 'a'
a.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'a'
a.posi_to_play, -- Selecting the position to play from the player_mast table aliased as 'a'
a.age, -- Selecting the age from the player_mast table aliased as 'a'
b.country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM
player_mast a -- Specifying the player_mast table with an alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the player_mast table based on the team_id
WHERE
a.playing_club = 'Lyon' -- Filtering players who belong to the club 'Lyon'
AND a.team_id IN ( -- Filtering players whose team ID is in the list of country IDs participating in the final stage matches
SELECT
b.country_id -- Selecting the country IDs from the soccer_country table aliased as 'b'
FROM
soccer_country b -- Specifying the soccer_country table for the subquery
WHERE
b.country_id IN ( -- Filtering country IDs that are in the list of team IDs participating in the final stage matches
SELECT
c.team_id -- Selecting the team IDs from the match_details table aliased as 'c'
FROM
match_details c -- Specifying the match_details table for the subquery
WHERE
c.play_stage = 'F' -- Filtering matches that are in the final stage
)
);
Sample Output:
player_name | jersey_no | posi_to_play | age | country_name
--------------------+-----------+--------------+-----+--------------
Christophe Jallet | 2 | DF | 32 | France
Samuel Umtiti | 22 | DF | 22 | France
Anthony Lopes | 12 | GK | 25 | Portugal
(3 rows)
Code Explanation:
The said query in SQL that retrieves the player's name, jersey number, position to play, age, and country name for all players playing for the club "Lyon" and whose team has played in the "Finals" of a soccer tournament.
The JOIN clause joins the 'player_mast' table with the 'soccer_country' table using the "team_id" and the "country_id" columns from the respective tables.
The WHERE clause filters data for players who play for the club "Lyon" and whose team has played in the "Finals" of a soccer tournament. The nested subqueries have been used to get the desired data.
Alternative Solutions:
Using EXISTS Clause:
-- This query selects player information such as player name, jersey number, position to play, age, and country name for players belonging to the club 'Lyon' and participating in the final stage matches.
SELECT
a.player_name, -- Selecting the player name from the player_mast table aliased as 'a'
a.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'a'
a.posi_to_play, -- Selecting the position to play from the player_mast table aliased as 'a'
a.age, -- Selecting the age from the player_mast table aliased as 'a'
b.country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM
player_mast a -- Specifying the player_mast table with an alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the player_mast table based on the team_id
WHERE
a.playing_club = 'Lyon' -- Filtering players who belong to the club 'Lyon'
AND EXISTS ( -- Checking for the existence of at least one record that satisfies the condition within the subquery
SELECT 1 -- Selecting 1 as a placeholder value (could be any value since we're using EXISTS)
FROM
soccer_country c -- Specifying the soccer_country table with an alias 'c' for the subquery
WHERE
c.country_id IN ( -- Filtering country IDs that are in the list of team IDs participating in the final stage matches
SELECT
d.team_id -- Selecting the team IDs from the match_details table aliased as 'd'
FROM
match_details d -- Specifying the match_details table for the subquery
WHERE
d.play_stage = 'F' -- Filtering matches that are in the final stage
)
AND c.country_id = a.team_id -- Matching the country ID with the team ID of the player
);
Explanation:
This query uses the EXISTS clause with a subquery to check if there exists a team in the final stage that matches the player's team_id. It also ensures the player is from Lyon.
Using INNER JOIN with Subquery:
-- This query selects player information such as player name, jersey number, position to play, age, and country name for players belonging to the club 'Lyon' and participating in the final stage matches.
SELECT
a.player_name, -- Selecting the player name from the player_mast table aliased as 'a'
a.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'a'
a.posi_to_play, -- Selecting the position to play from the player_mast table aliased as 'a'
a.age, -- Selecting the age from the player_mast table aliased as 'a'
b.country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM
player_mast a -- Specifying the player_mast table with an alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the player_mast table based on the team_id
JOIN
(
-- Subquery to select distinct team IDs participating in the final stage matches
SELECT DISTINCT
c.team_id -- Selecting distinct team IDs from the match_details table aliased as 'c'
FROM
match_details c -- Specifying the match_details table for the subquery
WHERE
c.play_stage = 'F' -- Filtering matches that are in the final stage
) d ON a.team_id = d.team_id -- Joining the main query with the subquery based on the team_id
-- Filtering players who belong to the club 'Lyon'
WHERE
a.playing_club = 'Lyon';
Explanation:
This query first uses a subquery to find distinct team_ids playing in the final stage. It then joins this subquery with player_mast and soccer_country based on the team_id. Finally, it filters for players from Lyon.
Using INNER JOIN and Subquery with IN:
-- This query selects player information such as player name, jersey number, position to play, age, and country name for players belonging to the club 'Lyon' and participating in the final stage matches.
SELECT
a.player_name, -- Selecting the player name from the player_mast table aliased as 'a'
a.jersey_no, -- Selecting the jersey number from the player_mast table aliased as 'a'
a.posi_to_play, -- Selecting the position to play from the player_mast table aliased as 'a'
a.age, -- Selecting the age from the player_mast table aliased as 'a'
b.country_name -- Selecting the country name from the soccer_country table aliased as 'b'
FROM
player_mast a -- Specifying the player_mast table with an alias 'a'
JOIN
soccer_country b ON a.team_id = b.country_id -- Joining the soccer_country table with the player_mast table based on the team_id
JOIN
match_details c ON a.team_id = c.team_id -- Joining the match_details table with the player_mast table based on the team_id
WHERE
a.playing_club = 'Lyon' -- Filtering players who belong to the club 'Lyon'
AND c.play_stage = 'F' -- Filtering matches that are in the final stage
AND a.team_id IN ( -- Filtering players whose team ID is in the list of team IDs participating in the final stage matches
SELECT
team_id -- Selecting the team ID from the subquery
FROM
match_details -- Specifying the match_details table for the subquery
-- Filtering matches that are in the final stage
WHERE
play_stage = 'F'
);
Explanation:
This query first uses a combination of JOIN and WHERE clauses to ensure that the player is from Lyon and their team is in the final stage. It also checks the play_stage directly in the join condition.
Go to:
PREV : Bottom of their groups and conceded 4 goals in 3 games.
NEXT : Find the final four teams in the tournament.
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.