SQL Exercise: Who scored the last goal in the 2nd semi-final
20. From the following table, write a SQL query to find the players who scored the last goal in the second semi-final, i.e., the 50th match of the 2016-EURO Cup. Return player name, goal time, goal half, 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
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: 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:
-- Selecting player_name, goal_time, goal_half, and country_name
SELECT a.player_name, b.goal_time, b.goal_half, c.country_name
-- Joining player_mast, goal_details, and soccer_country tables
FROM player_mast a, goal_details b, soccer_country c
-- Matching player_id between player_mast and goal_details, and team_id between goal_details and soccer_country
WHERE a.player_id=b.player_id
AND b.team_id=c.country_id
-- Filtering by match_no
AND match_no=50
-- Filtering by goal_time equal to maximum goal_time for match_no 50
AND goal_time= (
-- Subquery to find the maximum goal_time for match_no 50
SELECT MAX(goal_time)
FROM goal_details
WHERE match_no=50
);
Sample Output:
player_name | goal_time | goal_half | country_name
--------------------+-----------+-----------+--------------
Antoine Griezmann | 72 | 2 | France
(1 row)
Code Explanation:
The said query in SQL that retrieves the player name, goal time, goal half, and country name for the player who scored the latest goal in match number 50. It joins the 'player_mast', 'goal_details', and 'soccer_country' tables together using their respective IDs.
The subquery returns the maximum goal time for match number 50 in the 'goal_details' table, and the outer query filters the tables to only include the row where the goal time matches the maximum and the match number is 50.
Alternative Solution:
Using Subquery with ORDER BY and LIMIT:
-- Selecting player_name, goal_time, goal_half, and country_name
SELECT pm.player_name, gd.goal_time, gd.goal_half, sc.country_name
-- Joining player_mast and goal_details tables based on player_id
FROM player_mast pm
JOIN goal_details gd ON pm.player_id = gd.player_id
-- Joining goal_details and soccer_country tables based on team_id
JOIN soccer_country sc ON gd.team_id = sc.country_id
-- Filtering by match_no
WHERE gd.match_no = 50
-- Ordering the results by goal_time in descending order
ORDER BY gd.goal_time DESC
-- Limiting the result to one row, i.e., the player with the latest goal in match_no 50
LIMIT 1;
Explanation:
This query uses a subquery to find the maximum goal time in the goal_details table for match number 50. It then joins the relevant tables to retrieve the player name, goal time, goal half, and country name for the player who scored at that specific time.
Using JOIN and Subquery:
-- Selecting player_name, goal_time, goal_half, and country_name
SELECT pm.player_name, gd.goal_time, gd.goal_half, sc.country_name
-- Joining player_mast and goal_details tables based on player_id
FROM player_mast pm
JOIN goal_details gd ON pm.player_id = gd.player_id
-- Joining goal_details and soccer_country tables based on team_id
JOIN soccer_country sc ON gd.team_id = sc.country_id
-- Filtering by match_no
WHERE gd.match_no = 50
-- Filtering by goal_time equal to the maximum goal_time for match_no 50
AND gd.goal_time = (
-- Subquery to find the maximum goal_time for match_no 50
SELECT MAX(goal_time)
FROM goal_details
WHERE match_no = 50
);
Explanation:
This query uses a subquery to find the maximum goal time for match number 50. It then joins the relevant tables to retrieve the player name, goal time, goal half, and country name for the player who scored at that specific time.
Using MAX() with GROUP BY:
-- Selecting player_name, goal_time, goal_half, and country_name
SELECT pm.player_name, gd.goal_time, gd.goal_half, sc.country_name
-- Joining player_mast and goal_details tables based on player_id
FROM player_mast pm
JOIN goal_details gd ON pm.player_id = gd.player_id
-- Joining goal_details and soccer_country tables based on team_id
JOIN soccer_country sc ON gd.team_id = sc.country_id
-- Filtering by match_no
WHERE gd.match_no = 50
-- Filtering by goal_time equal to the maximum goal_time for match_no 50
AND gd.goal_time = (
-- Subquery to find the maximum goal_time for match_no 50
SELECT MAX(goal_time)
FROM goal_details
WHERE match_no = 50
-- Grouping by match_no to ensure only the maximum goal_time for match_no 50 is selected
GROUP BY match_no
);
Explanation:
This query uses the MAX() function along with GROUP BY to find the maximum goal time for match number 50. It then joins the relevant tables to retrieve the player name, goal time, goal half, and country name for the player who scored at that specific time.
Using ORDER BY and FETCH FIRST ROW ONLY:
-- Selecting player_name, goal_time, goal_half, and country_name
SELECT pm.player_name, gd.goal_time, gd.goal_half, sc.country_name
-- Joining player_mast and goal_details tables based on player_id
FROM player_mast pm
JOIN goal_details gd ON pm.player_id = gd.player_id
-- Joining goal_details and soccer_country tables based on team_id
JOIN soccer_country sc ON gd.team_id = sc.country_id
-- Filtering by match_no
WHERE gd.match_no = 50
-- Ordering the results by goal_time in descending order
ORDER BY gd.goal_time DESC
-- Fetching only the first row, i.e., the player with the latest goal in match_no 50
FETCH FIRST ROW ONLY;
Explanation:
This query uses the ORDER BY clause to sort the results by goal time in descending order. It then uses FETCH FIRST ROW ONLY to retrieve only the top row, which corresponds to the player with the latest goal in match number 50.
Go to:
PREV : Liverpool players in the England squad at 2016 EURO.
NEXT : The captain of Portugal EURO cup 2016 winning team.
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.