%%capture
%load_ext sql
%sql sqlite:///factbook.db
%%sql
SELECT *
FROM facts
LIMIT 5;
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
1 | af | Afghanistan | 652230 | 652230 | 0 | 32564342 | 2.32 | 38.57 | 13.89 | 1.51 |
2 | al | Albania | 28748 | 27398 | 1350 | 3029278 | 0.3 | 12.92 | 6.58 | 3.3 |
3 | ag | Algeria | 2381741 | 2381741 | 0 | 39542166 | 1.84 | 23.67 | 4.31 | 0.92 |
4 | an | Andorra | 468 | 468 | 0 | 85580 | 0.12 | 8.13 | 6.96 | 0.0 |
5 | ao | Angola | 1246700 | 1246700 | 0 | 19625353 | 2.78 | 38.78 | 11.49 | 0.46 |
%%sql
SELECT MIN(population), MAX(population), MIN(population_growth), MAX(population_growth)
FROM facts;
Done.
MIN(population) | MAX(population) | MIN(population_growth) | MAX(population_growth) |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
%%sql
SELECT name
FROM facts
WHERE population IN (SELECT MIN(population) FROM facts);
Done.
name |
---|
Antarctica |
%%sql
SELECT name
FROM facts
WHERE population IN (SELECT MAX(population) FROM facts);
Done.
name |
---|
World |
(eliminating outliers)
%%sql
SELECT ROUND(AVG(population)), ROUND(AVG(area), 2)
FROM facts
WHERE name != 'World';
Done.
ROUND(AVG(population)) | ROUND(AVG(area), 2) |
---|---|
32242667.0 | 555093.55 |
%%sql
SELECT name
FROM facts
WHERE population > (SELECT AVG(population) FROM facts) AND
area < (SELECT AVG(area) FROM facts);
Done.
name |
---|
Bangladesh |
Germany |
Japan |
Philippines |
Thailand |
United Kingdom |
Vietnam |
Which countries have more water than land?
%%sql
SELECT name
FROM facts
WHERE area_water > area_land;
Done.
name |
---|
British Indian Ocean Territory |
Virgin Islands |
Which countries will add the most people to their population next year?
%%sql
SELECT name, population_growth
FROM facts
ORDER BY population_growth DESC
LIMIT 10;
Done.
name | population_growth |
---|---|
South Sudan | 4.02 |
Malawi | 3.32 |
Burundi | 3.28 |
Niger | 3.25 |
Uganda | 3.24 |
Qatar | 3.07 |
Burkina Faso | 3.03 |
Mali | 2.98 |
Cook Islands | 2.95 |
Iraq | 2.93 |
Which countries have a higher death rate than birth rate?
%%sql
SELECT name, death_rate, birth_rate
FROM facts
WHERE death_rate > birth_rate
ORDER BY death_rate - birth_rate DESC;
Done.
name | death_rate | birth_rate |
---|---|---|
Bulgaria | 14.44 | 8.92 |
Serbia | 13.66 | 9.08 |
Latvia | 14.31 | 10.0 |
Lithuania | 14.27 | 10.1 |
Ukraine | 14.46 | 10.72 |
Hungary | 12.73 | 9.16 |
Germany | 11.42 | 8.47 |
Slovenia | 11.37 | 8.42 |
Romania | 11.9 | 9.14 |
Croatia | 12.18 | 9.45 |
Belarus | 13.36 | 10.7 |
Monaco | 9.24 | 6.65 |
Greece | 11.09 | 8.66 |
Saint Pierre and Miquelon | 9.72 | 7.42 |
Russia | 13.69 | 11.6 |
Estonia | 12.4 | 10.51 |
Portugal | 11.02 | 9.27 |
Japan | 9.51 | 7.93 |
Italy | 10.19 | 8.74 |
Bosnia and Herzegovina | 9.75 | 8.87 |
Czech Republic | 10.34 | 9.63 |
Moldova | 12.59 | 12.0 |
Poland | 10.19 | 9.74 |
Austria | 9.42 | 9.41 |