SQL Exercise: Matches with most stoppage time added in the 2nd half
21. From the following tables, write a SQL query to find those matches where the highest stoppage time was added in 2nd half of play. Return match number, country name, stoppage time(sec.).
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: match_mast
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
........
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
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 match_no, country_name, and stop2_sec from match_mast, match_details, and soccer_country
SELECT match_details.match_no, soccer_country.country_name, match_mast.stop2_sec as "Stoppage Time(sec.)"
-- From clause with JOINs between match_mast, match_details, and soccer_country
FROM match_mast
JOIN match_details ON match_mast.match_no = match_details.match_no
JOIN soccer_country ON match_details.team_id = soccer_country.country_id
-- Where clause to filter results for matches where stop2_sec is in the set of maximum stop2_sec values in match_mast
WHERE stop2_sec IN (
-- Subquery to find the maximum value of stop2_sec in the match_mast table
SELECT MAX(stop2_sec)
FROM match_mast
);
Sample Output:
match_no | country_name | Stoppage Time(sec.)
----------+------------------+---------------------
17 | Ukraine | 411
17 | Northern Ireland | 411
(2 rows)
Code Explanation:
The said query in SQL that selects the match number, country name, and stoppage time in seconds for all matches where the stoppage time in seconds is equal to the maximum stoppage time in seconds across all matches from the tables match_mast, match_details, and soccer_country.
The JOIN clause is used to join the tables match_mast and match_details table based on the match_no column and join the soccer_country table with the results of previous join based on team_id columns of match_details table and country_id column of soccer_country table.
The WHERE clause filters the results to only show matches where the stop2_sec column in the match_mast table is equal to the maximum value of stop2_sec in the same table. The subquery inside the WHERE clause is used to get the maximum value of stop2_sec from the match_mast table.
Alternative Solutions:
Using Subquery in the WHERE Clause:
-- Selecting match_no, country_name, and stop2_sec from match_mast, match_details, and soccer_country
SELECT md.match_no, sc.country_name, mm.stop2_sec as "Stoppage Time(sec.)"
-- From clause with JOINs between match_mast, match_details, and soccer_country
FROM match_mast mm
JOIN match_details md ON mm.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
-- Where clause to filter results for matches where stop2_sec is equal to the maximum stop2_sec value in match_mast
WHERE mm.stop2_sec = (SELECT MAX(stop2_sec) FROM match_mast);
Explanation:
This query uses a subquery in the WHERE clause to filter the matches. It first selects the maximum stop2_sec from match_mast, and then checks if stop2_sec is equal to the maximum.
Using Subquery in the FROM Clause (Derived Table):
-- This query selects specific columns from multiple tables and calculates stoppage time for a soccer match.
SELECT
md.match_no, -- Selecting the match number from match_details table
sc.country_name, -- Selecting the country name from soccer_country table
mm.stop2_sec as "Stoppage Time(sec.)" -- Selecting the stoppage time from the derived table aliased as mm
FROM (
-- Subquery to select all columns from match_mast table where stop2_sec equals the maximum stoppage time in the match
SELECT *
FROM match_mast
WHERE stop2_sec = (SELECT MAX(stop2_sec) FROM match_mast)
) mm
JOIN match_details md ON mm.match_no = md.match_no -- Joining match_details table with the derived table on match number
JOIN soccer_country sc ON md.team_id = sc.country_id; -- Joining soccer_country table with match_details table on team id
Explanation:
This query uses a subquery in the FROM clause to create a derived table (mm) that contains the records where stop2_sec is the maximum. The main query then joins this derived table with match_details and soccer_country to get the desired columns.
Go to:
PREV : Team(s) who conceded the most goals in EURO cup 2016.
NEXT : Matches end in a goalless draw in group stage of play.
Practice Online
Sample Database: soccer
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.