SQL Exercise: Where was the final match of the EURO cup 2016 held?
SQL soccer Database: Joins Exercise-1 with Solution
1. From the following table, write a SQL query to find out where the final match of the EURO cup 2016 was played. 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 | 1207Sample 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 and city
SELECT venue_name, city
FROM soccer_venue a
-- Joining soccer_venue with soccer_city using city_id
JOIN soccer_city b ON a.city_id = b.city_id
-- Joining the result with match_mast using venue_id
JOIN match_mast d ON d.venue_id = a.venue_id
-- Filtering the results to include only matches in the final play stage
AND d.play_stage = 'F';
Sample Output:
venue_name | city -----------------+------------- Stade de France | Saint-Denis (1 row)
Code Explanation:
The given query in SQL that returns a list of all the venue names and their corresponding cities where matches with play_stage 'F' were held..
The query uses two JOIN operations to link the soccer_venue and soccer_city tables based on the common city_id column. It then uses a third JOIN operation to link the match_mast table with the previous result set based on the common venue_id column. The ON condition for this JOIN specifies that only rows where the play_stage column equals 'F' (which likely stands for "finals") are included in the final result set.
Alternative Solutions:
Using Subquery with WHERE Clause:
-- Selecting venue_name and city
SELECT venue_name, city
FROM soccer_venue
-- Joining soccer_venue with soccer_city using city_id
JOIN soccer_city ON soccer_venue.city_id = soccer_city.city_id
-- Filtering the results to include only venues with venue_id in the subquery result
WHERE venue_id IN (
-- Selecting venue_id from match_mast where play_stage is 'F'
SELECT venue_id
FROM match_mast
WHERE play_stage = 'F'
);
Explanation:
This query uses a subquery in the WHERE clause to find the venue_id values where the play_stage is 'F'. Then, we perform an inner join between soccer_venue and soccer_city tables to get the venue_name and city.
Using EXISTS Subquery:
-- Selecting venue_name and city
SELECT venue_name, city
FROM soccer_venue
-- Joining soccer_venue with soccer_city using city_id
JOIN soccer_city ON soccer_venue.city_id = soccer_city.city_id
-- Using EXISTS to filter results based on a subquery condition
WHERE EXISTS (
-- Selecting 1 (or any constant) from match_mast
SELECT 1
FROM match_mast
-- Matching venue_id in both tables and checking for play_stage 'F'
WHERE match_mast.venue_id = soccer_venue.venue_id
AND play_stage = 'F'
);
Explanation:
This query uses an EXISTS subquery to check if there exists a match in the match_mast table with the same venue_id and play_stage is 'F'. If such a match is found, it includes the venue in the result.
Using INNER JOIN with WHERE Clause:
-- Selecting venue_name and city
SELECT venue_name, city
-- Inner joining soccer_venue with soccer_city using city_id
FROM soccer_venue
INNER JOIN soccer_city ON soccer_venue.city_id = soccer_city.city_id
-- Inner joining the result with match_mast using venue_id
INNER JOIN match_mast ON soccer_venue.venue_id = match_mast.venue_id
-- Filtering the results to include only matches in the final play stage
WHERE match_mast.play_stage = 'F';
Explanation:
This query uses INNER JOINs to combine the tables soccer_venue, soccer_city, and match_mast. It then applies a WHERE clause to filter the results where play_stage is '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: SQL JOINS Exercises on Soccer Database
Next SQL Exercise: Number of goals scored by each team in each match.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-1.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics