In this project, we'll work with data from the CIA World Factbook, a compendium of statistics about all of the countries on Earth. Our focus will be on analysing the Demographics in the countries:
%%capture
%load_ext sql
%sql sqlite:///factbook.db
'Connected: None@factbook.db'
%%sql
SELECT *
FROM facts
LIMIT 10;
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 |
6 | ac | Antigua and Barbuda | 442 | 442 | 0 | 92436 | 1.24 | 15.85 | 5.69 | 2.21 |
7 | ar | Argentina | 2780400 | 2736690 | 43710 | 43431886 | 0.93 | 16.64 | 7.33 | 0.0 |
8 | am | Armenia | 29743 | 28203 | 1540 | 3056382 | 0.15 | 13.61 | 9.34 | 5.8 |
9 | as | Australia | 7741220 | 7682300 | 58920 | 22751014 | 1.07 | 12.15 | 7.14 | 5.65 |
10 | au | Austria | 83871 | 82445 | 1426 | 8665550 | 0.55 | 9.41 | 9.42 | 5.56 |
%%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 *
FROM facts
WHERE population == (SELECT MIN(population)
FROM facts);
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
250 | ay | Antarctica | None | 280000 | None | 0 | None | None | None | None |
From the analysis Artarctica has a population of zero which makes sense because the place has no permanent inhabitants
%%sql
SELECT *
FROM facts
WHERE population == (SELECT MAX(population)
FROM facts);
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
261 | xx | World | None | None | None | 7256490011 | 1.08 | 18.6 | 7.8 | None |
The fact book lists the world as a place so there is need for us to exclude it if we are to obtain accurate insights from the data
%%sql
SELECT MIN(population),
MAX(population),
MIN(population_growth),
MAX(population_growth)
FROM facts
WHERE name <> "World";
Done.
MIN(population) | MAX(population) | MIN(population_growth) | MAX(population_growth) |
---|---|---|---|
0 | 1367485388 | 0.0 | 4.02 |
This is understandable because in the world there are two countries with a popultion above 1 billion people
%%sql
SELECT AVG(population), AVG(area)
FROM facts
WHERE name <> "World";
Done.
AVG(population) | AVG(area) |
---|---|
32242666.56846473 | 555093.546184739 |
The average population in countries is 32 million people and the average sise of countries is 555 sq km
%%sql
SELECT name AS Country, area, population
FROM facts
WHERE population >(SELECT AVG(population)
FROM facts
WHERE name <> "World")
AND area < (SELECT AVG(area)
FROM facts);
Done.
Country | area | population |
---|---|---|
Bangladesh | 148460 | 168957745 |
Germany | 357022 | 80854408 |
Iraq | 438317 | 37056169 |
Italy | 301340 | 61855120 |
Japan | 377915 | 126919659 |
Korea, South | 99720 | 49115196 |
Morocco | 446550 | 33322699 |
Philippines | 300000 | 100998376 |
Poland | 312685 | 38562189 |
Spain | 505370 | 48146134 |
Thailand | 513120 | 67976405 |
Uganda | 241038 | 37101745 |
United Kingdom | 243610 | 64088222 |
Vietnam | 331210 | 94348835 |
From the table, Bangladesh is the most densely populated areas in the whole world. Most of these nations are from Africa and Asia
%%sql
SELECT name AS Country, population_growth
FROM facts
WHERE population_growth >(SELECT AVG(population_growth)
FROM facts
WHERE name <> "World")
ORDER BY population_growth DESC
LIMIT 20;
Done.
Country | 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 |
Ethiopia | 2.89 |
Zambia | 2.88 |
Western Sahara | 2.82 |
Gaza Strip | 2.81 |
Tanzania | 2.79 |
Angola | 2.78 |
Benin | 2.78 |
Togo | 2.69 |
Guinea | 2.63 |
Cameroon | 2.59 |
African countries have the highest population growth
%%sql
SELECT name AS Country, population_growth, birth_rate
FROM facts
WHERE population_growth >(SELECT AVG(population_growth)
FROM facts
WHERE name <> "World")
AND birth_rate > (SELECT AVG(birth_rate)
FROM facts
WHERE name <> "World")
ORDER BY birth_rate DESC
LIMIT 20;
Done.
Country | population_growth | birth_rate |
---|---|---|
Niger | 3.25 | 45.45 |
Mali | 2.98 | 44.99 |
Uganda | 3.24 | 43.79 |
Zambia | 2.88 | 42.13 |
Burkina Faso | 3.03 | 42.03 |
Burundi | 3.28 | 42.01 |
Malawi | 3.32 | 41.56 |
Somalia | 1.83 | 40.45 |
Angola | 2.78 | 38.78 |
Mozambique | 2.45 | 38.58 |
Afghanistan | 2.32 | 38.57 |
Nigeria | 2.45 | 37.64 |
Ethiopia | 2.89 | 37.27 |
Sierra Leone | 2.35 | 37.03 |
South Sudan | 4.02 | 36.91 |
Chad | 1.89 | 36.6 |
Tanzania | 2.79 | 36.39 |
Cameroon | 2.59 | 36.17 |
Benin | 2.78 | 36.02 |
Congo, Republic of the | 2.0 | 35.85 |
There is change in the list if we order it by thr birth rate but African countries dominate population growth rates by birth. Somalia and Chad have high birth rates but low population growth.
%%sql
SELECT *
FROM facts
WHERE area > (SELECT MAX(area_land)
FROM facts);
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
143 | rs | Russia | 17098242 | 16377742 | 720500 | 142423773 | 0.04 | 11.6 | 13.69 | 1.69 |
%%sql
SELECT name AS Country, population_growth, birth_rate, death_rate
FROM facts
WHERE death_rate > birth_rate;
Done.
Country | population_growth | birth_rate | death_rate |
---|---|---|---|
Austria | 0.55 | 9.41 | 9.42 |
Belarus | 0.2 | 10.7 | 13.36 |
Bosnia and Herzegovina | 0.13 | 8.87 | 9.75 |
Bulgaria | 0.58 | 8.92 | 14.44 |
Croatia | 0.13 | 9.45 | 12.18 |
Czech Republic | 0.16 | 9.63 | 10.34 |
Estonia | 0.55 | 10.51 | 12.4 |
Germany | 0.17 | 8.47 | 11.42 |
Greece | 0.01 | 8.66 | 11.09 |
Hungary | 0.22 | 9.16 | 12.73 |
Italy | 0.27 | 8.74 | 10.19 |
Japan | 0.16 | 7.93 | 9.51 |
Latvia | 1.06 | 10.0 | 14.31 |
Lithuania | 1.04 | 10.1 | 14.27 |
Moldova | 1.03 | 12.0 | 12.59 |
Monaco | 0.12 | 6.65 | 9.24 |
Poland | 0.09 | 9.74 | 10.19 |
Portugal | 0.09 | 9.27 | 11.02 |
Romania | 0.3 | 9.14 | 11.9 |
Russia | 0.04 | 11.6 | 13.69 |
Serbia | 0.46 | 9.08 | 13.66 |
Slovenia | 0.26 | 8.42 | 11.37 |
Ukraine | 0.6 | 10.72 | 14.46 |
Saint Pierre and Miquelon | 1.08 | 7.42 | 9.72 |
All the countries in this list are European Zone
%%sql
SELECT name AS Country, ROUND((population/area), 3) AS population_area_ratio, birth_rate, death_rate
FROM facts
WHERE population_area_ratio > (SELECT ROUND((population/area), 3)
FROM facts
WHERE name <> "World")
ORDER BY population_area_ratio DESC
LIMIT 10;
Done.
Country | population_area_ratio | birth_rate | death_rate |
---|---|---|---|
Macau | 21168.0 | 8.88 | 4.22 |
Monaco | 15267.0 | 6.65 | 9.24 |
Singapore | 8141.0 | 8.27 | 3.43 |
Hong Kong | 6445.0 | 9.23 | 7.07 |
Gaza Strip | 5191.0 | 31.11 | 3.04 |
Gibraltar | 4876.0 | 14.08 | 8.37 |
Bahrain | 1771.0 | 13.66 | 2.69 |
Maldives | 1319.0 | 15.75 | 3.89 |
Malta | 1310.0 | 10.18 | 9.09 |
Bermuda | 1299.0 | 11.33 | 8.23 |
These nations are from different continents