w3resource

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

SQL soccer Database: Joins Exercise-6 with Solution

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:

Relational Algebra Expression: Find the country where Football EURO cup 2016 held.

Relational Algebra Tree:

Relational Algebra Tree: Find the country where Football EURO cup 2016 held.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the country where Football EURO cup 2016 held - Duration

Rows:

Query visualization of Find the country where Football EURO cup 2016 held - Rows

Cost:

Query visualization of Find the country where Football EURO cup 2016 held - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the scorer of only goal along with his country.
Next SQL Exercise: Find the player who socred first goal of EURO cup 2016.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.