w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the player of each team who wear jersey number 10

SQL soccer Database: Joins Exercise-56 with Solution

Write a query in SQL to find the player of each team who wear jersey number 10.

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)

Practice Online


Sample Database: soccer

soccer database relationship structure

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

Previous: Write a query in SQL to find the referees who booked most number of players.
Next: Write a query in SQL to find the defender who scored goal for his team.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming