SQL Exercise: City where the opening match of EURO cup 2016 played

Last update on January 15 2026 12:41:40 (UTC/GMT +8 hours)

12. From the following table, write a SQL query to find the city where the opening match of EURO cup 2016 took place. Return venue name, city.

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: soccer_city

 city_id |     city      | country_id
---------+---------------+------------
   10001 | Paris         |       1207
   10002 | Saint-Denis   |       1207
   10003 | Bordeaux      |       1207
   10004 | Lens          |       1207
   10005 | Lille         |       1207
   10006 | Lyon          |       1207
   10007 | Marseille     |       1207
   10008 | Nice          |       1207
   10009 | Saint-Etienne |       1207
   10010 | Toulouse      |       1207

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 Solution:

SQL Code:

-- Selecting venue_name and city
SELECT a.venue_name, b.city
-- From clause with JOINs between soccer_venue, soccer_city, and match_mast
FROM soccer_venue a
JOIN soccer_city b ON a.city_id = b.city_id
JOIN match_mast c ON a.venue_id = c.venue_id
-- Filtering the results to include only the match with match_no equal to 1
WHERE match_no = 1;

Sample Output:

   venue_name    |    city
-----------------+-------------
 Stade de France | Saint-Denis
(1 row)

Code Explanation:

The said query in SQL that selects the name of the venue where match number 1 is played, and the city in which the venue is located from the tables soccer_venue, soccer_city, and match_mast.
The JOIN keyword is used to join the soccer_venue with soccer_city on their city_id columns, and then join the resulting table with match_mast on their venue_id columns.
The WHERE keyword filters data for the match with match number 1.

Alternative Solutions:

Subquery with IN:

-- Selecting venue_name and city
SELECT a.venue_name, b.city
-- From clause with JOIN between soccer_venue and soccer_city using city_id
FROM soccer_venue a
JOIN soccer_city b ON a.city_id = b.city_id
-- Filtering the results to include only venues with venue_id found in the subquery
WHERE a.venue_id IN (
    -- Subquery to select venue_id from match_mast for the match with match_no equal to 1
    SELECT venue_id
    -- From clause to select from match_mast
    FROM match_mast
    -- Condition for filtering match_mast based on match_no
    WHERE match_no = 1
);

Explanation:

This query uses a subquery to select the venue_id from match_mast where match_no is 1. It then uses this list of venue_id values to filter the soccer_venue table.

EXISTS Subquery:

-- Selecting venue_name and city
SELECT a.venue_name, b.city
-- From clause with JOIN between soccer_venue and soccer_city using city_id
FROM soccer_venue a
JOIN soccer_city b ON a.city_id = b.city_id
-- Using EXISTS to filter results based on a subquery condition
WHERE EXISTS (
    -- Subquery to check if there is at least one match for the venue with match_no equal to 1
    SELECT 1
    -- From clause to select from match_mast
    FROM match_mast c
    -- Conditions for joining match_mast based on venue_id and match_no
    WHERE a.venue_id = c.venue_id
    AND match_no = 1
);

Explanation:

This query uses an EXISTS subquery to check if there's at least one row in match_mast with the corresponding venue_id and match_no is 1. It joins soccer_venue and soccer_city tables.

Relational Algebra Expression: