The data from the CIA World Factbook, a compendium of statistics about all of the countries on Earth. The Factbook contains demographic information like:
- population - The population as of 2015.
- population_growth - The annual population growth rate, as a
percentage.
- area - The total land and water area.
To download the SQLite database - factbook.db
%%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 |
Column Name | Description |
---|---|
name | The name of the country |
area | The total land and sea area of the country. |
population | The country's population. |
population_growth | The country's population growth as a percentage. |
birth_rate | The country's birth rate, or the number of births a year per 1,000 people. |
death_rate | The country's death rate, or the number of death a year per 1,000 people. |
area | The country's total area (both land and water). |
area_land | The country's land area in square kilometers. |
area_water | The country's waterarea in square kilometers. |
%%sql
SELECT
MIN(population) AS min_pop,
MAX(population) AS max_pop,
MIN(population_growth) AS min_pop_grwth,
MAX(population_growth) AS max_pop_grwth
FROM facts;
Done.
min_pop | max_pop | min_pop_grwth | max_pop_grwth |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
The result we got from our above query is really astounding, we have a country with zero population and a country with more than 7.2 billion people. Also we have astounding population growth rate. Can we associate the same with the respective country with population 0 and more than 7.2 billion population?
Let us find out these two countries. And the countries with 0.0 and 4.02 population growth rate.
%%sql
SELECT
name AS country,
population AS pop,
population_growth AS pop_grwth
FROM facts
WHERE pop == (SELECT MIN(population) FROM facts);
Done.
country | pop | pop_grwth |
---|---|---|
Antarctica | 0 | None |
There is only one country with least population (population of 0 or no population at all): Antarctica
%%sql
SELECT
name AS country,
population AS pop,
population_growth AS pop_grwth
FROM facts
WHERE pop == (SELECT MAX(population) FROM facts);
Done.
country | pop | pop_grwth |
---|---|---|
World | 7256490011 | 1.08 |
There is only one country with maximum population (population of over 7.2 billion): World
Taking into consideration that here the World as a whole is counted to be one big country itself on our Earth.
%%sql
SELECT
name AS country,
population AS pop,
population_growth AS pop_grwth
FROM facts
WHERE pop_grwth == (SELECT MIN(population_growth) FROM facts);
Done.
country | pop | pop_grwth |
---|---|---|
Holy See (Vatican City) | 842 | 0.0 |
Cocos (Keeling) Islands | 596 | 0.0 |
Greenland | 57733 | 0.0 |
Pitcairn Islands | 48 | 0.0 |
%%sql
SELECT
name AS country,
population AS pop,
population_growth AS pop_grwth
FROM facts
WHERE pop_grwth == (SELECT MAX(population_growth) FROM facts);
Done.
country | pop | pop_grwth |
---|---|---|
South Sudan | 12042910 | 4.02 |
%%sql
SELECT
DISTINCT population_growth AS pop_grwth
FROM facts
ORDER BY pop_grwth DESC;
Done.
pop_grwth |
---|
4.02 |
3.32 |
3.28 |
3.25 |
3.24 |
3.07 |
3.03 |
2.98 |
2.95 |
2.93 |
2.89 |
2.88 |
2.82 |
2.81 |
2.79 |
2.78 |
2.69 |
2.63 |
2.59 |
2.58 |
2.56 |
2.51 |
2.47 |
2.45 |
2.42 |
2.41 |
2.35 |
2.32 |
2.3 |
2.25 |
2.23 |
2.21 |
2.2 |
2.18 |
2.16 |
2.13 |
2.1 |
2.07 |
2.03 |
2.02 |
2.0 |
1.95 |
1.93 |
1.91 |
1.89 |
1.87 |
1.84 |
1.83 |
1.82 |
1.79 |
1.78 |
1.77 |
1.72 |
1.71 |
1.68 |
1.66 |
1.62 |
1.61 |
1.6 |
1.58 |
1.56 |
1.55 |
1.51 |
1.46 |
1.44 |
1.43 |
1.39 |
1.38 |
1.36 |
1.35 |
1.33 |
1.32 |
1.31 |
1.26 |
1.25 |
1.24 |
1.23 |
1.22 |
1.21 |
1.2 |
1.18 |
1.17 |
1.16 |
1.15 |
1.14 |
1.13 |
1.11 |
1.08 |
1.07 |
1.06 |
1.04 |
1.03 |
1.01 |
1.0 |
0.97 |
0.96 |
0.94 |
0.93 |
0.92 |
0.89 |
0.86 |
0.85 |
0.84 |
0.83 |
0.82 |
0.8 |
0.78 |
0.77 |
0.76 |
0.75 |
0.71 |
0.68 |
0.67 |
0.64 |
0.6 |
0.59 |
0.58 |
0.55 |
0.54 |
0.53 |
0.51 |
0.5 |
0.48 |
0.46 |
0.45 |
0.43 |
0.42 |
0.41 |
0.4 |
0.38 |
0.34 |
0.33 |
0.32 |
0.31 |
0.3 |
0.28 |
0.27 |
0.26 |
0.25 |
0.24 |
0.23 |
0.22 |
0.21 |
0.2 |
0.17 |
0.16 |
0.15 |
0.14 |
0.13 |
0.12 |
0.09 |
0.08 |
0.04 |
0.03 |
0.02 |
0.01 |
0.0 |
None |
Thus from the results of above SQL queries we conclude that:
- the table contains a row for Antarctica, which explains having a value 0 in the population column.
- the table contains a row for the whole world, which explains having a value of over 7.2 billion in the population column.
This seems to match the CIA Factbook page for Antarctica
We also found that Antarctica population growth is None, but obvious there is no population, and World population growth is 1.08. The minimum and maximum values in population_growth columns are not associated with these two countries as we had assumed previously.
- the table contains 4 countries which do have some population
but numbers shows zero population growth in these countries.
- our query above reveals that South Sudan is the country which has maximum population growth rate of 4.02.
%%sql
SELECT
AVG(population) AS pop_avg,
AVG(area) AS area_avg
FROM facts;
Done.
pop_avg | area_avg |
---|---|
62094928.32231405 | 555093.546184739 |
The result of above query reveals that the average population is far more than the average area. Does this means overpopulated?
Let us find the countries which are overpopulated / densely populated.
%%sql
SELECT
name AS countries,
population AS pop,
area AS area
FROM facts
WHERE population > (SELECT AVG(population) FROM facts) And
area < (SELECT AVG(area) FROM facts);
Done.
countries | pop | area |
---|---|---|
Bangladesh | 168957745 | 148460 |
Germany | 80854408 | 357022 |
Japan | 126919659 | 377915 |
Philippines | 100998376 | 300000 |
Thailand | 67976405 | 513120 |
United Kingdom | 64088222 | 243610 |
Vietnam | 94348835 | 331210 |
Above query outputs the name of the countries in the table having dense population.
To find countries that are densely populated we need to identify the countries that have:
- Above average values for population
and
- Below average values for area.
The result of our above queries shows that as per 2015 records there are seven countries which are overpopulated / densely populated. And South Sudan is having the highest population growth rate.
%%sql
SELECT
name AS countries,
ROUND(CAST(area_water AS Float)/CAST(area_land AS Float),2) AS ratios
FROM facts
ORDER BY ratios DESC
LIMIT 5;
Done.
countries | ratios |
---|---|
British Indian Ocean Territory | 905.67 |
Virgin Islands | 4.52 |
Puerto Rico | 0.55 |
Bahamas, The | 0.39 |
Guinea-Bissau | 0.28 |
The above query reveals that the country British Indian Ocean Territory has highest ratio of 905.67 of water to land followed by Virgin Islands with 4.52
%%sql
SELECT
name AS countries,
area_water,
area_land
FROM facts
WHERE area_water > area_land;
Done.
countries | area_water | area_land |
---|---|---|
British Indian Ocean Territory | 54340 | 60 |
Virgin Islands | 1564 | 346 |
The above query reveals more details to explain ratios 905.67 and 4.52 of water to land for the countries British Indian Ocean Territory and Virgin Islands respectively.
%%sql
SELECT
name AS countries,
population_growth AS pop_grwth
FROM facts
ORDER BY pop_grwth
DESC LIMIT 10;
Done.
countries | pop_grwth |
---|---|
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 |
Through above query we collected minimum 10 countries with their respective population growth rate, and found South Sudan is leading with highest population growth rate of 4.02.
As mentioned this is for 2016 as per 2015 record. However there were some drastic changes seen from 2018 to 2019 in the population growth rate, and hence based on population growth rate records in our table as of 2015, we won't be able to find the countries who have and will add most people in present year 2020.
%%sql
SELECT
name AS countries,
death_rate,
birth_rate,
ROUND((death_rate - birth_rate),2) AS diff
FROM facts
WHERE death_rate > birth_rate
ORDER BY diff DESC;
Done.
countries | death_rate | birth_rate | diff |
---|---|---|---|
Bulgaria | 14.44 | 8.92 | 5.52 |
Serbia | 13.66 | 9.08 | 4.58 |
Latvia | 14.31 | 10.0 | 4.31 |
Lithuania | 14.27 | 10.1 | 4.17 |
Ukraine | 14.46 | 10.72 | 3.74 |
Hungary | 12.73 | 9.16 | 3.57 |
Germany | 11.42 | 8.47 | 2.95 |
Slovenia | 11.37 | 8.42 | 2.95 |
Romania | 11.9 | 9.14 | 2.76 |
Croatia | 12.18 | 9.45 | 2.73 |
Belarus | 13.36 | 10.7 | 2.66 |
Monaco | 9.24 | 6.65 | 2.59 |
Greece | 11.09 | 8.66 | 2.43 |
Saint Pierre and Miquelon | 9.72 | 7.42 | 2.3 |
Russia | 13.69 | 11.6 | 2.09 |
Estonia | 12.4 | 10.51 | 1.89 |
Portugal | 11.02 | 9.27 | 1.75 |
Japan | 9.51 | 7.93 | 1.58 |
Italy | 10.19 | 8.74 | 1.45 |
Bosnia and Herzegovina | 9.75 | 8.87 | 0.88 |
Czech Republic | 10.34 | 9.63 | 0.71 |
Moldova | 12.59 | 12.0 | 0.59 |
Poland | 10.19 | 9.74 | 0.45 |
Austria | 9.42 | 9.41 | 0.01 |
The result of our query above shows that Bulgaria is the country which has higher death rate than birth rate.