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 |