A SQL exercise

Here is a simple exercise of SQL on SQLZOO

The table is like this:

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000

The tenth question:

Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

The question means you need to group the countries by continents, find out the country that has the largest population in their continent group. In addition, you also need to find out the country that has the second largest population. Then compare the two countries. If the largest one has the population more than three times that the second largest one, select the country and show the continent.

It’s a bit tricky because we have built-in function to find out the largest one, but do not have the function that can find out the second largest one. We have to write our own functions.

Firstly, we try to find out the country that has the largest population in each continent group:

SELECT [First] = MAX(w.population), continent 
FROM world w
GROUP BY continent

We can get the result:

First continent
166629000 Africa
1347350000 Asia
81874000 Europe
314895000 North America
22833376 Oceania
193946886 South America

The we join the table above with the world table

SELECT w.name, w.population, t1.First, w.continent
FROM 
world w,
(
    SELECT [First] = MAX(w.population), continent 
    FROM world w
    GROUP BY continent
) t1
WHERE w.continent = t1.continent

Here we list the country info and the largest population group by continent together. The result is:

name population First continent
Algeria 37100000 166629000 Africa
Angola 20609294 166629000 Africa
Benin 9352000 166629000 Africa
Botswana 2024904 166629000 Africa
Burkina Faso 15730977 166629000 Africa

Then we can compare population and First to find out the second largest population group by continent.

SELECT [Second] = MAX(w.population), w.continent
FROM 
world w,
(
    SELECT [First] = MAX(w.population), continent 
    FROM world w
    GROUP BY continent
) t1
WHERE w.continent = t1.continent AND w.population < t1.First
GROUP BY w.continent

The result is:

Second continent
84320987 Africa
1210193422 Asia
74724269 Europe
112336538 North America
7170000 Oceania
46817000 South America

Then we join the largest population table and the second largest population table:

SELECT t1.continent, t1.First, t2.Second
FROM 
(
    SELECT [First] = MAX(w.population), continent 
    FROM world w
    GROUP BY continent
) t1,
(
    SELECT [Second] = MAX(w.population), w.continent
    FROM 
    world w,
    (
        SELECT [First] = MAX(w.population), continent 
        FROM world w
        GROUP BY continent
    ) t1
    WHERE w.continent = t1.continent AND w.population < t1.First
    GROUP BY w.continent
) t2
WHERE t1.continent = t2.continent

The result is:

continent First Second
Africa 166629000 84320987
Asia 1347350000 1210193422
Europe 81874000 74724269
North America 314895000 112336538
Oceania 22833376 7170000
South America 193946886 46817000

Find out from each continent group where the largest one has the population more than three times that the second largest one.

SELECT t1.continent, t1.First, t2.Second
FROM 
(
    SELECT [First] = MAX(w.population), continent 
    FROM world w
    GROUP BY continent
) t1,
(
    SELECT [Second] = MAX(w.population), w.continent
    FROM 
    world w,
    (
        SELECT [First] = MAX(w.population), continent 
        FROM world w
        GROUP BY continent
    ) t1
    WHERE w.continent = t1.continent AND w.population < t1.First
    GROUP BY w.continent
) t2
WHERE t1.continent = t2.continent
  AND t1.First > t2.Second * 3

The result is:

continent First Second
Oceania 22833376 7170000
South America 193946886 46817000

But we want to show the country of the largest population above, no the population itself, so we will join world table:

SELECT w1.name, w1.continent
FROM 
(
    SELECT [First] = MAX(w.population), continent 
    FROM world w
    GROUP BY continent
) t1,
(
    SELECT [Second] = MAX(w.population), w.continent
    FROM 
    world w,
    (
        SELECT [First] = MAX(w.population), continent 
        FROM world w
        GROUP BY continent
    ) t1
    WHERE w.continent = t1.continent AND w.population < t1.First
    GROUP BY w.continent
) t2,
world w1
WHERE t1.continent = t2.continent
  AND t1.First = w1.population 
  AND t1.First > t2.Second * 3

Then we get the final result:

name continent
Australia Oceania
Brazil South America

Leave a Reply

Your email address will not be published. Required fields are marked *