In this project, I'll work with data from the CIA World Factbook, a compendium of statistics about all of the countries on Earth. The Factbook contains demographic information like the following:
population
— the global population.population_growth
— the annual population growth rate, as a percentage.area
— the total land and water area.In this project, I'll use SQL in Jupyter Notebook to analyze data from this database. If you want to work on this project locally, you can download the SQLite factbook.db database here
The default Anaconda installation includes Jupyter and several other useful tools.
I'll also need ipython-sql which I'll install by running the following code:
!conda install -yc conda-forge ipython-sql
Collecting package metadata (current_repodata.json): ...working... done Solving environment: ...working... done # All requested packages already installed.
I'll use the following code to connect our Jupyter Notebook to our database file:
NB: The database file should be in the same folder as the notebook file
%%capture
%load_ext sql
%sql sqlite:///factbook.db
Just to ensure that my connection was correct, I'll write a query to return all tables in the database
%%sql
SELECT *
FROM sqlite_master
WHERE type='table';
* sqlite:///factbook.db 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) |
Now lets explore the contents of the facts table as this is the table which contains the data I want to analyse
%%sql
SELECT *
FROM facts
LIMIT 5;
* sqlite:///factbook.db 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 |
From this result, I can see teh different columns in the dataset and can probably even deduce what they correspond to. But for clarity, I'll describe each column and what they correspond to.
id
- Serial incremental identifier for tablecode
-Country codename
- Country namearea
- Total land and water area in square kilometersarea_land
- Total land area in square kilometersarea_water
- Total water area in square kilometerspopulation
- Total population of countrypopulation_growth
- Annual population growth as a percentagebirth_rate
- Annual birth rate, or the number of births per year per 1,000 people.death_rate
- Annual death rate, or the number of deaths per year per 1,000 people.migration_rate
- - Annual migration rateNow, I'll write a query to determine the minimum and maximum population and also the fastest and slowest growing population
%%sql
SELECT MIN(population) AS 'Minimum Population',
MAX(population) AS 'Maximum Population',
MIN(population_growth) AS 'Minimum Population Growth',
MAX(population_growth) AS 'Maximum Population Growth'
FROM facts
* sqlite:///factbook.db Done.
Minimum Population | Maximum Population | Minimum Population Growth | Maximum Population Growth |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
Oh wow, who would've thought there was a country with Zero population right? And another country with over 7 billion people yeah? Hmmmmmmmmmm, Is that not like the population of the whole world???
You know what, let's see which countries these are.
%%sql
SELECT name, population
FROM facts
WHERE population in (SELECT MIN(population) FROM facts)
* sqlite:///factbook.db Done.
name | population |
---|---|
Antarctica | 0 |
Oh, It obviously had to be Antarctica. Now let's see the country with the highest population
%%sql
SELECT name, population
FROM facts
WHERE population in (SELECT MAX(population) FROM facts)
* sqlite:///factbook.db Done.
name | population |
---|---|
World | 7256490011 |
Figures. World is indicated in my dataset as a country.
I'm going to recalculate max and min populations again but this time, I'll exempt the row that contains World. ANd yes, I'll have to exempt world from all my queries from now on
%%sql
SELECT MIN(population) AS 'Minimum Population',
MAX(population) AS 'Maximum Population',
MIN(population_growth) AS 'Minimum Population Growth',
MAX(population_growth) AS 'Maximum Population Growth'
FROM facts
WHERE name NOT IN ('World')
* sqlite:///factbook.db Done.
Minimum Population | Maximum Population | Minimum Population Growth | Maximum Population Growth |
---|---|---|---|
0 | 1367485388 | 0.0 | 4.02 |
Okay, I think its fairer now. 1.3 billion is the highest. I'm guessing that should be China or India. Will probably confirm that later.
For now, I want to calculate the average population of all the countries and also average total surface area
%%sql
SELECT ROUND(AVG(population), 2) AS 'Average Population',
ROUND(AVG(area), 3) AS 'Average Area'
FROM facts
WHERE name NOT IN ('World')
* sqlite:///factbook.db Done.
Average Population | Average Area |
---|---|
32242666.57 | 555093.546 |
Great. Okay, I'm actually curious.
Let me just confirm the most populous country in the world before moving on, lol.
%%sql
SELECT name, population
FROM facts
WHERE population in (SELECT MAX(population) FROM facts WHERE name NOT IN ('World'))
* sqlite:///factbook.db Done.
name | population |
---|---|
China | 1367485388 |
I knew it. China. It just had to be. No wonder they are the world manufacturing power.
Okay, curiosity satisfied
Who else noticed that the average population is quite small. Just over 32 milion. I mean, considering the fact that we have the likes of China in this list, tehre has to be an awful lot of countries with really low population to hget this kind of average right. Cos, I mean Nigeria my country even has over 200 million people.
The average area also looks kinda small I think. That's like half of Nigeria's or 60% give or take. I'm really curious to know which countries are this small.
You know what, lets find the countries that have above average values for population and below average values for area
%%sql
SELECT name, population
FROM facts
WHERE population > (SELECT ROUND(AVG(population), 2) FROM facts WHERE name NOT IN ('World'))
AND name NOT IN ('World')
* sqlite:///factbook.db Done.
name | population |
---|---|
Afghanistan | 32564342 |
Algeria | 39542166 |
Argentina | 43431886 |
Bangladesh | 168957745 |
Brazil | 204259812 |
Burma | 56320206 |
Canada | 35099836 |
China | 1367485388 |
Colombia | 46736728 |
Congo, Democratic Republic of the | 79375136 |
Egypt | 88487396 |
Ethiopia | 99465819 |
France | 66553766 |
Germany | 80854408 |
India | 1251695584 |
Indonesia | 255993674 |
Iran | 81824270 |
Iraq | 37056169 |
Italy | 61855120 |
Japan | 126919659 |
Kenya | 45925301 |
Korea, South | 49115196 |
Mexico | 121736809 |
Morocco | 33322699 |
Nigeria | 181562056 |
Pakistan | 199085847 |
Philippines | 100998376 |
Poland | 38562189 |
Russia | 142423773 |
South Africa | 53675563 |
Spain | 48146134 |
Sudan | 36108853 |
Tanzania | 51045882 |
Thailand | 67976405 |
Turkey | 79414269 |
Uganda | 37101745 |
Ukraine | 44429471 |
United Kingdom | 64088222 |
United States | 321368864 |
Vietnam | 94348835 |
European Union | 513949445 |
Oh wow, they are actually just a handful
%%sql
SELECT name, population
FROM facts
WHERE area < (SELECT ROUND(AVG(area), 3) FROM facts WHERE name NOT IN ('World'))
AND name NOT IN ('World')
* sqlite:///factbook.db Done.
name | population |
---|---|
Albania | 3029278 |
Andorra | 85580 |
Antigua and Barbuda | 92436 |
Armenia | 3056382 |
Austria | 8665550 |
Azerbaijan | 9780780 |
Bahamas, The | 324597 |
Bahrain | 1346613 |
Bangladesh | 168957745 |
Barbados | 290604 |
Belarus | 9589689 |
Belgium | 11323973 |
Belize | 347369 |
Benin | 10448647 |
Bhutan | 741919 |
Bosnia and Herzegovina | 3867055 |
Brunei | 429646 |
Bulgaria | 7186893 |
Burkina Faso | 18931686 |
Burundi | 10742276 |
Cambodia | 15708756 |
Cameroon | 23739218 |
Cabo Verde | 545993 |
Comoros | 780971 |
Congo, Republic of the | 4755097 |
Costa Rica | 4814144 |
Cote d'Ivoire | 23295302 |
Croatia | 4464844 |
Cuba | 11031433 |
Cyprus | 1189197 |
Czech Republic | 10644842 |
Denmark | 5581503 |
Djibouti | 828324 |
Dominica | 73607 |
Dominican Republic | 10478756 |
Ecuador | 15868396 |
El Salvador | 6141350 |
Equatorial Guinea | 740743 |
Eritrea | 6527689 |
Estonia | 1265420 |
Fiji | 909389 |
Finland | 5476922 |
Gabon | 1705336 |
Gambia, The | 1967709 |
Georgia | 4931226 |
Germany | 80854408 |
Ghana | 26327649 |
Greece | 10775643 |
Grenada | 110694 |
Guatemala | 14918999 |
Guinea | 11780162 |
Guinea-Bissau | 1726170 |
Guyana | 735222 |
Haiti | 10110019 |
Honduras | 8746673 |
Hungary | 9897541 |
Iceland | 331918 |
Iraq | 37056169 |
Ireland | 4892305 |
Israel | 8049314 |
Italy | 61855120 |
Jamaica | 2950210 |
Japan | 126919659 |
Jordan | 8117564 |
Kiribati | 105711 |
Korea, North | 24983205 |
Korea, South | 49115196 |
Kosovo | 1870981 |
Kuwait | 2788534 |
Kyrgyzstan | 5664939 |
Laos | 6911544 |
Latvia | 1986705 |
Lebanon | 6184701 |
Lesotho | 1947701 |
Liberia | 4195666 |
Liechtenstein | 37624 |
Lithuania | 2884433 |
Luxembourg | 570252 |
Macedonia | 2096015 |
Malawi | 17964697 |
Malaysia | 30513848 |
Maldives | 393253 |
Malta | 413965 |
Marshall Islands | 72191 |
Mauritius | 1339827 |
Micronesia, Federated States of | 105216 |
Moldova | 3546847 |
Monaco | 30535 |
Montenegro | 647073 |
Morocco | 33322699 |
Nauru | 9540 |
Nepal | 31551305 |
Netherlands | 16947904 |
New Zealand | 4438393 |
Nicaragua | 5907881 |
Norway | 5207689 |
Oman | 3286936 |
Palau | 21265 |
Panama | 3657024 |
Papua New Guinea | 6672429 |
Paraguay | 6783272 |
Philippines | 100998376 |
Poland | 38562189 |
Portugal | 10825309 |
Qatar | 2194817 |
Romania | 21666350 |
Rwanda | 12661733 |
Saint Kitts and Nevis | 51936 |
Saint Lucia | 163922 |
Saint Vincent and the Grenadines | 102627 |
Samoa | 197773 |
San Marino | 33020 |
Sao Tome and Principe | 194006 |
Senegal | 13975834 |
Serbia | 7176794 |
Seychelles | 92430 |
Sierra Leone | 5879098 |
Singapore | 5674472 |
Slovakia | 5445027 |
Slovenia | 1983412 |
Solomon Islands | 622469 |
Spain | 48146134 |
Sri Lanka | 22053488 |
Suriname | 579633 |
Swaziland | 1435613 |
Sweden | 9801616 |
Switzerland | 8121830 |
Syria | 17064854 |
Tajikistan | 8191958 |
Thailand | 67976405 |
Timor-Leste | 1231116 |
Togo | 7552318 |
Tonga | 106501 |
Trinidad and Tobago | 1222363 |
Tunisia | 11037225 |
Turkmenistan | 5231422 |
Tuvalu | 10869 |
Uganda | 37101745 |
United Arab Emirates | 5779760 |
United Kingdom | 64088222 |
Uruguay | 3341893 |
Uzbekistan | 29199942 |
Vanuatu | 272264 |
Holy See (Vatican City) | 842 |
Vietnam | 94348835 |
Yemen | 26737317 |
Zimbabwe | 14229541 |
Taiwan | 23415126 |
Ashmore and Cartier Islands | None |
Christmas Island | 1530 |
Cocos (Keeling) Islands | 596 |
Coral Sea Islands | None |
Heard Island and McDonald Islands | None |
Norfolk Island | 2210 |
Hong Kong | 7141106 |
Macau | 592731 |
Faroe Islands | 50196 |
Clipperton Island | None |
French Polynesia | 282703 |
New Caledonia | 271615 |
Saint Martin | 31754 |
Saint Pierre and Miquelon | 5657 |
Wallis and Futuna | 15613 |
Aruba | 112162 |
Curacao | 148406 |
Sint Maarten | 39689 |
Cook Islands | 9838 |
Niue | 1190 |
Tokelau | 1337 |
Bouvet Island | None |
Jan Mayen | None |
Svalbard | 1872 |
Akrotiri | 15700 |
Anguilla | 16418 |
Bermuda | 70196 |
British Indian Ocean Territory | None |
British Virgin Islands | 33454 |
Cayman Islands | 56092 |
Dhekelia | 15700 |
Falkland Islands (Islas Malvinas) | 3361 |
Gibraltar | 29258 |
Guernsey | 66080 |
Jersey | 97294 |
Isle of Man | 87545 |
Montserrat | 5241 |
Pitcairn Islands | 48 |
Saint Helena, Ascension, and Tristan da Cunha | 7795 |
South Georgia and South Sandwich Islands | None |
Turks and Caicos Islands | 50280 |
American Samoa | 54343 |
Guam | 161785 |
Navassa Island | None |
Northern Mariana Islands | 52344 |
Puerto Rico | 3598357 |
Virgin Islands | 103574 |
Wake Island | None |
Gaza Strip | 1869055 |
Paracel Islands | None |
Spratly Islands | None |
West Bank | 2785366 |
Western Sahara | 570866 |
Okay, this is a loooooot. And there are reallllly small countries like Cocos Island, Vatican city, etc
Okay, let's see which country as the highest population growth rate
%%sql
SELECT name, population_growth
FROM facts
WHERE population_growth IN (SELECT MAX(population_growth) FROM facts WHERE name NOT IN ('World'))
* sqlite:///factbook.db Done.
name | population_growth |
---|---|
South Sudan | 4.02 |
Okay, this one came as a surprise. Ofcourse I expected it to be in Africa. But for some reason, I did not expect it to be South Sudan.
Next, I want to see which country has more water than land. I've heard there are countries that are mostly water than land.
So first, let's find the ratio of water to land for all the countries yeah
%%sql
SELECT name, area_land, area_water, ROUND(CAST(area_water AS Float)/CAST(area_land AS Float), 4) AS 'Ratio'
FROM facts
WHERE name != 'World'
* sqlite:///factbook.db Done.
name | area_land | area_water | Ratio |
---|---|---|---|
Afghanistan | 652230 | 0 | 0.0 |
Albania | 27398 | 1350 | 0.0493 |
Algeria | 2381741 | 0 | 0.0 |
Andorra | 468 | 0 | 0.0 |
Angola | 1246700 | 0 | 0.0 |
Antigua and Barbuda | 442 | 0 | 0.0 |
Argentina | 2736690 | 43710 | 0.016 |
Armenia | 28203 | 1540 | 0.0546 |
Australia | 7682300 | 58920 | 0.0077 |
Austria | 82445 | 1426 | 0.0173 |
Azerbaijan | 82629 | 3971 | 0.0481 |
Bahamas, The | 10010 | 3870 | 0.3866 |
Bahrain | 760 | 0 | 0.0 |
Bangladesh | 130170 | 18290 | 0.1405 |
Barbados | 430 | 0 | 0.0 |
Belarus | 202900 | 4700 | 0.0232 |
Belgium | 30278 | 250 | 0.0083 |
Belize | 22806 | 160 | 0.007 |
Benin | 110622 | 2000 | 0.0181 |
Bhutan | 38394 | 0 | 0.0 |
Bolivia | 1083301 | 15280 | 0.0141 |
Bosnia and Herzegovina | 51187 | 10 | 0.0002 |
Botswana | 566730 | 15000 | 0.0265 |
Brazil | 8358140 | 157630 | 0.0189 |
Brunei | 5265 | 500 | 0.095 |
Bulgaria | 108489 | 2390 | 0.022 |
Burkina Faso | 273800 | 400 | 0.0015 |
Burma | 653508 | 23070 | 0.0353 |
Burundi | 25680 | 2150 | 0.0837 |
Cambodia | 176515 | 4520 | 0.0256 |
Cameroon | 472710 | 2730 | 0.0058 |
Canada | 9093507 | 891163 | 0.098 |
Cabo Verde | 4033 | 0 | 0.0 |
Central African Republic | 622984 | 0 | 0.0 |
Chad | 1259200 | 24800 | 0.0197 |
Chile | 743812 | 12290 | 0.0165 |
China | 9326410 | 270550 | 0.029 |
Colombia | 1038700 | 100210 | 0.0965 |
Comoros | 2235 | 0 | 0.0 |
Congo, Democratic Republic of the | 2267048 | 77810 | 0.0343 |
Congo, Republic of the | 341500 | 500 | 0.0015 |
Costa Rica | 51060 | 40 | 0.0008 |
Cote d'Ivoire | 318003 | 4460 | 0.014 |
Croatia | 55974 | 620 | 0.0111 |
Cuba | 109820 | 1040 | 0.0095 |
Cyprus | 9241 | 10 | 0.0011 |
Czech Republic | 77247 | 1620 | 0.021 |
Denmark | 42434 | 660 | 0.0156 |
Djibouti | 23180 | 20 | 0.0009 |
Dominica | 751 | 0 | 0.0 |
Dominican Republic | 48320 | 350 | 0.0072 |
Ecuador | 276841 | 6720 | 0.0243 |
Egypt | 995450 | 6000 | 0.006 |
El Salvador | 20721 | 320 | 0.0154 |
Equatorial Guinea | 28051 | 0 | 0.0 |
Eritrea | 101000 | 16600 | 0.1644 |
Estonia | 42388 | 2840 | 0.067 |
Ethiopia | None | 104300 | None |
Fiji | 18274 | 0 | 0.0 |
Finland | 303815 | 34330 | 0.113 |
France | 640427 | 3374 | 0.0053 |
Gabon | 257667 | 10000 | 0.0388 |
Gambia, The | 10120 | 1180 | 0.1166 |
Georgia | 69700 | 0 | 0.0 |
Germany | 348672 | 8350 | 0.0239 |
Ghana | 227533 | 11000 | 0.0483 |
Greece | 130647 | 1310 | 0.01 |
Grenada | 344 | 0 | 0.0 |
Guatemala | 107159 | 1730 | 0.0161 |
Guinea | 245717 | 140 | 0.0006 |
Guinea-Bissau | 28120 | 8005 | 0.2847 |
Guyana | 196849 | 18120 | 0.0921 |
Haiti | 27560 | 190 | 0.0069 |
Honduras | 111890 | 200 | 0.0018 |
Hungary | 89608 | 3420 | 0.0382 |
Iceland | 100250 | 2750 | 0.0274 |
India | 2973193 | 314070 | 0.1056 |
Indonesia | 1811569 | 93000 | 0.0513 |
Iran | 1531595 | 116600 | 0.0761 |
Iraq | 437367 | 950 | 0.0022 |
Ireland | 68883 | 1390 | 0.0202 |
Israel | 20330 | 440 | 0.0216 |
Italy | 294140 | 7200 | 0.0245 |
Jamaica | 10831 | 160 | 0.0148 |
Japan | 364485 | 13430 | 0.0368 |
Jordan | 88802 | 540 | 0.0061 |
Kazakhstan | 2699700 | 25200 | 0.0093 |
Kenya | 569140 | 11227 | 0.0197 |
Kiribati | 811 | 0 | 0.0 |
Korea, North | 120408 | 130 | 0.0011 |
Korea, South | 96920 | 2800 | 0.0289 |
Kosovo | 10887 | 0 | 0.0 |
Kuwait | 17818 | 0 | 0.0 |
Kyrgyzstan | 191801 | 8150 | 0.0425 |
Laos | 230800 | 6000 | 0.026 |
Latvia | 62249 | 2340 | 0.0376 |
Lebanon | 10230 | 170 | 0.0166 |
Lesotho | 30355 | 0 | 0.0 |
Liberia | 96320 | 15049 | 0.1562 |
Libya | 1759540 | 0 | 0.0 |
Liechtenstein | 160 | 0 | 0.0 |
Lithuania | 62680 | 2620 | 0.0418 |
Luxembourg | 2586 | 0 | 0.0 |
Macedonia | 25433 | 280 | 0.011 |
Madagascar | 581540 | 5501 | 0.0095 |
Malawi | 94080 | 24404 | 0.2594 |
Malaysia | 328657 | 1190 | 0.0036 |
Maldives | 298 | 0 | 0.0 |
Mali | 1220190 | 20002 | 0.0164 |
Malta | 316 | 0 | 0.0 |
Marshall Islands | 181 | 0 | 0.0 |
Mauritania | 1030700 | 0 | 0.0 |
Mauritius | 2030 | 10 | 0.0049 |
Mexico | 1943945 | 20430 | 0.0105 |
Micronesia, Federated States of | 702 | 0 | 0.0 |
Moldova | 32891 | 960 | 0.0292 |
Monaco | 2 | 0 | 0.0 |
Mongolia | 1553556 | 10560 | 0.0068 |
Montenegro | 13452 | 360 | 0.0268 |
Morocco | 446300 | 250 | 0.0006 |
Mozambique | 786380 | 13000 | 0.0165 |
Namibia | 823290 | 1002 | 0.0012 |
Nauru | 21 | 0 | 0.0 |
Nepal | 143351 | 3830 | 0.0267 |
Netherlands | 33893 | 7650 | 0.2257 |
New Zealand | 267710 | None | None |
Nicaragua | 119990 | 10380 | 0.0865 |
Niger | 1266700 | 300 | 0.0002 |
Nigeria | 910768 | 13000 | 0.0143 |
Norway | 304282 | 19520 | 0.0642 |
Oman | 309500 | 0 | 0.0 |
Pakistan | 770875 | 25220 | 0.0327 |
Palau | 459 | 0 | 0.0 |
Panama | 74340 | 1080 | 0.0145 |
Papua New Guinea | 452860 | 9980 | 0.022 |
Paraguay | 397302 | 9450 | 0.0238 |
Peru | 1279996 | 5220 | 0.0041 |
Philippines | 298170 | 1830 | 0.0061 |
Poland | 304255 | 8430 | 0.0277 |
Portugal | 91470 | 620 | 0.0068 |
Qatar | 11586 | 0 | 0.0 |
Romania | 229891 | 8500 | 0.037 |
Russia | 16377742 | 720500 | 0.044 |
Rwanda | 24668 | 1670 | 0.0677 |
Saint Kitts and Nevis | 261 | 0 | 0.0 |
Saint Lucia | 606 | 10 | 0.0165 |
Saint Vincent and the Grenadines | 389 | 0 | 0.0 |
Samoa | 2821 | 10 | 0.0035 |
San Marino | 61 | 0 | 0.0 |
Sao Tome and Principe | 964 | 0 | 0.0 |
Saudi Arabia | 2149690 | 0 | 0.0 |
Senegal | 192530 | 4192 | 0.0218 |
Serbia | 77474 | 0 | 0.0 |
Seychelles | 455 | 0 | 0.0 |
Sierra Leone | 71620 | 120 | 0.0017 |
Singapore | 687 | 10 | 0.0146 |
Slovakia | 48105 | 930 | 0.0193 |
Slovenia | 20151 | 122 | 0.0061 |
Solomon Islands | 27986 | 910 | 0.0325 |
Somalia | 627337 | 10320 | 0.0165 |
South Africa | 1214470 | 4620 | 0.0038 |
South Sudan | None | None | None |
Spain | 498980 | 6390 | 0.0128 |
Sri Lanka | 64630 | 980 | 0.0152 |
Sudan | None | None | None |
Suriname | 156000 | 7820 | 0.0501 |
Swaziland | 17204 | 160 | 0.0093 |
Sweden | 410335 | 39960 | 0.0974 |
Switzerland | 39997 | 1280 | 0.032 |
Syria | 183630 | 1550 | 0.0084 |
Tajikistan | 141510 | 2590 | 0.0183 |
Tanzania | 885800 | 61500 | 0.0694 |
Thailand | 510890 | 2230 | 0.0044 |
Timor-Leste | 14874 | 0 | 0.0 |
Togo | 54385 | 2400 | 0.0441 |
Tonga | 717 | 30 | 0.0418 |
Trinidad and Tobago | 5128 | 0 | 0.0 |
Tunisia | 155360 | 8250 | 0.0531 |
Turkey | 769632 | 13930 | 0.0181 |
Turkmenistan | 469930 | 18170 | 0.0387 |
Tuvalu | 26 | 0 | 0.0 |
Uganda | 197100 | 43938 | 0.2229 |
Ukraine | 579330 | 24220 | 0.0418 |
United Arab Emirates | 83600 | 0 | 0.0 |
United Kingdom | 241930 | 1680 | 0.0069 |
United States | 9161966 | 664709 | 0.0726 |
Uruguay | 175015 | 1200 | 0.0069 |
Uzbekistan | 425400 | 22000 | 0.0517 |
Vanuatu | 12189 | 0 | 0.0 |
Holy See (Vatican City) | 0 | 0 | None |
Venezuela | 882050 | 30000 | 0.034 |
Vietnam | 310070 | 21140 | 0.0682 |
Yemen | 527968 | 0 | 0.0 |
Zambia | 743398 | 9220 | 0.0124 |
Zimbabwe | 386847 | 3910 | 0.0101 |
Taiwan | 32260 | 3720 | 0.1153 |
European Union | None | None | None |
Ashmore and Cartier Islands | 5 | 0 | 0.0 |
Christmas Island | 135 | 0 | 0.0 |
Cocos (Keeling) Islands | 14 | 0 | 0.0 |
Coral Sea Islands | 3 | 0 | 0.0 |
Heard Island and McDonald Islands | 412 | 0 | 0.0 |
Norfolk Island | 36 | 0 | 0.0 |
Hong Kong | 1073 | 35 | 0.0326 |
Macau | 28 | 0 | 0.0 |
Faroe Islands | 1393 | 0 | 0.0 |
Greenland | 2166086 | None | None |
Clipperton Island | 6 | 0 | 0.0 |
French Polynesia | 3827 | 340 | 0.0888 |
French Southern and Antarctic Lands | None | None | None |
New Caledonia | 18275 | 300 | 0.0164 |
Saint Barthelemy | None | None | None |
Saint Martin | 54 | None | None |
Saint Pierre and Miquelon | 242 | 0 | 0.0 |
Wallis and Futuna | 142 | 0 | 0.0 |
Aruba | 180 | 0 | 0.0 |
Curacao | 444 | 0 | 0.0 |
Sint Maarten | 34 | 0 | 0.0 |
Cook Islands | 236 | 0 | 0.0 |
Niue | 260 | 0 | 0.0 |
Tokelau | 12 | 0 | 0.0 |
Bouvet Island | 49 | 0 | 0.0 |
Jan Mayen | 377 | 0 | 0.0 |
Svalbard | 62045 | 0 | 0.0 |
Akrotiri | None | None | None |
Anguilla | 91 | 0 | 0.0 |
Bermuda | 54 | 0 | 0.0 |
British Indian Ocean Territory | 60 | 54340 | 905.6667 |
British Virgin Islands | 151 | 0 | 0.0 |
Cayman Islands | 264 | 0 | 0.0 |
Dhekelia | None | None | None |
Falkland Islands (Islas Malvinas) | 12173 | 0 | 0.0 |
Gibraltar | 6 | 0 | 0.0 |
Guernsey | 78 | 0 | 0.0 |
Jersey | 116 | 0 | 0.0 |
Isle of Man | 572 | 0 | 0.0 |
Montserrat | 102 | 0 | 0.0 |
Pitcairn Islands | 47 | 0 | 0.0 |
Saint Helena, Ascension, and Tristan da Cunha | 122 | 0 | 0.0 |
South Georgia and South Sandwich Islands | 3903 | 0 | 0.0 |
Turks and Caicos Islands | 948 | 0 | 0.0 |
American Samoa | 199 | 0 | 0.0 |
Guam | 544 | 0 | 0.0 |
Navassa Island | 5 | 0 | 0.0 |
Northern Mariana Islands | 464 | 0 | 0.0 |
Puerto Rico | 8870 | 4921 | 0.5548 |
Virgin Islands | 346 | 1564 | 4.5202 |
Wake Island | 6 | 0 | 0.0 |
United States Pacific Island Wildlife Refuges | None | None | None |
Antarctica | 280000 | None | None |
Gaza Strip | 360 | 0 | 0.0 |
Paracel Islands | 7 | 0 | 0.0 |
Spratly Islands | 5 | 0 | 0.0 |
West Bank | 5640 | 220 | 0.039 |
Western Sahara | 266000 | 0 | 0.0 |
Arctic Ocean | None | None | None |
Atlantic Ocean | None | None | None |
Indian Ocean | None | None | None |
Pacific Ocean | None | None | None |
Southern Ocean | None | None | None |
From this, the values for the OCeans are empty so it would seem that all the countries here have more land area than water. but let's still confirm that.
%%sql
SELECT name, area_land, area_water, ROUND(CAST(area_water AS Float)/CAST(area_land AS Float), 4) AS 'Ratio'
FROM facts
WHERE name != 'World'
AND Ratio > 1
* sqlite:///factbook.db Done.
name | area_land | area_water | Ratio |
---|---|---|---|
British Indian Ocean Territory | 60 | 54340 | 905.6667 |
Virgin Islands | 346 | 1564 | 4.5202 |
Okay, So we have just two countries with a higher water area than land area. British Indian Ocean Territory
stands out though. Is that ven an Island or a complete water body, lol.
Now let's explore birth and death rate. let's see which countries have a higher death rate than birth rate cos that would have to be abnormal yeah.
%%sql
SELECT name, birth_rate, death_rate
FROM facts
WHERE name != 'World'
AND death_rate > birth_rate
* sqlite:///factbook.db Done.
name | birth_rate | death_rate |
---|---|---|
Austria | 9.41 | 9.42 |
Belarus | 10.7 | 13.36 |
Bosnia and Herzegovina | 8.87 | 9.75 |
Bulgaria | 8.92 | 14.44 |
Croatia | 9.45 | 12.18 |
Czech Republic | 9.63 | 10.34 |
Estonia | 10.51 | 12.4 |
Germany | 8.47 | 11.42 |
Greece | 8.66 | 11.09 |
Hungary | 9.16 | 12.73 |
Italy | 8.74 | 10.19 |
Japan | 7.93 | 9.51 |
Latvia | 10.0 | 14.31 |
Lithuania | 10.1 | 14.27 |
Moldova | 12.0 | 12.59 |
Monaco | 6.65 | 9.24 |
Poland | 9.74 | 10.19 |
Portugal | 9.27 | 11.02 |
Romania | 9.14 | 11.9 |
Russia | 11.6 | 13.69 |
Serbia | 9.08 | 13.66 |
Slovenia | 8.42 | 11.37 |
Ukraine | 10.72 | 14.46 |
Saint Pierre and Miquelon | 7.42 | 9.72 |
WHATTTTT???
Okay, this is too much. Germany, Japan, Italy???? I'll need to research this to find out why. This should not be.
DFinally, I want to see which countries have the highest population to area ratio. Let's see which countries we can counsider over populated and probably contract Thanos to snap a finger at them. lmao
%%sql
SELECT name, population, area , ROUND(CAST(population AS Float)/area, 2) AS population_area_ratio
FROM facts
WHERE name != 'World'
ORDER BY population_area_ratio DESC
* sqlite:///factbook.db Done.
name | population | area | 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 |
France | 66553766 | 643801 | 103.38 |
Austria | 8665550 | 83871 | 103.32 |
Armenia | 3056382 | 29743 | 102.76 |
Turkey | 79414269 | 783562 | 101.35 |
Cuba | 11031433 | 110860 | 99.51 |
Dominica | 73607 | 751 | 98.01 |
Slovenia | 1983412 | 20273 | 97.84 |
Spain | 48146134 | 505370 | 95.27 |
Costa Rica | 4814144 | 51100 | 94.21 |
Benin | 10448647 | 112622 | 92.78 |
Serbia | 7176794 | 77474 | 92.63 |
Malaysia | 30513848 | 329847 | 92.51 |
Syria | 17064854 | 185180 | 92.15 |
Romania | 21666350 | 238391 | 90.89 |
Jordan | 8117564 | 89342 | 90.86 |
Ethiopia | 99465819 | 1104300 | 90.07 |
Egypt | 88487396 | 1001450 | 88.36 |
Cambodia | 15708756 | 181035 | 86.77 |
Iraq | 37056169 | 438317 | 84.54 |
Burma | 56320206 | 676578 | 83.24 |
Timor-Leste | 1231116 | 14874 | 82.77 |
Swaziland | 1435613 | 17364 | 82.68 |
Sierra Leone | 5879098 | 71740 | 81.95 |
Greece | 10775643 | 131957 | 81.66 |
Macedonia | 2096015 | 25713 | 81.52 |
Kenya | 45925301 | 580367 | 79.13 |
Croatia | 4464844 | 56594 | 78.89 |
Honduras | 8746673 | 112090 | 78.03 |
Bosnia and Herzegovina | 3867055 | 51197 | 75.53 |
Morocco | 33322699 | 446550 | 74.62 |
Brunei | 429646 | 5765 | 74.53 |
Ukraine | 44429471 | 603550 | 73.61 |
Cote d'Ivoire | 23295302 | 322463 | 72.24 |
Senegal | 13975834 | 196722 | 71.04 |
Georgia | 4931226 | 69700 | 70.75 |
Samoa | 197773 | 2831 | 69.86 |
Ireland | 4892305 | 70273 | 69.62 |
United Arab Emirates | 5779760 | 83600 | 69.14 |
Burkina Faso | 18931686 | 274200 | 69.04 |
French Polynesia | 282703 | 4167 | 67.84 |
Tunisia | 11037225 | 163610 | 67.46 |
Uzbekistan | 29199942 | 447400 | 65.27 |
Bulgaria | 7186893 | 110879 | 64.82 |
Lesotho | 1947701 | 30355 | 64.16 |
Mexico | 121736809 | 1964375 | 61.97 |
Norfolk Island | 2210 | 36 | 61.39 |
Tajikistan | 8191958 | 144100 | 56.85 |
Ecuador | 15868396 | 283561 | 55.96 |
Eritrea | 6527689 | 117600 | 55.51 |
Virgin Islands | 103574 | 1910 | 54.23 |
Tanzania | 51045882 | 947300 | 53.89 |
Turks and Caicos Islands | 50280 | 948 | 53.04 |
Montserrat | 5241 | 102 | 51.38 |
Yemen | 26737317 | 527968 | 50.64 |
Afghanistan | 32564342 | 652230 | 49.93 |
Cameroon | 23739218 | 475440 | 49.93 |
Fiji | 909389 | 18274 | 49.76 |
Iran | 81824270 | 1648195 | 49.64 |
Panama | 3657024 | 75420 | 48.49 |
Guinea | 11780162 | 245857 | 47.91 |
Guinea-Bissau | 1726170 | 36125 | 47.78 |
Montenegro | 647073 | 13812 | 46.85 |
Palau | 21265 | 459 | 46.33 |
Belarus | 9589689 | 207600 | 46.19 |
Nicaragua | 5907881 | 130370 | 45.32 |
Lithuania | 2884433 | 65300 | 44.17 |
South Africa | 53675563 | 1219090 | 44.03 |
Cocos (Keeling) Islands | 596 | 14 | 42.57 |
Cook Islands | 9838 | 236 | 41.69 |
Colombia | 46736728 | 1138910 | 41.04 |
Madagascar | 23812681 | 587041 | 40.56 |
Liberia | 4195666 | 111369 | 37.67 |
Zimbabwe | 14229541 | 390757 | 36.42 |
Faroe Islands | 50196 | 1393 | 36.03 |
Djibouti | 828324 | 23200 | 35.7 |
Congo, Democratic Republic of the | 79375136 | 2344858 | 33.85 |
United States | 321368864 | 9826675 | 32.7 |
Venezuela | 29275460 | 912050 | 32.1 |
Mozambique | 25303113 | 799380 | 31.65 |
Latvia | 1986705 | 64589 | 30.76 |
Laos | 6911544 | 236800 | 29.19 |
Kyrgyzstan | 5664939 | 199951 | 28.33 |
Estonia | 1265420 | 45228 | 27.98 |
Equatorial Guinea | 740743 | 28051 | 26.41 |
Saint Helena, Ascension, and Tristan da Cunha | 7795 | 308 | 25.31 |
Brazil | 204259812 | 8515770 | 23.99 |
Peru | 30444999 | 1285216 | 23.69 |
Bahamas, The | 324597 | 13880 | 23.39 |
Saint Pierre and Miquelon | 5657 | 242 | 23.38 |
Chile | 17508260 | 756102 | 23.16 |
Vanuatu | 272264 | 12189 | 22.34 |
Sweden | 9801616 | 450295 | 21.77 |
Solomon Islands | 622469 | 28896 | 21.54 |
Zambia | 15066266 | 752618 | 20.02 |
Sudan | 36108853 | 1861484 | 19.4 |
Bhutan | 741919 | 38394 | 19.32 |
Uruguay | 3341893 | 176215 | 18.96 |
South Sudan | 12042910 | 644329 | 18.69 |
Paraguay | 6783272 | 406752 | 16.68 |
Somalia | 10616380 | 637657 | 16.65 |
Algeria | 39542166 | 2381741 | 16.6 |
New Zealand | 4438393 | 267710 | 16.58 |
Finland | 5476922 | 338145 | 16.2 |
Norway | 5207689 | 323802 | 16.08 |
Angola | 19625353 | 1246700 | 15.74 |
Argentina | 43431886 | 2780400 | 15.62 |
Belize | 347369 | 22966 | 15.13 |
New Caledonia | 271615 | 18575 | 14.62 |
Papua New Guinea | 6672429 | 462840 | 14.42 |
Congo, Republic of the | 4755097 | 342000 | 13.9 |
Mali | 16955536 | 1240192 | 13.67 |
Saudi Arabia | 27752316 | 2149690 | 12.91 |
Christmas Island | 1530 | 135 | 11.33 |
Turkmenistan | 5231422 | 488100 | 10.72 |
Oman | 3286936 | 309500 | 10.62 |
Bolivia | 10800882 | 1098581 | 9.83 |
Central African Republic | 5391539 | 622984 | 8.65 |
Russia | 142423773 | 17098242 | 8.33 |
Kazakhstan | 18157122 | 2724900 | 6.66 |
Gabon | 1705336 | 267667 | 6.37 |
Niue | 1190 | 260 | 4.58 |
Botswana | 2182719 | 581730 | 3.75 |
Libya | 6411776 | 1759540 | 3.64 |
Suriname | 579633 | 163820 | 3.54 |
Canada | 35099836 | 9984670 | 3.52 |
Mauritania | 3596702 | 1030700 | 3.49 |
Guyana | 735222 | 214969 | 3.42 |
Iceland | 331918 | 103000 | 3.22 |
Australia | 22751014 | 7741220 | 2.94 |
Namibia | 2212307 | 824292 | 2.68 |
Western Sahara | 570866 | 266000 | 2.15 |
Mongolia | 2992908 | 1564116 | 1.91 |
Pitcairn Islands | 48 | 47 | 1.02 |
Falkland Islands (Islas Malvinas) | 3361 | 12173 | 0.28 |
Greenland | 57733 | 2166086 | 0.03 |
Svalbard | 1872 | 62045 | 0.03 |
Chad | 11631456 | None | None |
Niger | 18045729 | None | None |
Holy See (Vatican City) | 842 | 0 | None |
Ashmore and Cartier Islands | None | 5 | None |
Coral Sea Islands | None | 3 | None |
Heard Island and McDonald Islands | None | 412 | None |
Clipperton Island | None | 6 | None |
French Southern and Antarctic Lands | None | None | None |
Saint Barthelemy | 7237 | None | None |
Bouvet Island | None | 49 | None |
Jan Mayen | None | 377 | None |
British Indian Ocean Territory | None | 54400 | None |
South Georgia and South Sandwich Islands | None | 3903 | None |
Navassa Island | None | 5 | None |
Wake Island | None | 6 | None |
United States Pacific Island Wildlife Refuges | None | None | None |
Antarctica | 0 | None | None |
Paracel Islands | None | 7 | None |
Spratly Islands | None | 5 | None |
Arctic Ocean | None | None | None |
Atlantic Ocean | None | None | None |
Indian Ocean | None | None | None |
Pacific Ocean | None | None | None |
Southern Ocean | None | None | None |
I think this is too much so let me limit the results to the first 30 entries
%%sql
SELECT name, population, area , ROUND(CAST(population AS Float)/area, 2) AS population_area_ratio
FROM facts
WHERE name != 'World'
ORDER BY population_area_ratio DESC
LIMIT 30
* sqlite:///factbook.db Done.
name | population | area | 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 |