%%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 |
Minimum population
%%sql
SELECT MIN(population)
FROM facts;
Done.
MIN(population) |
---|
0 |
Maximum population
%%sql
SELECT MAX(population) AS 'Max Population'
FROM facts;
Done.
Max Population |
---|
7256490011 |
Minimum population growth
%%sql
SELECT MIN(population_growth) AS 'Min Population Growth'
FROM facts;
Done.
Min Population Growth |
---|
0.0 |
Maximum population growth
%%sql
SELECT MAX(population_growth) AS 'Max Population Growth'
FROM facts;
Done.
Max Population Growth |
---|
4.02 |
Observing the results from the above queries, we found some abnormal values which doesn't fit in. Let's take a closer look at each of these by using SQL Subqueries.
Query that returns the countrie(s) with the minimum population
%%sql
SELECT name AS Country, population AS Population
FROM facts
WHERE population == (SELECT MIN(population)
FROM facts);
Done.
Country | Population |
---|---|
Antarctica | 0 |
Hence, we find that Antartica is the only country with no population.
Query that returns the countrie(s) with the maximum population
%%sql
SELECT name AS Country, population AS Population
FROM facts
WHERE population == (SELECT MAX(population)
FROM facts);
Done.
Country | Population |
---|---|
World | 7256490011 |
We find that there are some inconsistencies in the data as there is no country with the name World. Instead this row denotes the population for the whole world, which explains the population of over 7.2 billion.
Let's continue calculating the summary statistics by excluding the row for the whole world.
We can use the <>
operator in the WHERE
clause to exclude the World
row.
%%sql
SELECT MIN(population) AS 'Min Population',
MAX(population) AS 'Max Population',
MIN(population_growth) AS 'Min Population Growth',
MAX(population_growth) AS '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 |
Next, let's calculate the average values for the following columns:
%%sql
SELECT AVG(population) AS 'Avg Population',
AVG(area) AS 'Avg Area'
FROM facts
WHERE name <> 'World';
Done.
Avg Population | Avg Area |
---|---|
32242666.56846473 | 555093.546184739 |
Next, we'll try to find out some of the densely populated countries. Let's identify countries that have:
%%sql
SELECT *
FROM facts
WHERE population > (SELECT AVG(population)
FROM facts)
AND
area < (SELECT AVG(area)
FROM facts);
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 |
65 | gm | Germany | 357022 | 348672 | 8350 | 80854408 | 0.17 | 8.47 | 11.42 | 1.24 |
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 |
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 |
192 | vm | Vietnam | 331210 | 310070 | 21140 | 94348835 | 0.97 | 15.96 | 5.93 | 0.3 |
Next, let's look at the 5 most populated countries in the dataset
%%sql
SELECT name AS Country, population as Population
FROM facts
WHERE Country <> 'World'
ORDER BY population DESC
LIMIT 5;
Done.
Country | Population |
---|---|
China | 1367485388 |
India | 1251695584 |
European Union | 513949445 |
United States | 321368864 |
Indonesia | 255993674 |
Next up, let;s check what proportion of world population does China hold.
%%sql
SELECT CAST(Population AS FLOAT) / (SELECT MAX(population) FROM facts) AS Proportion
FROM facts
WHERE name == (SELECT name
FROM facts
WHERE population == (SELECT MAX(population)
FROM facts
WHERE name <> 'World'));
Done.
Proportion |
---|
0.1884499786986615 |
Let's now query for countries that have a higher death rate than birth rate.
%%sql
SELECT name AS Country,
birth_rate AS 'Birth Rate',
death_rate AS 'Death Rate'
FROM facts
WHERE 'Death Rate' > 'Birth Rate';
Done.
Country | Birth Rate | Death Rate |
---|---|---|
Afghanistan | 38.57 | 13.89 |
Albania | 12.92 | 6.58 |
Algeria | 23.67 | 4.31 |
Andorra | 8.13 | 6.96 |
Angola | 38.78 | 11.49 |
Antigua and Barbuda | 15.85 | 5.69 |
Argentina | 16.64 | 7.33 |
Armenia | 13.61 | 9.34 |
Australia | 12.15 | 7.14 |
Austria | 9.41 | 9.42 |
Azerbaijan | 16.64 | 7.07 |
Bahamas, The | 15.5 | 7.05 |
Bahrain | 13.66 | 2.69 |
Bangladesh | 21.14 | 5.61 |
Barbados | 11.87 | 8.44 |
Belarus | 10.7 | 13.36 |
Belgium | 11.41 | 9.63 |
Belize | 24.68 | 5.97 |
Benin | 36.02 | 8.21 |
Bhutan | 17.78 | 6.69 |
Bolivia | 22.76 | 6.52 |
Bosnia and Herzegovina | 8.87 | 9.75 |
Botswana | 20.96 | 13.39 |
Brazil | 14.46 | 6.58 |
Brunei | 17.32 | 3.52 |
Bulgaria | 8.92 | 14.44 |
Burkina Faso | 42.03 | 11.72 |
Burma | 18.39 | 7.96 |
Burundi | 42.01 | 9.27 |
Cambodia | 23.83 | 7.68 |
Cameroon | 36.17 | 10.11 |
Canada | 10.28 | 8.42 |
Cabo Verde | 20.33 | 6.11 |
Central African Republic | 35.08 | 13.8 |
Chad | 36.6 | 14.28 |
Chile | 13.83 | 6.0 |
China | 12.49 | 7.53 |
Colombia | 16.47 | 5.4 |
Comoros | 27.84 | 7.57 |
Congo, Democratic Republic of the | 34.88 | 10.07 |
Congo, Republic of the | 35.85 | 10.0 |
Costa Rica | 15.91 | 4.55 |
Cote d'Ivoire | 28.67 | 9.55 |
Croatia | 9.45 | 12.18 |
Cuba | 9.9 | 7.72 |
Cyprus | 11.41 | 6.62 |
Czech Republic | 9.63 | 10.34 |
Denmark | 10.27 | 10.25 |
Djibouti | 23.65 | 7.73 |
Dominica | 15.41 | 7.91 |
Dominican Republic | 18.73 | 4.55 |
Ecuador | 18.51 | 5.06 |
Egypt | 22.9 | 4.77 |
El Salvador | 16.46 | 5.69 |
Equatorial Guinea | 33.31 | 8.19 |
Eritrea | 30.0 | 7.52 |
Estonia | 10.51 | 12.4 |
Ethiopia | 37.27 | 8.19 |
Fiji | 19.43 | 6.04 |
Finland | 10.72 | 9.83 |
France | 12.38 | 9.16 |
Gabon | 34.49 | 13.12 |
Gambia, The | 30.86 | 7.15 |
Georgia | 12.74 | 10.82 |
Germany | 8.47 | 11.42 |
Ghana | 31.09 | 7.22 |
Greece | 8.66 | 11.09 |
Grenada | 16.03 | 8.08 |
Guatemala | 24.89 | 4.77 |
Guinea | 35.74 | 9.46 |
Guinea-Bissau | 33.38 | 14.33 |
Guyana | 15.59 | 7.32 |
Haiti | 22.31 | 7.83 |
Honduras | 23.14 | 5.17 |
Hungary | 9.16 | 12.73 |
Iceland | 13.91 | 6.28 |
India | 19.55 | 7.32 |
Indonesia | 16.72 | 6.37 |
Iran | 17.99 | 5.94 |
Iraq | 31.45 | 3.77 |
Ireland | 14.84 | 6.48 |
Israel | 18.48 | 5.15 |
Italy | 8.74 | 10.19 |
Jamaica | 18.16 | 6.7 |
Japan | 7.93 | 9.51 |
Jordan | 25.37 | 3.79 |
Kazakhstan | 19.15 | 8.21 |
Kenya | 26.4 | 6.89 |
Kiribati | 21.46 | 7.12 |
Korea, North | 14.52 | 9.21 |
Korea, South | 8.19 | 6.75 |
Kosovo | None | None |
Kuwait | 19.91 | 2.18 |
Kyrgyzstan | 22.98 | 6.65 |
Laos | 24.25 | 7.63 |
Latvia | 10.0 | 14.31 |
Lebanon | 14.59 | 4.88 |
Lesotho | 25.47 | 14.89 |
Liberia | 34.41 | 9.69 |
Libya | 18.03 | 3.58 |
Liechtenstein | 10.45 | 7.12 |
Lithuania | 10.1 | 14.27 |
Luxembourg | 11.37 | 7.24 |
Macedonia | 11.55 | 9.08 |
Madagascar | 32.61 | 6.81 |
Malawi | 41.56 | 8.41 |
Malaysia | 19.71 | 5.03 |
Maldives | 15.75 | 3.89 |
Mali | 44.99 | 12.89 |
Malta | 10.18 | 9.09 |
Marshall Islands | 25.6 | 4.21 |
Mauritania | 31.34 | 8.2 |
Mauritius | 13.29 | 6.91 |
Mexico | 18.78 | 5.26 |
Micronesia, Federated States of | 20.54 | 4.23 |
Moldova | 12.0 | 12.59 |
Monaco | 6.65 | 9.24 |
Mongolia | 20.25 | 6.35 |
Montenegro | 10.42 | 9.43 |
Morocco | 18.2 | 4.81 |
Mozambique | 38.58 | 12.1 |
Namibia | 19.8 | 13.91 |
Nauru | 24.95 | 5.87 |
Nepal | 20.64 | 6.56 |
Netherlands | 10.83 | 8.66 |
New Zealand | 13.33 | 7.36 |
Nicaragua | 18.03 | 5.08 |
Niger | 45.45 | 12.42 |
Nigeria | 37.64 | 12.9 |
Norway | 12.14 | 8.12 |
Oman | 24.44 | 3.36 |
Pakistan | 22.58 | 6.49 |
Palau | 11.05 | 7.99 |
Panama | 18.32 | 4.81 |
Papua New Guinea | 24.38 | 6.53 |
Paraguay | 16.37 | 4.68 |
Peru | 18.28 | 6.01 |
Philippines | 24.27 | 6.11 |
Poland | 9.74 | 10.19 |
Portugal | 9.27 | 11.02 |
Qatar | 9.84 | 1.53 |
Romania | 9.14 | 11.9 |
Russia | 11.6 | 13.69 |
Rwanda | 33.75 | 8.96 |
Saint Kitts and Nevis | 13.5 | 7.09 |
Saint Lucia | 13.7 | 7.42 |
Saint Vincent and the Grenadines | 13.57 | 7.18 |
Samoa | 20.87 | 5.32 |
San Marino | 8.63 | 8.45 |
Sao Tome and Principe | 34.23 | 7.24 |
Saudi Arabia | 18.51 | 3.33 |
Senegal | 34.52 | 8.46 |
Serbia | 9.08 | 13.66 |
Seychelles | 14.19 | 6.89 |
Sierra Leone | 37.03 | 10.81 |
Singapore | 8.27 | 3.43 |
Slovakia | 9.91 | 9.74 |
Slovenia | 8.42 | 11.37 |
Solomon Islands | 25.77 | 3.85 |
Somalia | 40.45 | 13.62 |
South Africa | 20.75 | 9.91 |
South Sudan | 36.91 | 8.18 |
Spain | 9.64 | 9.04 |
Sri Lanka | 15.85 | 6.11 |
Sudan | 29.19 | 7.66 |
Suriname | 16.34 | 6.13 |
Swaziland | 24.67 | 13.56 |
Sweden | 11.99 | 9.4 |
Switzerland | 10.5 | 8.13 |
Syria | 22.17 | 4.0 |
Tajikistan | 24.38 | 6.18 |
Tanzania | 36.39 | 8.0 |
Thailand | 11.19 | 7.8 |
Timor-Leste | 34.16 | 6.1 |
Togo | 34.13 | 7.26 |
Tonga | 23.0 | 4.85 |
Trinidad and Tobago | 13.46 | 8.56 |
Tunisia | 16.64 | 5.98 |
Turkey | 16.33 | 5.88 |
Turkmenistan | 19.4 | 6.13 |
Tuvalu | 23.74 | 8.74 |
Uganda | 43.79 | 10.69 |
Ukraine | 10.72 | 14.46 |
United Arab Emirates | 15.43 | 1.97 |
United Kingdom | 12.17 | 9.35 |
United States | 12.49 | 8.15 |
Uruguay | 13.07 | 9.45 |
Uzbekistan | 17.0 | 5.3 |
Vanuatu | 25.04 | 4.09 |
Holy See (Vatican City) | None | None |
Venezuela | 19.16 | 5.31 |
Vietnam | 15.96 | 5.93 |
Yemen | 29.98 | 6.28 |
Zambia | 42.13 | 12.67 |
Zimbabwe | 32.26 | 10.13 |
Taiwan | 8.47 | 7.11 |
European Union | 10.2 | 10.2 |
Ashmore and Cartier Islands | None | None |
Christmas Island | None | None |
Cocos (Keeling) Islands | None | None |
Coral Sea Islands | None | None |
Heard Island and McDonald Islands | None | None |
Norfolk Island | None | None |
Hong Kong | 9.23 | 7.07 |
Macau | 8.88 | 4.22 |
Faroe Islands | 13.77 | 8.71 |
Greenland | 14.48 | 8.49 |
Clipperton Island | None | None |
French Polynesia | 15.22 | 5.02 |
French Southern and Antarctic Lands | None | None |
New Caledonia | 15.33 | 5.52 |
Saint Barthelemy | None | None |
Saint Martin | None | None |
Saint Pierre and Miquelon | 7.42 | 9.72 |
Wallis and Futuna | 13.45 | 5.06 |
Aruba | 12.56 | 8.18 |
Curacao | 13.8 | 8.2 |
Sint Maarten | 13.0 | 4.51 |
Cook Islands | 14.33 | 8.03 |
Niue | None | None |
Tokelau | None | None |
Bouvet Island | None | None |
Jan Mayen | None | None |
Svalbard | None | None |
Akrotiri | None | None |
Anguilla | 12.67 | 4.57 |
Bermuda | 11.33 | 8.23 |
British Indian Ocean Territory | None | None |
British Virgin Islands | 10.91 | 4.99 |
Cayman Islands | 12.11 | 5.53 |
Dhekelia | None | None |
Falkland Islands (Islas Malvinas) | 10.9 | 4.9 |
Gibraltar | 14.08 | 8.37 |
Guernsey | 9.84 | 8.78 |
Jersey | 11.91 | 7.68 |
Isle of Man | 11.1 | 10.06 |
Montserrat | 11.26 | 6.3 |
Pitcairn Islands | None | None |
Saint Helena, Ascension, and Tristan da Cunha | 9.88 | 7.44 |
South Georgia and South Sandwich Islands | None | None |
Turks and Caicos Islands | 16.13 | 3.1 |
American Samoa | 22.89 | 4.75 |
Guam | 16.82 | 5.12 |
Navassa Island | None | None |
Northern Mariana Islands | 18.32 | 3.71 |
Puerto Rico | 10.86 | 8.67 |
Virgin Islands | 10.31 | 8.54 |
Wake Island | None | None |
United States Pacific Island Wildlife Refuges | None | None |
Antarctica | None | None |
Gaza Strip | 31.11 | 3.04 |
Paracel Islands | None | None |
Spratly Islands | None | None |
West Bank | 22.99 | 3.5 |
Western Sahara | 30.24 | 8.34 |
Arctic Ocean | None | None |
Atlantic Ocean | None | None |
Indian Ocean | None | None |
Pacific Ocean | None | None |
Southern Ocean | None | None |
World | 18.6 | 7.8 |