w3resource
Soccer Database Exercises

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. Write a query in SQL to 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.

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

soccer database relationship structure

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

Previous: Write a query in SQL to find the matchs ending with a goalless draw in group stage of play.
Next: Write a query in SQL to find the player and his team and how many matches he kept goal for his team.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming