﻿ SQL: Where was the final match of the EURO cup 2016 held?

# 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      |       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
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.

﻿