SQL Exercise: Find the player of each team who wear jersey number 10
SQL soccer Database: Joins Exercise-56 with Solution
From the following tables, write a SQL query to find those players on each team who wore jersey number 10. Return country name, player name, position to play, age and playing club.
Sample table: player_mast
Sample table: soccer_country
Sample Solution:
SQL Code:
SELECT country_name,
player_name,
posi_to_play,
age,
playing_club
FROM player_mast a
JOIN soccer_country b ON a.team_id=b.country_id
WHERE jersey_no=10
ORDER BY country_name;
Sample Output:
country_name | player_name | posi_to_play | age | playing_club ---------------------+----------------------+--------------+-----+----------------- Albania | Armando Sadiku | FD | 25 | Vaduz Austria | Zlatko Junuzovic | MF | 28 | Bremen Belgium | Eden Hazard | MF | 25 | Chelsea Croatia | Luka Modric | MF | 30 | Real Madrid Czech Republic | TomasRosicky | MF | 35 | Arsenal England | Wayne Rooney | FD | 30 | Man. United France | Andre-Pierre Gignac | FD | 30 | Tigres Germany | Lukas Podolski | FD | 31 | Galatasaray Hungary | Zoltan Gera | FD | 37 | Ferencvaros Iceland | Gylfi Sigurdsson | MF | 26 | Swansea Italy | Thiago Motta | MF | 33 | Paris Northern Ireland | Kyle Lafferty | FD | 28 | Birmingham Poland | Grzegorz Krychowiak | MF | 26 | Sevilla Portugal | Joao Mario | MF | 23 | Sporting CP Republic of Ireland | Robbie Keane | FD | 35 | LA Galaxy Romania | Nicolae Stanciu | MF | 23 | Steaua Russia | Fedor Smolov | FD | 26 | Krasnodar Slovakia | Miroslav Stoch | MF | 26 | Bursaspor Spain | Cesc Fabregas | MF | 29 | Chelsea Sweden | Zlatan Ibrahimovic | FD | 34 | Paris Switzerland | Granit Xhaka | MF | 23 | Monchengladbach Turkey | Arda Turan | MF | 29 | Barcelona Ukraine | Yevhen Konoplyanka | MF | 26 | Sevilla Wales | Aaron Ramsey | MF | 25 | Arsenal (24 rows)
Code Explanation:
The said query in SQL that retrieves information about soccer players who wear jersey number 10, including their country of origin, name, position to play, age, and current club. The results are sorted by country name from the player_mast table.
The WHERE clause that specified must be met for a player to be included in the results that they must have a jersey number of 10.
The results is sorted by country name, in ascending order.
Relational Algebra Expression:

Relational Algebra Tree:

Practice Online
Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Find the referees who booked most number of players.
Next SQL Exercise: Find the defender who scored goal for his team.
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
Ref: https://bit.ly/3MGrNlk
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- 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