Nicky's Tech Blog

Here is a simple exercise of SQL on SQLZOO. 

The table is like this:

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:

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:

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:

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:

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:

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: