SQL Exercise: First penalty along with his team and jersey number
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
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
......
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 table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
.....
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- This SQL query retrieves the player name, jersey number, and country name
-- for players who scored a penalty goal in the earliest match of the group stage.
SELECT a.player_name, a.jersey_no, d.country_name
-- Selects the 'player_name', 'jersey_no', and 'country_name' columns.
FROM player_mast a, goal_details b, goal_details c, soccer_country d
-- Specifies the tables involved in the query, using aliases 'a' for 'player_mast', 'b' for the first instance of 'goal_details', 'c' for the second instance of 'goal_details', and 'd' for 'soccer_country'.
WHERE a.player_id = b.player_id AND a.team_id = d.country_id
-- Connects rows where 'player_id' in 'player_mast' matches 'player_id' in the first instance of 'goal_details', and 'team_id' in 'player_mast' matches 'country_id' in 'soccer_country'.
AND a.player_id = (
-- The WHERE clause filters rows where 'player_id' matches the result of the subquery.
SELECT b.player_id
-- This subquery selects the 'player_id' column.
FROM goal_details b
-- 'goal_details' is the name of the table involved in the subquery, using alias 'b'.
WHERE b.goal_type = 'P'
-- Further filters rows in the subquery where 'goal_type' is 'P' (penalty goal).
AND b.match_no = (
-- Further filters rows in the subquery where 'match_no' matches the result of another subquery.
SELECT MIN(c.match_no)
-- This sub-subquery selects the minimum 'match_no'.
FROM goal_details c
-- 'goal_details' is the name of the table involved in the sub-subquery, using alias 'c'.
WHERE c.goal_type = 'P'
-- Further filters rows in the sub-subquery where 'goal_type' is 'P' (penalty goal).
AND c.play_stage = 'G'
-- Further filters rows in the sub-subquery where 'play_stage' is 'G' (group stage).
)
)
GROUP BY player_name, jersey_no, country_name;
-- Groups the results by 'player_name', 'jersey_no', and 'country_name'.
Sample Output:
player_name | jersey_no | country_name ----------------+-----------+-------------- Bogdan Stancu | 19 | Romania (1 row)
Code Explanation:
This SQL query selects the player name, jersey number, and country name for the player who scored the first penalty goal in the earliest game in the play stage "G". It does this by joining four tables together and using subqueries to filter the results.
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, jersey number, and country name for that player.
This groups the results by player name, jersey number, and country name.
Alternative Solutions:
Using JOIN and Subqueries:
SELECT pm.player_name, pm.jersey_no, sc.country_name
FROM player_mast pm
JOIN goal_details gd1 ON pm.player_id = gd1.player_id
JOIN soccer_country sc ON pm.team_id = sc.country_id
WHERE gd1.goal_type = 'P'
AND gd1.match_no = (
SELECT MIN(gd2.match_no)
FROM goal_details gd2
WHERE gd2.goal_type = 'P' AND gd2.play_stage = 'G'
)
GROUP BY pm.player_name, pm.jersey_no, sc.country_name;
Explanation:
This query uses JOINs to connect the player_mast, goal_details, and soccer_country tables. It applies conditions in the WHERE clause to filter for goals of type 'P' and the minimum match_no in the specified play stage. The GROUP BY clause ensures unique combinations of player name, jersey number, and country name.
Using Nested Subqueries with JOIN:
SELECT pm.player_name, pm.jersey_no, sc.country_name
FROM player_mast pm
JOIN soccer_country sc ON pm.team_id = sc.country_id
WHERE pm.player_id = (
SELECT gd1.player_id
FROM goal_details gd1
WHERE gd1.goal_type = 'P'
AND gd1.match_no = (
SELECT MIN(gd2.match_no)
FROM goal_details gd2
WHERE gd2.goal_type = 'P' AND gd2.play_stage = 'G'
)
)
GROUP BY pm.player_name, pm.jersey_no, sc.country_name;
Explanation:
This query uses nested subqueries to find the player_id that matches the conditions. It then joins the player_mast and soccer_country tables and applies the necessary conditions.
Using JOIN and EXISTS:
SELECT pm.player_name, pm.jersey_no, sc.country_name
FROM player_mast pm
JOIN soccer_country sc ON pm.team_id = sc.country_id
WHERE EXISTS (
SELECT 1
FROM goal_details gd1
WHERE gd1.goal_type = 'P'
AND gd1.match_no = (
SELECT MIN(gd2.match_no)
FROM goal_details gd2
WHERE gd2.goal_type = 'P' AND gd2.play_stage = 'G'
)
AND gd1.player_id = pm.player_id
)
GROUP BY pm.player_name, pm.jersey_no, sc.country_name;
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.
Go to:
PREV : Player and jersey number who scored the first penalty.
NEXT : Italy Goalkeeper in 2016 penalty shootout with Germany.
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.