** In this project I will use SQL to explore data from the CIA Factbook.**
The CIA factbook contains the following information about all of the countries in the world:
The questions that I will answer are:
Firstly we need to connect to the database and find the name of the file that we will be using.
%%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) |
So the name of the file is 'facts'.
Let's check out the first 5 rows of 'facts' to get an idea of what we are working with.
%%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 |
'Facts' is ordered by the name column which has each country in alphabetical order. The rest of the columns then give us information about each country. Now let's start answering some of our questions.
** What are the highest and lowest populations and also what are the highest and lowest population growths?**
%%sql
SELECT MAX(population), MIN(population), MAX(population_growth), MIN(population_growth)
FROM facts
Done.
MAX(population) | MIN(population) | MAX(population_growth) | MIN(population_growth) |
---|---|---|---|
7256490011 | 0 | 4.02 | 0.0 |
Some interesting information here. Apparantly the highest population is above 7 billion which is far too much and the lowest population is 0!
** Let's dig deeper to find which countries have given us this data.**
%%sql
SELECT name, Population
FROM facts
WHERE Population = (SELECT Max(population) FROM facts)
OR Population = (SELECT Min(population) FROM facts)
Done.
name | population |
---|---|
Antarctica | 0 |
World | 7256490011 |
Hmmm well Antarctica and the world aren't countries at all! Let's get rid of them and try again.
%%sql
SELECT name, population
FROM facts
WHERE Population = (SELECT Max(population) FROM facts WHERE name != 'World')
OR Population = (SELECT Min (population) FROM facts WHERE name != 'Antarctica')
Done.
name | population |
---|---|
China | 1367485388 |
Pitcairn Islands | 48 |
%%sql
SELECT name, population_growth
FROM facts
WHERE population_growth = (SELECT MAX(population_growth) FROM facts)
OR population_growth = (SELECT MIN(population_growth) FROM facts)
Done.
name | population_growth |
---|---|
South Sudan | 4.02 |
Holy See (Vatican City) | 0.0 |
Cocos (Keeling) Islands | 0.0 |
Greenland | 0.0 |
Pitcairn Islands | 0.0 |
There we go. Now we can see that China has the highest population at 1.3 billion and the Pitcairn Islands have the lowest population with just just 48.
For the population growth, the fastest growing country is the South Sudan which is growing by 4% each year. The Vatican, The Cocos Islands, Greenland and The Pitcairn islands have the lowest population growth as they are all not growing at all.
** Next Question. What is the average population and area of each country?**
%%sql
SELECT round(AVG(population), 2) as 'Average Population', round(AVG(area), 2) as 'Average Area'
FROM facts
Done.
Average Population | Average Area |
---|---|
62094928.32 | 555093.55 |
The average population is just over 62 million and the average area is 555,093. Which brings us on to the next question:
Which countries have above average population but lower than average area size?
%%sql
SELECT name, population, area
FROM facts
WHERE population > (SELECT AVG(Population) FROM facts)
AND area < (SELECT AVG(area) FROM facts)
Done.
name | population | area |
---|---|---|
Bangladesh | 168957745 | 148460 |
Germany | 80854408 | 357022 |
Japan | 126919659 | 377915 |
Philippines | 100998376 | 300000 |
Thailand | 67976405 | 513120 |
United Kingdom | 64088222 | 243610 |
Vietnam | 94348835 | 331210 |
These 7 countries have a large population but not much land for all of the people!
Which countries have the highest ratios of water to land? Which countries have more water than land?
%%sql
SELECT name, round(CAST(area_water as FLOAT) / area_land, 2) as ratio
FROM facts
ORDER BY ratio DESC
LIMIT 10
Done.
name | ratio |
---|---|
British Indian Ocean Territory | 905.67 |
Virgin Islands | 4.52 |
Puerto Rico | 0.55 |
Bahamas, The | 0.39 |
Guinea-Bissau | 0.28 |
Malawi | 0.26 |
Netherlands | 0.23 |
Uganda | 0.22 |
Eritrea | 0.16 |
Liberia | 0.16 |
By far the country with the highest ratio of water to land is The British Indian Ocean Territory. It's area is comprised of 900 times more water than land. The only other country with more water than land are The Virgin Islands.
Which countries will add the most people to their populations next year?
%%sql
SELECT name, population, population_growth, round(CAST(population as FLOAT) / 100 * population_growth, 2) as People_added
FROM facts
WHERE name != 'World'
ORDER BY People_added DESC
LIMIT 10
Done.
name | population | population_growth | People_added |
---|---|---|---|
India | 1251695584 | 1.22 | 15270686.12 |
China | 1367485388 | 0.45 | 6153684.25 |
Nigeria | 181562056 | 2.45 | 4448270.37 |
Pakistan | 199085847 | 1.46 | 2906653.37 |
Ethiopia | 99465819 | 2.89 | 2874562.17 |
Bangladesh | 168957745 | 1.6 | 2703323.92 |
United States | 321368864 | 0.78 | 2506677.14 |
Indonesia | 255993674 | 0.92 | 2355141.8 |
Congo, Democratic Republic of the | 79375136 | 2.45 | 1944690.83 |
Philippines | 100998376 | 1.61 | 1626073.85 |
India will add the most people to their population next year with an extra 15 million people!
Which countries have a higher death rate than birth rate?
%%sql
SELECT name, death_rate, birth_rate, round(CAST(death_rate as FLOAT) - birth_rate, 2) as 'ratio'
FROM facts
WHERE ratio > 0
ORDER BY ratio DESC
Done.
name | death_rate | birth_rate | ratio |
---|---|---|---|
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 |
All of these countries have a higher death rate than birth rate. Bulgaria is the country with the biggest gap between the death rate and birth rate.
Which countries have the highest population/area ratio?
%%sql
SELECT name, population, area, round(CAST(population as FLOAT) / area, 2) as ratio
FROM facts
WHERE ratio >= 0
ORDER BY ratio DESC
LIMIT 100
Done.
name | population | area | ratio |
---|---|---|---|
Macau | 592731 | 28 | 21168.96 |
Monaco | 30535 | 2 | 15267.5 |
Singapore | 5674472 | 697 | 8141.28 |
Hong Kong | 7141106 | 1108 | 6445.04 |
Gaza Strip | 1869055 | 360 | 5191.82 |
Gibraltar | 29258 | 6 | 4876.33 |
Bahrain | 1346613 | 760 | 1771.86 |
Maldives | 393253 | 298 | 1319.64 |
Malta | 413965 | 316 | 1310.02 |
Bermuda | 70196 | 54 | 1299.93 |
Sint Maarten | 39689 | 34 | 1167.32 |
Bangladesh | 168957745 | 148460 | 1138.07 |
Guernsey | 66080 | 78 | 847.18 |
Jersey | 97294 | 116 | 838.74 |
Barbados | 290604 | 430 | 675.82 |
Mauritius | 1339827 | 2040 | 656.78 |
Taiwan | 23415126 | 35980 | 650.78 |
Aruba | 112162 | 180 | 623.12 |
Lebanon | 6184701 | 10400 | 594.68 |
Saint Martin | 31754 | 54 | 588.04 |
San Marino | 33020 | 61 | 541.31 |
Korea, South | 49115196 | 99720 | 492.53 |
Rwanda | 12661733 | 26338 | 480.74 |
West Bank | 2785366 | 5860 | 475.32 |
Nauru | 9540 | 21 | 454.29 |
Tuvalu | 10869 | 26 | 418.04 |
Netherlands | 16947904 | 41543 | 407.96 |
Marshall Islands | 72191 | 181 | 398.85 |
Israel | 8049314 | 20770 | 387.55 |
Burundi | 10742276 | 27830 | 386.0 |
India | 1251695584 | 3287263 | 380.77 |
Belgium | 11323973 | 30528 | 370.94 |
Haiti | 10110019 | 27750 | 364.33 |
Comoros | 780971 | 2235 | 349.43 |
Philippines | 100998376 | 300000 | 336.66 |
Sri Lanka | 22053488 | 65610 | 336.13 |
Japan | 126919659 | 377915 | 335.84 |
Curacao | 148406 | 444 | 334.25 |
Grenada | 110694 | 344 | 321.78 |
Guam | 161785 | 544 | 297.4 |
El Salvador | 6141350 | 21041 | 291.88 |
Vietnam | 94348835 | 331210 | 284.86 |
American Samoa | 54343 | 199 | 273.08 |
Jamaica | 2950210 | 10991 | 268.42 |
Saint Lucia | 163922 | 616 | 266.11 |
Saint Vincent and the Grenadines | 102627 | 389 | 263.82 |
United Kingdom | 64088222 | 243610 | 263.08 |
Puerto Rico | 3598357 | 13791 | 260.92 |
Pakistan | 199085847 | 796095 | 250.08 |
Trinidad and Tobago | 1222363 | 5128 | 238.37 |
Liechtenstein | 37624 | 160 | 235.15 |
Germany | 80854408 | 357022 | 226.47 |
British Virgin Islands | 33454 | 151 | 221.55 |
Luxembourg | 570252 | 2586 | 220.52 |
Dominican Republic | 10478756 | 48670 | 215.3 |
Nepal | 31551305 | 147181 | 214.37 |
Cayman Islands | 56092 | 264 | 212.47 |
Antigua and Barbuda | 92436 | 442 | 209.13 |
Korea, North | 24983205 | 120538 | 207.26 |
Italy | 61855120 | 301340 | 205.27 |
Seychelles | 92430 | 455 | 203.14 |
Sao Tome and Principe | 194006 | 964 | 201.25 |
Saint Kitts and Nevis | 51936 | 261 | 198.99 |
Switzerland | 8121830 | 41277 | 196.76 |
Nigeria | 181562056 | 923768 | 196.55 |
Qatar | 2194817 | 11586 | 189.44 |
Andorra | 85580 | 468 | 182.86 |
Anguilla | 16418 | 91 | 180.42 |
Gambia, The | 1967709 | 11300 | 174.13 |
Kosovo | 1870981 | 10887 | 171.85 |
Kuwait | 2788534 | 17818 | 156.5 |
Uganda | 37101745 | 241038 | 153.92 |
Isle of Man | 87545 | 572 | 153.05 |
Malawi | 17964697 | 118484 | 151.62 |
Micronesia, Federated States of | 105216 | 702 | 149.88 |
Tonga | 106501 | 747 | 142.57 |
China | 1367485388 | 9596960 | 142.49 |
Guatemala | 14918999 | 108889 | 137.01 |
Cabo Verde | 545993 | 4033 | 135.38 |
Czech Republic | 10644842 | 78867 | 134.97 |
Indonesia | 255993674 | 1904569 | 134.41 |
Togo | 7552318 | 56785 | 133.0 |
Thailand | 67976405 | 513120 | 132.48 |
Kiribati | 105711 | 811 | 130.35 |
Denmark | 5581503 | 43094 | 129.52 |
Cyprus | 1189197 | 9251 | 128.55 |
Akrotiri | 15700 | 123 | 127.64 |
Poland | 38562189 | 312685 | 123.33 |
Dhekelia | 15700 | 130 | 120.77 |
European Union | 513949445 | 4324782 | 118.84 |
Portugal | 10825309 | 92090 | 117.55 |
Azerbaijan | 9780780 | 86600 | 112.94 |
Northern Mariana Islands | 52344 | 464 | 112.81 |
Tokelau | 1337 | 12 | 111.42 |
Slovakia | 5445027 | 49035 | 111.04 |
Ghana | 26327649 | 238533 | 110.37 |
Wallis and Futuna | 15613 | 142 | 109.95 |
Hungary | 9897541 | 93028 | 106.39 |
Albania | 3029278 | 28748 | 105.37 |
Moldova | 3546847 | 33851 | 104.78 |
These countries show which populations are crammed into the smallest space. As city nations it is unsuprising that Macau, Monaco, Singapore and Hong Kong sit at the top of the list.
Thanks for reading. I hope you enjoyed this quick look through population and country size.