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. Write a query in SQL to find those three players with other information, who contracted to Lyon participated in the EURO cup 2016 Finals.
Sample table: player_mast
Sample table: soccer_country
Sample table: match_details
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' ));
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)
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query in SQL to find the teams with other information that finished bottom of their respective groups after conceding four times in three games.s
Next: Write a query in SQL to find the final four teams in the tournament.
What is the difficulty level of this exercise?