SQL Practice: MONDIAL

The MONDIAL database comprises geographic and demographic information from the CIA World Factbook, Wikipedia, and other web sources. It contains a number of relations with a cyclic data graph (i.e., numerous foreign keys), as evidenced by its entity-relationship (ER) diagram, relational schema, and referential dependencies.

Download the PostgreSQL schema and data files and run both to create a local copy of the MONDIAL database. Then, complete the queries listed below (solutions).

Queries

Each of the following queries can – and should – be answered using a single SQL statement.

SQL SELECT

What is the name and land area of each continent?
```
SELECT name, area
FROM Continent;
```
What is the name of all ethnic groups? Do not include duplicates in the results.
```
SELECT DISTINCT name
FROM EthnicGroup;
```
What is the gross domestic product (GDP) of Switzerland (country code ‘CH’) and what percentage is provided by agriculture, service, and industry?
```
SELECT GDP, agriculture, service, industry
FROM Economy
WHERE country = 'CH';
```

What is the surface area and depth of Lake Malawi?

SELECT area, depth
FROM Lake
WHERE name = 'Lake Malawi';

What is the IATA code, name, and city of all airports in Colorado? (The country code for the United States is ‘USA’.)
```
SELECT iatacode, name, city
FROM Airport
WHERE country = 'USA'
  AND province = 'Colorado';
```

What are the names and capitals of the Canadian provinces (and territories)?

SELECT Province.name, Province.capital
FROM Province, Country
WHERE Province.country = Country.code
  AND Country.name = 'Canada';

In what country and mountain range is Aconcagua, the highest summit in the Western Hemisphere?

SELECT Country.name, Mountain.mountains
FROM Country, geo_mountain, Mountain
WHERE Country.code = geo_mountain.country
  AND geo_mountain.mountain = Mountain.name
  AND Mountain.name = 'Aconcagua';

Which countries (without including duplicates) intersect a desert that is greater than 1000000 square km in size?

SELECT DISTINCT Country.name
FROM Country, geo_desert, Desert
WHERE Country.code = geo_desert.country
  AND geo_desert.desert = Desert.name
  AND Desert.area > 1000000;

What are the names of countries and provinces through which the Rhine and Rhone rivers flow? (Hint: The German spelling of the Rhine must be used.)

SELECT Country.name, Province.name
FROM River, geo_river, Country, Province
WHERE River.name = geo_river.river
  AND geo_river.country = Country.code
  AND geo_river.province = Province.name
  AND River.name IN ('Rhein', 'Rhone');

Aggregates and Sorting

What are the names and surface area of all the lakes in decreasing order of depth?
```
SELECT name, area
FROM Lake
ORDER BY depth DESC;
```

What is the name and percentage of the population for each ethnic group in Russia? Sort the results by decreasing percentage.

SELECT EthnicGroup.name, EthnicGroup.percentage
FROM EthnicGroup, Country
WHERE EthnicGroup.country = Country.code
  AND Country.name = 'Russia'
ORDER BY EthnicGroup.percentage DESC;

What is the name, state, population, and elevation of every city in the United States (as least as recorded in the database)? Sort the results by state in ascending order and within each state by population in descending order.

SELECT City.name, City.province AS state, City.population, City.elevation
FROM City JOIN Country ON City.country = Country.code
WHERE Country.name = 'United States'
ORDER BY state ASC, population DESC;

What is the depth of the deepest lake in the world?
```
SELECT MAX(depth)
FROM Lake;
```
What is the cumulative land area covered by islands?
```
SELECT SUM(area)
FROM Island;
```

How many rivers flow through each country that has at least 25 rivers?

SELECT Country.name, COUNT(*)
FROM Country, geo_river
WHERE Country.code = geo_river.country
GROUP BY Country.name
HAVING COUNT(*) >= 20;

What is the mean rate of inflation across all African countries?

SELECT AVG(Economy.inflation)
FROM Economy, Country, encompasses, Continent
WHERE Economy.country = Country.code
  AND Country.code = encompasses.country
  AND encompasses.continent = Continent.name
  AND Continent.name = 'Africa';

What is the cumulative gross domestic product (GDP) of each continent? Sort the results by GDP in decreasing order.

SELECT Continent.name, SUM(Economy.GDP) AS GDP
FROM Continent, encompasses, Country, Economy
WHERE Continent.name = encompasses.continent
  AND encompasses.country = Country.code
  AND Country.code = Economy.country
GROUP BY Continent.name
ORDER BY GDP DESC;

Joins

What are all the countries where agriculture comprises at least 50% of the gross domestic product? Write your query using the INNER JOIN ... ON syntax.
```
SELECT Country.name
FROM Country INNER JOIN Economy ON Country.code = Economy.country
WHERE Economy.agriculture >= 50;
```

What are the names and capitals of the Canadian provinces? Write your query using the INNER JOIN ... ON syntax.

SELECT Province.name, Province.capital
FROM Province INNER JOIN Country ON Province.country = Country.code
WHERE Country.name = 'Canada';

What are the names and capitals of the Canadian provinces? Write your query using CROSS JOIN.
```
SELECT Province.name, Province.capital
FROM Province CROSS JOIN Country
WHERE Province.country = Country.code
  AND Country.name = 'Canada';
```
Is there any difference in the query plan between the current query and the prior one? (You can use PostgreSQL’s EXPLAIN command to display a query plan.)

No, the query plans are identical due to the query optimizer recognizing that restricting the Cartesian product to those rows where the province’s country matches the country code is equivalent to an inner join.

Which seas do not have any islands in them?

SELECT name
FROM Sea LEFT JOIN islandIn ON Sea.name = islandIn.sea
WHERE islandIn.island IS NULL;

What are the names of provinces through which no river flows (according to the database)?

SELECT Province.name
FROM Province LEFT JOIN geo_river ON Province.name = geo_river.province
WHERE geo_river.river IS NULL;

What is the name of all countries that do not border another country?

SELECT Country.name
FROM Country LEFT JOIN borders b1 ON Country.code = b1.country1
             LEFT JOIN borders b2 ON Country.code = b2.country2
WHERE b1.country1 IS NULL
  AND b2.country2 IS NULL;

Subqueries

What is the name of the longest river?

SELECT name
FROM River
WHERE length = (SELECT MAX(length) FROM River);

What are the names of provinces through which no river flows (according to the database)? Use a subquery and NOT IN.
```
SELECT name
FROM Province
WHERE name NOT IN (SELECT province FROM geo_river);
```
Compare the query plan to one using a LEFT JOIN (see comparable query completed previously). Do they differ?

What are all the countries where agriculture comprises at least 50% of the gross domestic product? Do not use any joins in your query.

SELECT name
FROM Country
WHERE code IN (SELECT country
               FROM Economy
               WHERE agriculture >= 50);

What is the name and percentage of all the ethnic groups in the United States where the percentage of the population of that ethnic group is lower than the average percentage across all ethnic groups in the United States?

SELECT EthnicGroup.name, EthnicGroup.percentage
FROM EthnicGroup INNER JOIN Country ON EthnicGroup.country = Country.code
WHERE EthnicGroup.percentage < (
              SELECT AVG(percentage)
              FROM EthnicGroup INNER JOIN Country
                      ON EthnicGroup.country = Country.code
              WHERE Country.name = 'United States'
      )
  AND Country.name = 'United States';

or, if the WITH clause is supported,

WITH EthnicGroup AS (
        SELECT *
        FROM EthnicGroup
        WHERE country = (SELECT code
                         FROM Country
                         WHERE name = 'United States')
)  -- restrict the content of the EthnicGroup table for this query
SELECT name, percentage
FROM EthnicGroup
WHERE percentage < (SELECT AVG(percentage) FROM EthnicGroup);

What are the two countries with the longest border?

SELECT c1.name, c2.name
FROM Country c1 JOIN Borders ON c1.code = Borders.country1
                JOIN Country c2 ON Borders.country2 = c2.code
WHERE Borders.length = (SELECT MAX(length) FROM Borders);

Which countries have an inflation rate that is greater than or equal to the average across all African countries?

SELECT Country.name
FROM Country JOIN Economy ON Country.code = Economy.country
WHERE Economy.inflation >= (
    SELECT AVG(Economy.inflation)
    FROM Economy JOIN Country ON Economy.country = Country.code
                 JOIN encompasses ON Country.code = encompasses.country
                 JOIN Continent ON encompasses.continent = Continent.name
    WHERE Continent.name = 'Africa'
);