SQL Exercise: Find the country where Football EURO cup 2016 held

Last update on December 20 2024 11:44:01 (UTC/GMT +8 hours)

6. From the following tables, write a SQL query to find out which country hosted the 2016 Football EURO Cup. Return country name.

Sample table: soccer_country

 country_id | country_abbr |    country_name
------------+--------------+---------------------
       1201 | ALB          | Albania
       1202 | AUT          | Austria
       1203 | BEL          | Belgium
       1204 | CRO          | Croatia
       1205 | CZE          | Czech Republic
       1206 | ENG          | England
       1207 | FRA          | France
       1208 | GER          | Germany
       1209 | HUN          | Hungary
       1210 | ISL          | Iceland
       1211 | ITA          | Italy
       1212 | NIR          | Northern Ireland
       1213 | POL          | Poland
       1214 | POR          | Portugal
       1215 | IRL          | Republic of Ireland
       1216 | ROU          | Romania
       1217 | RUS          | Russia
       1218 | SVK          | Slovakia
       1219 | ESP          | Spain
       1220 | SWE          | Sweden
       1221 | SUI          | Switzerland
       1222 | TUR          | Turkey
       1223 | UKR          | Ukraine
       1224 | WAL          | Wales
       1225 | SLO          | Slovenia
       1226 | NED          | Netherlands
       1227 | SRB          | Serbia
       1228 | SCO          | Scotland
       1229 | NOR          | Norway

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: 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 Solution:

SQL Code:

-- Selecting the unique country names
SELECT country_name
-- Joining soccer_country with soccer_city using country_id
FROM soccer_country a
JOIN soccer_city b ON a.country_id = b.country_id
-- Joining the result with soccer_venue using city_id
JOIN soccer_venue c ON b.city_id = c.city_id
-- Grouping the results by country_name to get unique country names
GROUP BY country_name;

Sample Output:

 country_name
--------------
 France
(1 row)

Code Explanation:

The said query in SQL that selects the unique country names of all countries that have soccer venues.
The JOIN clause joins the soccer_country and soccer_city table based on the country_id column and the soccer_city and soccer_venue tables are joins based on the city_id column.
The result set are grouped by country name.

Alternative Solutions:

Using Implicit JOIN:

-- Selecting the unique country names
SELECT a.country_name
-- From clause with implicit joins between soccer_country, soccer_city, and soccer_venue
FROM soccer_country a, soccer_city b, soccer_venue c
-- Conditions for joining the tables based on country_id and city_id
WHERE a.country_id = b.country_id
AND b.city_id = c.city_id
-- Grouping the results by country_name to get unique country names
GROUP BY a.country_name;

Explanation:

This query uses implicit joins to combine the tables soccer_country, soccer_city, and soccer_venue. It applies the necessary conditions in the WHERE clause and then groups the results.

Using EXISTS:

-- Selecting country names
SELECT country_name
-- From clause to select from soccer_country
FROM soccer_country a
-- Using EXISTS to filter results based on a subquery condition
WHERE EXISTS (
    -- Subquery to check if there is at least one corresponding city and venue for the country
    SELECT 1
    -- Joining soccer_city with soccer_venue using city_id
    FROM soccer_city b
    JOIN soccer_venue c ON b.city_id = c.city_id
    -- Checking if the country_id matches between soccer_country and soccer_city
    WHERE a.country_id = b.country_id
);

Explanation:

This query uses a subquery with EXISTS to check if there exists a row in the soccer_city and soccer_venue tables that matches the country_id. It then selects the country_name.

Using Subquery with IN:

-- Selecting country names
SELECT country_name
-- From clause to select from soccer_country
FROM soccer_country
-- Filtering the results to include only countries with corresponding cities and venues
WHERE country_id IN (
    -- Subquery to select country_id from soccer_city based on the relationship with soccer_venue
    SELECT b.country_id
    -- Joining soccer_city with soccer_venue using city_id
    FROM soccer_city b
    JOIN soccer_venue c ON b.city_id = c.city_id
);

Explanation:

This query uses a subquery with IN to retrieve the country_id values from the subquery that joins soccer_city and soccer_venue. It then selects the corresponding country_name.

Relational Algebra Expression: