SQL exercises on soccer Database: Find the players along with their jersey no., and playing club, who were the goalkeepers for the England squad for 2016 EURO cup

SQL soccer Database: Subqueries Exercise-18 with Solution

18. 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.

Sample table: player_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

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

Sample Output:

   player_name   | jersey_no | playing_club
-----------------+-----------+--------------
 Joe Hart        |         1 | Man. City
 Fraser Forster  |        13 | Southampton
 Tom Heaton      |        23 | Burnley
(3 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 table, write a SQL query to find the number of goals Germany scored at the tournament.
Next: 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.