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

SQL soccer Database: Joins Exercise-23 with Solution

23. From the following tables, write a SQL query to find those match(s) where the 2nd highest stoppage time had been added in the second half of play. Return match number, country name and stoppage time.

Sample table: match_mast

Sample table: match_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT a.match_no, c.country_name, a.stop2_sec
FROM match_mast a
JOIN match_details b ON a.match_no=b.match_no
JOIN soccer_country c ON b.team_id=c.country_id
WHERE (2-1) = (
SELECT COUNT(DISTINCT(b.stop2_sec))
FROM match_mast b
WHERE b.stop2_sec > a.stop2_sec);

Sample Output:

 match_no | country_name | stop2_sec
----------+--------------+-----------
       15 | France       |       374
       15 | Albania      |       374
(2 rows)

Practice Online

Sample Database: soccer

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

Previous: From the following tables, write a SQL query to find those matches ending with a goalless draw in-group stage of play. Return match number, country name.
Next: From the following tables, write a SQL query to find the number of matches played a player as a goalkeeper for his team. Return country name, player name, number of matches played as a goalkeeper.