SQL Exercise: Teams, 2nd highest stoppage time added in the 2nd half
10. From the following table, write a SQL query to find the teams played the match where the second highest stoppage time had been added in the second half of play. Return country name of the teams.
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 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 Solution:
SQL Code:
-- This SQL query retrieves the country names from the 'soccer_country' table
-- for teams that participated in matches where the 'stop2_sec' is the maximum value
-- and not equal to the maximum 'stop2_sec' in the entire 'match_mast' table.
SELECT country_name
-- Selects the 'country_name' column.
FROM soccer_country
-- 'soccer_country' is the name of the table being queried.
WHERE country_id IN (
-- The WHERE clause filters rows where 'country_id' is present in the result of the subquery.
SELECT team_id
-- The subquery selects the 'team_id' column.
FROM match_details
-- 'match_details' is the name of the table involved in the subquery.
WHERE match_no IN (
-- Further filters rows in the subquery where 'match_no' is present in the result of another subquery.
SELECT match_no
-- This subquery selects the 'match_no' column.
FROM match_mast
-- 'match_mast' is the name of the table involved in the subquery.
WHERE stop2_sec = (
-- Further filters rows in this subquery where 'stop2_sec' is equal to the maximum 'stop2_sec' in the sub-subquery.
SELECT max(stop2_sec)
-- This sub-subquery calculates the maximum 'stop2_sec'.
FROM match_mast
-- 'match_mast' is the name of the table involved in the sub-subquery.
WHERE stop2_sec <> (
-- Further filters rows in the sub-sub-subquery where 'stop2_sec' is not equal to the maximum 'stop2_sec' in the sub-subquery.
SELECT max(stop2_sec)
-- This sub-sub-subquery calculates the maximum 'stop2_sec'.
FROM match_mast
-- 'match_mast' is the name of the table involved in the sub-sub-subquery.
)
)
)
);
Sample Output:
country_name -------------- Albania France (2 rows)
Code Explanation:
The said query in SQL that retrieves the names of countries that participated in the soccer matches where the stoppage time for the match was the second highest.
1. The uses of WHERE clause the "country_id" value should be in the list of "team_id" values retrieved from the 'match_details' table.
2. The uses of WHERE clause the "match_no" value should be in the list of "match_no" values retrieved from the 'match_mast' table.
3. The uses of WHERE clause the "stop2_sec" value should be equal to the second-highest value of "stop2_sec" in the 'match_mast' table.
The subquery in the parentheses retrieves the second-highest value of "stop2_sec" from the 'match_mast' table by excluding the maximum value.
Alternative Solutions:
Using Subqueries and JOIN:
SELECT sc.country_name
FROM soccer_country sc
JOIN match_details md ON sc.country_id = md.team_id
WHERE md.match_no IN (
SELECT mm.match_no
FROM match_mast mm
WHERE mm.stop2_sec = (
SELECT MAX(stop2_sec)
FROM match_mast
WHERE stop2_sec <> (
SELECT MAX(stop2_sec)
FROM match_mast
)
)
);
Explanation:
This query uses multiple subqueries and joins to find the countries based on the conditions specified.
Using Subqueries and EXISTS:
SELECT sc.country_name
FROM soccer_country sc
WHERE EXISTS (
SELECT 1
FROM match_details md
JOIN match_mast mm ON md.match_no = mm.match_no
WHERE mm.stop2_sec = (
SELECT MAX(stop2_sec)
FROM match_mast
WHERE stop2_sec <> (
SELECT MAX(stop2_sec)
FROM match_mast
)
)
AND sc.country_id = md.team_id
);
Explanation:
This query uses EXISTS with correlated subqueries to check if there is a match that meets the conditions.
Using JOIN and Subqueries:
SELECT sc.country_name
FROM soccer_country sc
JOIN match_details md ON sc.country_id = md.team_id
JOIN match_mast mm ON md.match_no = mm.match_no
WHERE mm.stop2_sec = (
SELECT MAX(stop2_sec)
FROM match_mast
WHERE stop2_sec <> (
SELECT MAX(stop2_sec)
FROM match_mast
)
);
Explanation:
This query joins the relevant tables and uses subqueries to find the countries based on the specified conditions.
Go to:
PREV : Find the 2nd highest stoppage time, added in 2nd half.
NEXT : Matches, 2nd highest stoppage time in the 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.