w3resource

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 Expression: Find the name of the venue with city where the EURO cup 2016 final match was played.

Relational Algebra Tree:

Relational Algebra Tree: Find the name of the venue with city where the EURO cup 2016 final match was played.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the name of the venue with city where the EURO cup 2016 final match was played - Duration

Rows:

Query visualization of Find the name of the venue with city where the EURO cup 2016 final match was played - Rows

Cost:

Query visualization of Find the name of the venue with city where the EURO cup 2016 final match was played - 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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-1.php