SQL Exercise: Find the date when penalty shootout matches played

Last update on January 16 2026 12:32:31 (UTC/GMT +8 hours)

From the following table, write a SQL query to find out when the penalty shootout matches were played. Return playing date.

Sample table: soccer_venue

 venue_id |       venue_name        | city_id | aud_capacity
----------+-------------------------+---------+--------------
    20001 | Stade de Bordeaux       |   10003 |        42115
    20002 | Stade Bollaert-Delelis  |   10004 |        38223
    20003 | Stade Pierre Mauroy     |   10005 |        49822
    20004 | Stade de Lyon           |   10006 |        58585
    20005 | Stade VElodrome         |   10007 |        64354
    20006 | Stade de Nice           |   10008 |        35624
    20007 | Parc des Princes        |   10001 |        47294
    20008 | Stade de France         |   10002 |        80100
    20009 | Stade Geoffroy Guichard |   10009 |        42000
    20010 | Stadium de Toulouse     |   10010 |        33150

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
.........
       51 | F          | 2016-07-11 | WIN     | N          | 1-0        |    20008 |      70005 |   75868 |       160307 |       161 |       181

View the table

Sample table: penalty_shootout

 kick_id | match_no | team_id | player_id | score_goal | kick_no
---------+----------+---------+-----------+------------+---------
       1 |       37 |    1221 |    160467 | Y          |       1
       2 |       37 |    1213 |    160297 | Y          |       2
       3 |       37 |    1221 |    160477 | N          |       3
       4 |       37 |    1213 |    160298 | Y          |       4
       5 |       37 |    1221 |    160476 | Y          |       5
       6 |       37 |    1213 |    160281 | Y          |       6
       7 |       37 |    1221 |    160470 | Y          |       7
       8 |       37 |    1213 |    160287 | Y          |       8
       9 |       37 |    1221 |    160469 | Y          |       9
.........
      37 |       47 |    1208 |    160166 | Y          |      18

View the table

Sample Solution:

SQL Code:

SELECT play_date
FROM match_mast
WHERE match_no IN
    (SELECT DISTINCT match_no
     FROM penalty_shootout);

Sample Output:

 play_date
------------
 2016-07-03
 2016-07-01
 2016-06-25
(3 rows)

Code Explanation:

The said query in SQL that retrieves the play date for all matches in which penalty shootouts occurred from the match_mast table.
The condition filters the rows in the match_mast table to only include those where the match number matches a value returned by a subquery. The subquery selects distinct match numbers from the penalty_shootout table.

Alternative Solution:

JOIN with Subquery:


SELECT v.venue_name
FROM soccer_venue v
JOIN match_mast m ON v.venue_id = m.venue_id
WHERE m.match_no IN (
    SELECT DISTINCT match_no
    FROM penalty_shootout
);

Explanation:

This query uses a JOIN operation between soccer_venue and match_mast based on the venue_id. Then, it filters the results based on the condition that match_no should be in the list of distinct match_no values obtained from the subquery.

Using EXISTS:


SELECT play_date
FROM match_mast m
WHERE EXISTS (
    SELECT 1
    FROM penalty_shootout p
    WHERE m.match_no = p.match_no
);

Explanation:

This query uses the EXISTS keyword. It checks if there exists at least one row in the penalty_shootout table (aliased as p) where the match_no matches.

Go to:

PREV : Find the venues where penalty shootout matches played.
NEXT : Most quickest goal at EURO Cup 2016, after 5 minutes.

Practice Online