SQL Exercise: Player and jersey number who scored the first penalty
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
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
......
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample table: goal_details
goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
1 | 1 | 160159 | 1207 | 57 | N | G | NT | 2
2 | 1 | 160368 | 1216 | 65 | P | G | NT | 2
3 | 1 | 160154 | 1207 | 89 | N | G | NT | 2
4 | 2 | 160470 | 1221 | 5 | N | G | NT | 1
5 | 3 | 160547 | 1224 | 10 | N | G | NT | 1
6 | 3 | 160403 | 1218 | 61 | N | G | NT | 2
7 | 3 | 160550 | 1224 | 81 | N | G | NT | 2
8 | 4 | 160128 | 1206 | 73 | N | G | NT | 2
9 | 4 | 160373 | 1217 | 93 | N | G | ST | 2
.......
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
Sample Solution:
SQL Code:
-- This SQL query retrieves the player name and jersey number from the 'player_mast' table
-- for players who scored a penalty goal in the earliest match of the group stage.
SELECT player_name, jersey_no
-- Selects the 'player_name' and 'jersey_no' columns.
FROM player_mast
-- 'player_mast' is the name of the table being queried.
WHERE player_id = (
-- The WHERE clause filters rows where 'player_id' matches the result of the subquery.
SELECT player_id
-- This subquery selects the 'player_id' column.
FROM goal_details
-- 'goal_details' is the name of the table involved in the subquery.
WHERE goal_type = 'P'
-- Further filters rows in the subquery where 'goal_type' is 'P' (penalty goal).
AND match_no = (
-- Further filters rows in the subquery where 'match_no' matches the result of another subquery.
SELECT MIN(match_no)
-- This sub-subquery selects the minimum 'match_no'.
FROM goal_details
-- 'goal_details' is the name of the table involved in the sub-subquery.
WHERE goal_type = 'P'
-- Further filters rows in the sub-subquery where 'goal_type' is 'P' (penalty goal).
AND play_stage = 'G'
-- Further filters rows in the sub-subquery where 'play_stage' is 'G' (group stage).
)
);
Sample Output:
player_name | jersey_no ----------------+----------- Bogdan Stancu | 19 (1 row)
Code Explanation:
The said query in SQL that selects the player name and jersey number of the player who scored the first penalty goal in the earliest game in the play stage "G".
It happens by using a subquery to first find the match_no of the earliest game in the play stage "G" that had a penalty goal, and then using another subquery to find the player_id of the player who scored that goal. The outer query then selects the player name and jersey number for that player.
Alternative Solutions:
Using JOIN:
SELECT pm.player_name, pm.jersey_no
FROM player_mast pm
JOIN goal_details gd ON pm.player_id = gd.player_id
WHERE gd.goal_type = 'P'
AND gd.match_no = (
SELECT MIN(match_no)
FROM goal_details
WHERE goal_type = 'P' AND play_stage = 'G'
);
Explanation:
This query uses a JOIN between player_mast and goal_details on player_id. It then applies conditions in the WHERE clause to filter for goals of type 'P' and the minimum match_no in the specified play stage.
Using EXISTS:
SELECT player_name, jersey_no
FROM player_mast pm
WHERE EXISTS (
SELECT 1
FROM goal_details gd
WHERE gd.goal_type = 'P'
AND gd.match_no = (
SELECT MIN(match_no)
FROM goal_details
WHERE goal_type = 'P' AND play_stage = 'G'
)
AND pm.player_id = gd.player_id
);
Explanation:
This query uses EXISTS with correlated subqueries to check if there exists a goal of type 'P' in the minimum match_no of the specified play stage for each player.
Using LIMIT with Subqueries:
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'
)
LIMIT 1
);
Explanation:
This query uses subqueries with LIMIT 1 to ensure that only one player_id is selected for the conditions specified. It then selects the player_name and jersey_no based on that player_id.
Go to:
PREV : Club that supplied the most players to the 2016 EURO.
NEXT : First penalty along with his team and jersey number.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.