w3resource
Soccer Database Exercises

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. Write a query in SQL to find the player along with his team and jersey number who scored the first penalty of the tournament.

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)

Query Visualization:

Duration:

Query visualization of Find the player along with his team and jersey number who scored the first penalty of the tournament - Duration

Rows:

Query visualization of Find the player along with his team and jersey number who scored the first penalty of the tournament - Rows

Cost:

Query visualization of Find the player along with his team and jersey number who scored the first penalty of the tournament - Cost

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 player and his jersey number Who scored the first penalty of the tournament.
Next: Write a query in SQL to find the player who was the goalkeeper for Italy in penalty shootout against Germany in Football EURO cup 2016.

What is the difficulty level of this exercise?



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