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
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)
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.
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the best way to paginate results in SQL Server?
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= 1 AND RowNum < 20 ORDER BY RowNum
Database: SQL Server
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook