w3resource

SQL Exercise: Matches, 2nd highest stoppage time in the 2nd half

SQL soccer Database: Subqueries Exercise-11 with Solution

11. 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.

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
       10 | G          | 2016-06-14 | WIN     | N          | 0-2        |    20004 |      70005 |   55408 |       160244 |        63 |       189
       11 | G          | 2016-06-14 | WIN     | N          | 0-2        |    20001 |      70018 |   34424 |       160197 |        61 |       305
       12 | G          | 2016-06-15 | DRAW    | N          | 1-1        |    20009 |      70004 |   38742 |       160320 |        15 |       284
       13 | G          | 2016-06-15 | WIN     | N          | 1-2        |    20003 |      70001 |   38989 |       160405 |        62 |       189
       14 | G          | 2016-06-15 | DRAW    | N          | 1-1        |    20007 |      70015 |   43576 |       160477 |        74 |       206
       15 | G          | 2016-06-16 | WIN     | N          | 2-0        |    20005 |      70013 |   63670 |       160154 |        71 |       374
       16 | G          | 2016-06-16 | WIN     | N          | 2-1        |    20002 |      70003 |   34033 |       160540 |        62 |       212
       17 | G          | 2016-06-16 | WIN     | N          | 0-2        |    20004 |      70016 |   51043 |       160262 |         7 |       411
       18 | G          | 2016-06-17 | DRAW    | N          | 0-0        |    20008 |      70008 |   73648 |       160165 |         6 |       208
       19 | G          | 2016-06-17 | WIN     | N          | 1-0        |    20010 |      70007 |   29600 |       160248 |         2 |       264
       20 | G          | 2016-06-17 | DRAW    | N          | 2-2        |    20009 |      70005 |   38376 |       160086 |        71 |       280
       21 | G          | 2016-06-18 | WIN     | N          | 3-0        |    20006 |      70010 |   33409 |       160429 |        84 |       120
       22 | G          | 2016-06-18 | WIN     | N          | 3-0        |    20001 |      70004 |   39493 |       160064 |        11 |       180
       23 | G          | 2016-06-18 | DRAW    | N          | 1-1        |    20005 |      70015 |   60842 |       160230 |        61 |       280
       24 | G          | 2016-06-19 | DRAW    | N          | 0-0        |    20007 |      70011 |   44291 |       160314 |         3 |       200
       25 | G          | 2016-06-20 | WIN     | N          | 0-1        |    20004 |      70016 |   49752 |       160005 |       125 |       328
       26 | G          | 2016-06-20 | DRAW    | N          | 0-0        |    20003 |      70001 |   45616 |       160463 |        60 |       122
       27 | G          | 2016-06-21 | WIN     | N          | 0-3        |    20010 |      70006 |   28840 |       160544 |        62 |       119
       28 | G          | 2016-06-21 | DRAW    | N          | 0-0        |    20009 |      70012 |   39051 |       160392 |        62 |       301
       29 | G          | 2016-06-21 | WIN     | N          | 0-1        |    20005 |      70017 |   58874 |       160520 |        29 |       244
       30 | G          | 2016-06-21 | WIN     | N          | 0-1        |    20007 |      70018 |   44125 |       160177 |        21 |       195
       31 | G          | 2016-06-22 | WIN     | N          | 0-2        |    20002 |      70013 |   32836 |       160504 |        60 |       300
       32 | G          | 2016-06-22 | WIN     | N          | 2-1        |    20001 |      70008 |   37245 |       160085 |        70 |       282
       33 | G          | 2016-06-22 | WIN     | N          | 2-1        |    20008 |      70009 |   68714 |       160220 |         7 |       244
       34 | G          | 2016-06-22 | DRAW    | N          | 3-3        |    20004 |      70002 |   55514 |       160322 |        70 |       185
       35 | G          | 2016-06-23 | WIN     | N          | 0-1        |    20003 |      70014 |   44268 |       160333 |        79 |       221
       36 | G          | 2016-06-23 | WIN     | N          | 0-1        |    20006 |      70003 |   34011 |       160062 |        63 |       195
       37 | R          | 2016-06-25 | WIN     | P          | 1-1        |    20009 |      70005 |   38842 |       160476 |       126 |       243
       38 | R          | 2016-06-25 | WIN     | N          | 1-0        |    20007 |      70002 |   44342 |       160547 |         5 |       245
       39 | R          | 2016-06-26 | WIN     | N          | 0-1        |    20002 |      70012 |   33523 |       160316 |        61 |       198
       40 | R          | 2016-06-26 | WIN     | N          | 2-1        |    20004 |      70011 |   56279 |       160160 |       238 |       203
       41 | R          | 2016-06-26 | WIN     | N          | 3-0        |    20003 |      70009 |   44312 |       160173 |        62 |       124
       42 | R          | 2016-06-27 | WIN     | N          | 0-4        |    20010 |      70010 |   28921 |       160062 |         3 |       133
       43 | R          | 2016-06-27 | WIN     | N          | 2-0        |    20008 |      70004 |   76165 |       160235 |        63 |       243
       44 | R          | 2016-06-28 | WIN     | N          | 1-2        |    20006 |      70001 |   33901 |       160217 |         5 |       199
       45 | Q          | 2016-07-01 | WIN     | P          | 1-1        |    20005 |      70003 |   62940 |       160316 |        58 |       181
       46 | Q          | 2016-07-02 | WIN     | N          | 3-1        |    20003 |      70001 |   45936 |       160550 |        14 |       182
       47 | Q          | 2016-07-03 | WIN     | P          | 1-1        |    20001 |      70007 |   38764 |       160163 |        63 |       181
       48 | Q          | 2016-07-04 | WIN     | N          | 5-2        |    20008 |      70008 |   76833 |       160159 |        16 |       125
       49 | S          | 2016-07-07 | WIN     | N          | 2-0        |    20004 |      70006 |   55679 |       160322 |         2 |       181
       50 | S          | 2016-07-08 | WIN     | N          | 2-0        |    20005 |      70011 |   64078 |       160160 |       126 |       275
       51 | F          | 2016-07-11 | WIN     | N          | 1-0        |    20008 |      70005 |   75868 |       160307 |       161 |       181

Sample Solution:

SQL Code:

-- This SQL query retrieves match numbers, play dates, and stop2_sec values from the 'match_mast' table
-- where the difference in counts of distinct stop2_sec values greater than the current stop2_sec is equal to 1.

SELECT match_no, play_date, stop2_sec
-- Selects the 'match_no', 'play_date', and 'stop2_sec' columns.
FROM match_mast a
-- 'match_mast' is the name of the table being queried, using alias 'a'.
WHERE (2 - 1) = (
-- The WHERE clause checks if the difference between 2 and 1 is equal to the count of distinct stop2_sec values greater than the current stop2_sec in the subquery.
    SELECT COUNT(DISTINCT(b.stop2_sec))
    -- The subquery counts distinct stop2_sec values from the 'match_mast' table, using alias 'b'.
    FROM match_mast b
    -- 'match_mast' is the name of the table involved in the subquery, using alias 'b'.
    WHERE b.stop2_sec > a.stop2_sec
    -- Further filters rows in the subquery where stop2_sec is greater than the stop2_sec of the outer query.
);

Sample Output:

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

Code Explanation:

The said query in SQL that selects the match number, play date, and stop2_sec values from the match_mast table.
The WHERE clause of the outer query compares the value of (2-1) to a subquery that counts the number of distinct stop2_sec values in the match_mast table aliased as "b" that are greater than the stop2_sec value in the current row of the outer query ("a.stop2_sec").
The subquery is executed for each row in the match_mast table, comparing the stop2_sec value in the current row of the outer query to all other stop2_sec values in the table, counting the number of distinct values that are greater.
If the count of distinct stop2_sec values that are greater than the stop2_sec value in the current row of the outer query is equal to (2-1) = 1, the row is returned in the result set.
In other words, the query is selecting the matches where the stop2_sec value is the second lowest in the match_mast table.

Alternative Solutions:

Using Subquery with Correlation:


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

Explanation:

This query uses a correlated subquery to count the number of distinct stop2_sec values greater than the stop2_sec value of each row in the main query. It then compares this count to 1.

Using Subquery with JOIN:


SELECT a.match_no, a.play_date, a.stop2_sec
FROM match_mast a
JOIN (
    SELECT DISTINCT stop2_sec
    FROM match_mast
) b ON a.stop2_sec < b.stop2_sec
GROUP BY a.match_no, a.play_date, a.stop2_sec
HAVING COUNT(*) = 1;

Explanation:

This query uses a subquery with a join to create a list of distinct stop2_sec values. It then joins this list with the main table and groups the results. The HAVING clause ensures that only rows with a count of 1 are selected.

Using Self Join:


SELECT a.match_no, a.play_date, a.stop2_sec
FROM match_mast a
LEFT JOIN match_mast b ON a.stop2_sec < b.stop2_sec
GROUP BY a.match_no, a.play_date, a.stop2_sec
HAVING COUNT(b.stop2_sec) = 1;

Explanation:

This query uses a self-join on the match_mast table, where a.stop2_sec is less than b.stop2_sec. It then groups the results and uses the HAVING clause to select only rows where there is exactly one b.stop2_sec.

Practice Online


Sample Database: soccer

soccer database relationship structure

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

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

Previous SQL Exercise: Teams, 2nd highest stoppage time added in the 2nd half.
Next SQL Exercise: The team Portugal defeated in the EURO cup 2016 final.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.