SQL exercises on soccer Database: Find those three players with other information, who contracted to Lyon participated in the EURO cup 2016 Finals

SQL soccer Database: Joins Exercise-29 with Solution

29. From the following tables, write a SQL query to find those players, who contracted to ‘Lyon’ club and participated in the EURO cup 2016 Finals. Return player name, jerseyno, position to play, age, 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)

Practice Online

Sample Database: soccer

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

Previous: From the following tables, write a SQL query to find those teams that finished bottom of their respective groups after conceding four times in three games. Return country name, team group and match played.
Next: From the following tables, write a SQL query to find the final four teams in the tournament. Return country name.