SQL Exercise: Stadium hosted the final match of EURO cup 2016
13. From the following tables, write a SQL query to find out which stadium hosted the final match of the 2016 Euro Cup. Return venue_name, city, aud_capacity, audience.
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 | 1207Sample table: match_mast 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:
-- Selecting venue_name, city, aud_capacity, and audience
SELECT a.venue_name, b.city, a.aud_capacity, c.audence
-- 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 matches in the final play stage
WHERE play_stage = 'F';
Sample Output:
venue_name | city | aud_capacity | audence -----------------+-------------+--------------+--------- Stade de France | Saint-Denis | 80100 | 75868 (1 row)
Code Explanation:
The said query in SQL that selects the name of the venue where the final is played, the city in which the venue is located, the capacity of the venue, and the audience count for all matches in the "Finals" stage from the tables soccer_venue, soccer_city, and match_mast.
The JOIN keyword is used to join the tables together based on their city_id and venue_id columns.
The JOIN keyword is used to join 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 is used to filter records for matches in the "Finals" stage.
Alternative Solutions:
Inner Join with Match_Mast:
-- Selecting venue_name, city, aud_capacity, and audience for matches in the final play stage
SELECT a.venue_name, b.city, a.aud_capacity, c.audence
-- 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 matches in the final play stage
WHERE c.play_stage = 'F'
-- Filtering out matches where there exists a match with a higher match_no for the same venue
AND NOT EXISTS (
-- Subquery to check if there is at least one match with a higher match_no for the same venue
SELECT 1
-- From clause to select from match_mast (aliased as d)
FROM match_mast d
-- Conditions for joining match_mast based on venue_id and comparing match_no
WHERE a.venue_id = d.venue_id
AND d.match_no > c.match_no
);
Explanation:
This query uses an inner join to connect the tables. Additionally, it includes a subquery in the NOT EXISTS clause to filter out matches that are not the final one.
Window Function:
-- Selecting venue_name, city, aud_capacity, and audience for the latest match in the final play stage for each venue
SELECT venue_name, city, aud_capacity, audence
-- From clause with a subquery (aliased as subquery) using a window function
FROM (
-- Subquery to select venue_name, city, aud_capacity, audience, and row number partitioned by venue_id
SELECT a.venue_name, b.city, a.aud_capacity, c.audence,
ROW_NUMBER() OVER (PARTITION BY a.venue_id ORDER BY c.match_no DESC) as row_num
-- 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 matches in the final play stage
WHERE play_stage = 'F'
) as subquery
-- Filtering the results to include only rows where the row number is 1
WHERE row_num = 1;
Explanation:
This query uses a window function ROW_NUMBER() to assign a unique number to each row within each venue, ordered by match_no in descending order. It then selects only the rows with row_num = 1, which corresponds to the highest match number for each venue with play_stage equal to 'F'.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: City where the opening match of EURO cup 2016 played.
Next SQL Exercise: Number of matches played at each venue and their city.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.