SQL exercises on soccer Database: Find the teams played the match where 2nd highest stoppage time had been added in 2nd half of play

SQL soccer Database: Subqueries Exercise-10 with Solution

10. From the following tables, write a SQL query to find the teams played the match where second highest stoppage time had been added in second half of play. Return country name of the teams.

Sample table: soccer_country

Sample table: match_details

Sample table: match_mast

Sample Solution:

SQL Code:

SELECT country_name 
FROM soccer_country 
WHERE country_id IN(
SELECT team_id 
FROM match_details 
WHERE match_no IN(
SELECT match_no 
FROM match_mast 
WHERE stop2_sec=(
SELECT max(stop2_sec) 
FROM match_mast
WHERE stop2_sec<>(
SELECT max(stop2_sec) 
FROM match_mast))));

Sample Output:

 country_name
--------------
 Albania
 France
(2 rows)

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find the second-highest stoppage time, which had been added, in the second half of play.
Next: From the following table, write a SQL query to find the teams played the match where second highest stoppage time had been added in second half of play. Return match_no, play_date, stop2_sec.