w3resource
Soccer Database Exercises

SQL exercises on soccer Database: Find the match no, date of play and the 2nd highest stoppage time which have been added in the 2nd half of play

SQL soccer Database: Subqueries Exercise-11 with Solution

11. Write a query in SQL to find the match no, date of play and the 2nd highest stoppage time which have been added in the 2nd half of play.

Sample table: match_mast


Sample Solution:

SQL Code:

SELECT match_no,play_date,stop2_sec
FROM match_mast a
WHERE (2-1) = (
SELECT COUNT(DISTINCT(b.stop2_sec))
FROM match_mast b
WHERE b.stop2_sec > a.stop2_sec);

Sample Output:

 match_no | play_date  | stop2_sec
----------+------------+-----------
       15 | 2016-06-16 |       374
(1 row)

Query Visualization:

Duration:

Query visualization of Find the match no, date of play and the 2nd highest stoppage time which have been added in the 2nd half of play - Duration

Rows:

Query visualization of Find the match no, date of play and the 2nd highest stoppage time which have been added in the 2nd half of play - Rows

Cost:

Query visualization of Find the match no, date of play and the 2nd highest stoppage time which have been added in the 2nd half of play - Cost

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 teams played the match where 2nd highest stoppage time had been added in 2nd half of play.
Next: Write a query in SQL to find the team which was defeated by Portugal in EURO cup 2016 final.

What is the difficulty level of this exercise?



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