SQL: Player scores last goal for Portugal against Hungary
8. From the following tables, write a SQL query to find the player who scored the last goal for Portugal against Hungary. Return player 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
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 table: match_details
match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
1 | G | 1207 | W | N | 2 | | 80016 | 160140
1 | G | 1216 | L | N | 1 | | 80020 | 160348
2 | G | 1201 | L | N | 0 | | 80003 | 160001
2 | G | 1221 | W | N | 1 | | 80023 | 160463
3 | G | 1224 | W | N | 2 | | 80031 | 160532
3 | G | 1218 | L | N | 1 | | 80025 | 160392
4 | G | 1206 | D | N | 1 | | 80008 | 160117
4 | G | 1217 | D | N | 1 | | 80019 | 160369
5 | G | 1222 | L | N | 0 | | 80011 | 160486
........
51 | F | 1207 | L | N | 0 | | 80007 | 160140
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 names who scored the latest goal for Portugal in a match involving Hungary.
SELECT player_name
-- Selects the 'player_name' column.
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
-- The subquery selects the 'player_id' column.
FROM goal_details
-- 'goal_details' is the name of the table involved in the subquery.
WHERE match_no = (
-- Further filters rows in the subquery where 'match_no' matches the result of another subquery.
SELECT match_no
-- This subquery selects the 'match_no' column.
FROM match_details
-- 'match_details' is the name of the table involved in the subquery.
WHERE team_id = (
-- Further filters rows in this subquery where 'team_id' matches the country ID for Hungary or Portugal in another subquery.
SELECT country_id
-- This sub-subquery selects the 'country_id' column.
FROM soccer_country
-- 'soccer_country' is the name of the table involved in the sub-subquery.
WHERE country_name = 'Hungary'
or team_id = (
SELECT country_id
-- Continuation of the sub-subquery, selecting the 'country_id' column.
FROM soccer_country
-- 'soccer_country' is the name of the table involved in the sub-subquery.
WHERE country_name = 'Portugal'
)
GROUP BY match_no HAVING COUNT(DISTINCT team_id) = 2
-- Groups the results in this sub-subquery by 'match_no' and filters groups where the count of distinct 'team_id' values is equal to 2.
)
AND team_id = (
-- Further filters rows in this subquery where 'team_id' matches the team ID for Portugal in another subquery.
SELECT team_id
-- This sub-subquery selects the 'team_id' column.
FROM soccer_country a, soccer_team b
-- Specifies the tables involved in the sub-subquery, using aliases 'a' for 'soccer_country' and 'b' for 'soccer_team'.
WHERE a.country_id = b.team_id AND country_name = 'Portugal'
)
AND goal_time = (
-- Further filters rows in this subquery where 'goal_time' matches the maximum goal time in another subquery.
SELECT max(goal_time)
-- This sub-sub-subquery calculates the maximum 'goal_time'.
FROM goal_details
-- 'goal_details' is the name of the table involved in the sub-sub-subquery.
WHERE match_no = (
-- Continuation of the sub-sub-subquery, selecting the 'match_no' column.
SELECT match_no
-- This sub-sub-subquery selects the 'match_no' column.
FROM match_details
-- 'match_details' is the name of the table involved in the sub-sub-subquery.
WHERE team_id = (
-- Further filters rows in this sub-sub-subquery where 'team_id' matches the country ID for Hungary or Portugal in another subquery.
SELECT country_id
-- This sub-sub-sub-subquery selects the 'country_id' column.
FROM soccer_country
-- 'soccer_country' is the name of the table involved in the sub-sub-sub-subquery.
WHERE country_name = 'Hungary'
or team_id = (
SELECT country_id
-- Continuation of the sub-sub-sub-subquery, selecting the 'country_id' column.
FROM soccer_country
-- 'soccer_country' is the name of the table involved in the sub-sub-sub-subquery.
WHERE country_name = 'Portugal'
)
GROUP BY match_no HAVING COUNT(DISTINCT team_id) = 2
-- Groups
Sample Output:
player_name
--------------------
Cristiano Ronaldo
(1 row)
Code Explanation:
The said query in SQL that retrieves the name of the player who scored the last goal for the Portugal team in the match where both Hungary and Portugal participated.
The outermost query selects the name of the player from the player_mast table.
The first subquery selects the player_id from the goal_details table where the match_no is retrieved from the second subquery.
The second subquery retrieves the match_no where both Hungary and Portugal participated.
The third subquery selects the team_id of the Portugal team.
The fourth subquery selects the maximum goal_time of the last goal scored by the Portugal team in the match.
Alternative Solutions:
Using JOINs and Subqueries:
SELECT pm.player_name
FROM player_mast pm
JOIN goal_details gd ON pm.player_id = gd.player_id
JOIN match_details md ON gd.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
JOIN soccer_team st ON sc.country_id = st.team_id
WHERE md.match_no = (
SELECT md.match_no
FROM match_details md
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE sc.country_name IN ('Hungary', 'Portugal')
GROUP BY md.match_no
HAVING COUNT(DISTINCT sc.country_id) = 2
)
AND sc.country_name = 'Portugal'
AND gd.goal_time = (
SELECT MAX(gd.goal_time)
FROM goal_details gd
JOIN match_details md ON gd.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
JOIN soccer_team st ON sc.country_id = st.team_id
WHERE md.match_no = (
SELECT md.match_no
FROM match_details md
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE sc.country_name IN ('Hungary', 'Portugal')
GROUP BY md.match_no
HAVING COUNT(DISTINCT sc.country_id) = 2
)
AND sc.country_name = 'Portugal'
AND gd.team_id = st.team_id
);
Using EXISTS:
SELECT pm.player_name
FROM player_mast pm
JOIN goal_details gd ON pm.player_id = gd.player_id
JOIN match_details md ON gd.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
JOIN soccer_team st ON sc.country_id = st.team_id
WHERE md.match_no = (
SELECT md.match_no
FROM match_details md
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE sc.country_name IN ('Hungary', 'Portugal')
GROUP BY md.match_no
HAVING COUNT(DISTINCT sc.country_id) = 2
)
AND sc.country_name = 'Portugal'
AND gd.goal_time = (
SELECT MAX(gd.goal_time)
FROM goal_details gd
JOIN match_details md ON gd.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
JOIN soccer_team st ON sc.country_id = st.team_id
WHERE md.match_no = (
SELECT md.match_no
FROM match_details md
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE sc.country_name IN ('Hungary', 'Portugal')
GROUP BY md.match_no
HAVING COUNT(DISTINCT sc.country_id) = 2
)
AND sc.country_name = 'Portugal'
AND gd.team_id = st.team_id
);
Using JOINs and Subqueries with EXISTS():
SELECT pm.player_name
FROM player_mast pm
JOIN goal_details gd ON pm.player_id = gd.player_id
JOIN match_details md ON gd.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
JOIN soccer_team st ON sc.country_id = st.team_id
WHERE md.match_no = (
SELECT md.match_no
FROM match_details md
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE sc.country_name IN ('Hungary', 'Portugal')
GROUP BY md.match_no
HAVING COUNT(DISTINCT sc.country_id) = 2
)
AND sc.country_name = 'Portugal'
AND EXISTS (
SELECT 1
FROM goal_details gd2
WHERE gd2.match_no = md.match_no
AND gd2.team_id = st.team_id
HAVING gd.goal_time = MAX(gd2.goal_time)
);
Go to:
PREV : Find the teams who played the heighest audience match.
NEXT : Find the 2nd highest stoppage time, added in 2nd half.
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.