SQL exercises on soccer Database: Find the players with other information under contract to Liverpool were in the Squad of England in 2016 EURO cup

SQL soccer Database: Subqueries Exercise-19 with Solution

19. From the following tables, write a SQL query to find the players under contract to Liverpool were in the Squad of England in 2016-EURO cup. Return player name, jersey number, position to play, age.

Sample table: player_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT player_name, jersey_no, posi_to_play, age
FROM player_mast 
WHERE playing_club='Liverpool'
AND team_id=(
SELECT country_id
FROM soccer_country
WHERE country_name='England'
);

Sample Output:

    player_name    | jersey_no | posi_to_play | age
-------------------+-----------+--------------+-----
 James Milner      |         4 | MF           |  30
 Adam Lallana      |         8 | MF           |  28
 Nathaniel Clyne   |        12 | DF           |  25
 Jordan Henderson  |        14 | MF           |  26
 Daniel Sturridge  |        15 | FD           |  26
(5 rows)

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

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 the players who were the goalkeepers of England squad in 2016-EURO cup. Return player name, jersey number, club name.
Next: From the following tables, write a SQL query to find the players who scored the last goal in the 2nd semi-final, i.e., 50th match in EURO cup 2016. Return player name, goal time, goal half, country name.