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

SQL soccer Database: Subqueries Exercise-14 with Solution

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

Sample table: player_mast

Sample table: goal_details

Sample Solution:

SQL Code:

SELECT player_name,jersey_no 
FROM player_mast 
WHERE player_id=(
SELECT player_id 
FROM goal_details 
WHERE goal_type='P' AND match_no=(
SELECT MIN(match_no) 
FROM goal_details 
WHERE goal_type='P' AND play_stage='G'));

Sample Output:

  player_name   | jersey_no
----------------+-----------
 Bogdan Stancu  |        19
(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 table, write a SQL query to find the club, which supplied the most number of players to the 2016-EURO cup. Return club name, number of players.
Next: 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.