Some of the data provided in this factbook is as follows:
%%capture
%load_ext sql
%sql sqlite:///factbook.db
'Connected: None@factbook.db'
%%sql
SELECT *
FROM sqlite_master
WHERE type='table';
Done.
type | name | tbl_name | rootpage | sql |
---|---|---|---|---|
table | sqlite_sequence | sqlite_sequence | 3 | CREATE TABLE sqlite_sequence(name,seq) |
table | facts | facts | 47 | CREATE TABLE "facts" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "code" varchar(255) NOT NULL, "name" varchar(255) NOT NULL, "area" integer, "area_land" integer, "area_water" integer, "population" integer, "population_growth" float, "birth_rate" float, "death_rate" float, "migration_rate" float) |
%%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) AS min_pop, MAX(population) AS max_pop, MIN(population_growth) AS min_pop_growth, MAX(population_growth) AS max_pop_growth
FROM facts;
Done.
min_pop | max_pop | min_pop_growth | max_pop_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 |
We can see that the data includes a row for the whole World called 'World' which is skewing our country population data. We'll rerun the same stats without this row below.
%%sql
SELECT MIN(population) AS min_pop, MAX(population) AS max_pop, MIN(population_growth) AS min_pop_growth, MAX(population_growth) AS max_pop_growth
FROM facts
WHERE name != 'World';
Done.
min_pop | max_pop | min_pop_growth | max_pop_growth |
---|---|---|---|
0 | 1367485388 | 0.0 | 4.02 |
%%sql
SELECT ROUND(AVG(population), 2) AS avg_pop, ROUND(AVG(area), 2) AS avg_area
FROM facts;
Done.
avg_pop | avg_area |
---|---|
62094928.32 | 555093.55 |
%%sql
SELECT *
FROM facts
WHERE population > (SELECT ROUND(AVG(population), 2)
FROM facts
WHERE name <> 'World')
AND area < (SELECT ROUND(AVG(area), 2)
FROM facts
WHERE name <> 'World')
ORDER BY population DESC;
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
14 | bg | Bangladesh | 148460 | 130170 | 18290 | 168957745 | 1.6 | 21.14 | 5.61 | 0.46 |
85 | ja | Japan | 377915 | 364485 | 13430 | 126919659 | 0.16 | 7.93 | 9.51 | 0.0 |
138 | rp | Philippines | 300000 | 298170 | 1830 | 100998376 | 1.61 | 24.27 | 6.11 | 2.09 |
192 | vm | Vietnam | 331210 | 310070 | 21140 | 94348835 | 0.97 | 15.96 | 5.93 | 0.3 |
65 | gm | Germany | 357022 | 348672 | 8350 | 80854408 | 0.17 | 8.47 | 11.42 | 1.24 |
173 | th | Thailand | 513120 | 510890 | 2230 | 67976405 | 0.34 | 11.19 | 7.8 | 0.0 |
185 | uk | United Kingdom | 243610 | 241930 | 1680 | 64088222 | 0.54 | 12.17 | 9.35 | 2.54 |
83 | it | Italy | 301340 | 294140 | 7200 | 61855120 | 0.27 | 8.74 | 10.19 | 4.1 |
91 | ks | Korea, South | 99720 | 96920 | 2800 | 49115196 | 0.14 | 8.19 | 6.75 | 0.0 |
163 | sp | Spain | 505370 | 498980 | 6390 | 48146134 | 0.89 | 9.64 | 9.04 | 8.31 |
139 | pl | Poland | 312685 | 304255 | 8430 | 38562189 | 0.09 | 9.74 | 10.19 | 0.46 |
182 | ug | Uganda | 241038 | 197100 | 43938 | 37101745 | 3.24 | 43.79 | 10.69 | 0.74 |
80 | iz | Iraq | 438317 | 437367 | 950 | 37056169 | 2.93 | 31.45 | 3.77 | 1.62 |
120 | mo | Morocco | 446550 | 446300 | 250 | 33322699 | 1.0 | 18.2 | 4.81 | 3.36 |
Which country has the most people?
%%sql
SELECT *
FROM facts
Where name <> 'World'
ORDER BY population DESC
LIMIT 1;
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
37 | ch | China | 9596960 | 9326410 | 270550 | 1367485388 | 0.45 | 12.49 | 7.53 | 0.44 |
Which country has the highest growth rate?
%%sql
SELECT *
FROM facts
WHERE name != 'World'
ORDER BY population_growth DESC
LIMIT 1;
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
162 | od | South Sudan | 644329 | None | None | 12042910 | 4.02 | 36.91 | 8.18 | 11.47 |
Which countries have the highest ratios of water to land?
%%sql
SELECT name, (area_water / area_land) AS water_land_ratio
FROM facts
WHERE name != 'World'
ORDER BY water_land_ratio DESC
LIMIT 3;
Done.
name | water_land_ratio |
---|---|
British Indian Ocean Territory | 905 |
Virgin Islands | 4 |
Afghanistan | 0 |
Which countries have more water than land?
From our above query, we can see that only 2 countries have more water than land:
Which countries will add the most people to their populations next year?
%%sql
SELECT name, ROUND(birth_rate - death_rate, 2) AS birth_ratio
FROM facts
WHERE name != 'World'
ORDER BY birth_ratio DESC
LIMIT 3;
Done.
name | birth_ratio |
---|---|
Malawi | 33.15 |
Uganda | 33.1 |
Niger | 33.03 |
From the above query, we can see that the top 3 countries which will add the most people to their population next year according to birth ratio are:
All of which happen to be countries in the African Continent (where it's very hot climate)
Which countries have a higher death rate than birth rate?
%%sql
SELECT name, ROUND(death_rate - birth_rate, 2) AS death_ratio
FROM facts
WHERE name != 'World'
ORDER BY death_ratio DESC
LIMIT 3;
Done.
name | death_ratio |
---|---|
Bulgaria | 5.52 |
Serbia | 4.58 |
Latvia | 4.31 |
In the above query, we can see the top 3 countries which have higher death to birth ratios:
All 3 countries happen to be in Eastern Europe (where it's extremely cold)
Which countries have the highest population/area ratio, and how does it compare to list we found in the previous screen?
%%sql
SELECT name, ROUND(population / area, 2) AS pop_area_ratio
FROM facts
WHERE name != 'World'
ORDER BY pop_area_ratio DESC
LIMIT 3;
Done.
name | pop_area_ratio |
---|---|
Macau | 21168.0 |
Monaco | 15267.0 |
Singapore | 8141.0 |
In the above query, we can see that the top 3 countries with the highest population/area ratio are:
Interestingly, these 3 countries are well developed and known to be some of the richest/most expensive countries.