SQL Exercise: Where was the final match of the EURO cup 2016 held?
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 | 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
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:
Go to:
PREV : SQL JOINS Exercises on Soccer Database
NEXT : Number of goals scored by each team in each match.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.