w3resource

SQL Exercise: Stadium hosted the final match of EURO cup 2016

SQL soccer Database: Joins Exercise-13 with Solution

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      |       1207
Sample 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 Expression: Find the stadium hosted the final match of EURO cup 2016 along with the capacity, and audience for that match.

Relational Algebra Tree:

Relational Algebra Tree: Find the stadium hosted the final match of EURO cup 2016 along with the capacity, and audience for that match.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the stadium hosted the final match of EURO cup 2016 along with the capacity, and audance for that match - Duration

Rows:

Query visualization of Find the stadium hosted the final match of EURO cup 2016 along with the capacity, and audance for that match - Rows

Cost:

Query visualization of Find the stadium hosted the final match of EURO cup 2016 along with the capacity, and audance for that match - 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.



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-13.php