SQL exercises on soccer Database: Find the player along with his team and jersey number who scored the first penalty of the tournament

SQL soccer Database: Subqueries Exercise-15 with Solution

15. From the following tables, write a SQL query to find the player who scored the first penalty in the tournament. Return player name, Jersey number and country name.

Sample table: player_mast

Sample table: goal_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT a.player_name,a.jersey_no,d.country_name
FROM player_mast a, goal_details b, goal_details c, soccer_country d
WHERE a.player_id=b.player_id AND a.team_id=d.country_id AND 
a.player_id=(
SELECT b.player_id 
FROM goal_details b
WHERE b.goal_type='P' AND b.match_no=(
SELECT MIN(c.match_no) 
FROM goal_details c
WHERE c.goal_type='P' AND c.play_stage='G'))
GROUP BY player_name,jersey_no,country_name;

Sample Output:

  player_name   | jersey_no | country_name
----------------+-----------+--------------
 Bogdan Stancu  |        19 | Romania
(1 row)

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 player who scored the first penalty of the tournament. Return player name and Jersey number.
Next: From the following tables, write a SQL query to find the goalkeeper for Italy in penalty shootout against Germany in Football EURO cup 2016. Return goalkeeper name.