SQL Exercise: Find 3 Lyon players participated in the EURO Finals

SQL soccer Database: Joins Exercise-29 with Solution

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

Sample table: soccer_country

Sample table: match_details

Sample Solution:

SQL Code:

SELECT a.player_name, a.jersey_no, a.posi_to_play, a.age, b.country_name
FROM player_mast a 
JOIN soccer_country b
ON a.team_id=b.country_id
WHERE a.playing_club='Lyon'
AND a.team_id IN (
SELECT b.country_id 
FROM soccer_country b
WHERE b.country_id IN (
SELECT c.team_id 
FROM match_details c 
WHERE c.play_stage='F'
));

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.

Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Bottom of their groups and conceded 4 goals in 3 games.
Next SQL Exercise: Find the final four teams in the tournament.