%%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 |
%%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;
Done.
Minimum Population | Maximum Population | Minimum population growth | Maximum population growth |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
%%sql
SELECT name, population
FROM facts
ORDER BY population DESC
LIMIT 25;
Done.
name | population |
---|---|
World | 7256490011 |
China | 1367485388 |
India | 1251695584 |
European Union | 513949445 |
United States | 321368864 |
Indonesia | 255993674 |
Brazil | 204259812 |
Pakistan | 199085847 |
Nigeria | 181562056 |
Bangladesh | 168957745 |
Russia | 142423773 |
Japan | 126919659 |
Mexico | 121736809 |
Philippines | 100998376 |
Ethiopia | 99465819 |
Vietnam | 94348835 |
Egypt | 88487396 |
Iran | 81824270 |
Germany | 80854408 |
Turkey | 79414269 |
Congo, Democratic Republic of the | 79375136 |
Thailand | 67976405 |
France | 66553766 |
United Kingdom | 64088222 |
Italy | 61855120 |
%%sql
SELECT name, population
FROM facts
ORDER BY population
LIMIT 25;
Done.
name | population |
---|---|
Ashmore and Cartier Islands | None |
Coral Sea Islands | None |
Heard Island and McDonald Islands | None |
Clipperton Island | None |
French Southern and Antarctic Lands | None |
Bouvet Island | None |
Jan Mayen | None |
British Indian Ocean Territory | None |
South Georgia and South Sandwich Islands | None |
Navassa Island | None |
Wake Island | None |
United States Pacific Island Wildlife Refuges | None |
Paracel Islands | None |
Spratly Islands | None |
Arctic Ocean | None |
Atlantic Ocean | None |
Indian Ocean | None |
Pacific Ocean | None |
Southern Ocean | None |
Antarctica | 0 |
Pitcairn Islands | 48 |
Cocos (Keeling) Islands | 596 |
Holy See (Vatican City) | 842 |
Niue | 1190 |
Tokelau | 1337 |
%%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 != 'World';
Done.
Minimum Population | Maximum Population | Minimum population growth | Maximum population growth |
---|---|---|---|
0 | 1367485388 | 0.0 | 4.02 |
%%sql
SELECT AVG(population) AS avg_pop,
AVG(area) AS avg_area
FROM facts;
Done.
avg_pop | avg_area |
---|---|
62094928.32231405 | 555093.546184739 |
%%sql
SELECT name,
population AS pop_above_avg,
area AS area_below_avg
FROM facts
WHERE (population > (SELECT AVG(population)
FROM facts) AND
area < (SELECT AVG(area)
FROM facts))
AND name != 'World';
Done.
name | pop_above_avg | area_below_avg |
---|---|---|
Bangladesh | 168957745 | 148460 |
Germany | 80854408 | 357022 |
Japan | 126919659 | 377915 |
Philippines | 100998376 | 300000 |
Thailand | 67976405 | 513120 |
United Kingdom | 64088222 | 243610 |
Vietnam | 94348835 | 331210 |
%%sql
SELECT name, MAX(population)
FROM facts
WHERE (name != 'World' AND name != 'European Union');
Done.
name | MAX(population) |
---|---|
China | 1367485388 |
%%sql
SELECT name, MAX(population_growth)
FROM facts
WHERE (name != 'World' AND name != 'European Union');
Done.
name | MAX(population_growth) |
---|---|
South Sudan | 4.02 |
%%capture
%load_ext sql
%sql sqlite:///factbook.db
'Connected: None@factbook.db'
%%sql
SELECT name, ROUND(((CAST(area_water AS FLOAT) / CAST(area AS FLOAT)) * 100), 2) AS Ratio_of_Water
FROM facts
WHERE area_water != 0
ORDER BY Ratio_of_Water DESC;
Done.
name | Ratio_of_Water |
---|---|
British Indian Ocean Territory | 99.89 |
Virgin Islands | 81.88 |
Puerto Rico | 35.68 |
Bahamas, The | 27.88 |
Guinea-Bissau | 22.16 |
Malawi | 20.6 |
Netherlands | 18.41 |
Uganda | 18.23 |
Eritrea | 14.12 |
Liberia | 13.51 |
Bangladesh | 12.32 |
Gambia, The | 10.44 |
Taiwan | 10.34 |
Finland | 10.15 |
India | 9.55 |
Ethiopia | 9.44 |
Canada | 8.93 |
Sweden | 8.87 |
Colombia | 8.8 |
Brunei | 8.67 |
Guyana | 8.43 |
French Polynesia | 8.16 |
Nicaragua | 7.96 |
Burundi | 7.73 |
Iran | 7.07 |
United States | 6.76 |
Tanzania | 6.49 |
Vietnam | 6.38 |
Rwanda | 6.34 |
Estonia | 6.28 |
Norway | 6.03 |
Armenia | 5.18 |
Tunisia | 5.04 |
Uzbekistan | 4.92 |
Indonesia | 4.88 |
Suriname | 4.77 |
Albania | 4.7 |
Ghana | 4.61 |
Azerbaijan | 4.59 |
Togo | 4.23 |
Russia | 4.21 |
Kyrgyzstan | 4.08 |
Tonga | 4.02 |
Lithuania | 4.01 |
Ukraine | 4.01 |
West Bank | 3.75 |
Gabon | 3.74 |
Turkmenistan | 3.72 |
Hungary | 3.68 |
Latvia | 3.62 |
Romania | 3.57 |
Japan | 3.55 |
Burma | 3.41 |
Congo, Democratic Republic of the | 3.32 |
Venezuela | 3.29 |
Pakistan | 3.17 |
Hong Kong | 3.16 |
Solomon Islands | 3.15 |
Switzerland | 3.1 |
Moldova | 2.84 |
China | 2.82 |
Korea, South | 2.81 |
Poland | 2.7 |
Iceland | 2.67 |
Montenegro | 2.61 |
Nepal | 2.6 |
Botswana | 2.58 |
Laos | 2.53 |
Cambodia | 2.5 |
Italy | 2.39 |
Ecuador | 2.37 |
Germany | 2.34 |
Paraguay | 2.32 |
Belarus | 2.26 |
Bulgaria | 2.16 |
Papua New Guinea | 2.16 |
Senegal | 2.13 |
Israel | 2.12 |
Czech Republic | 2.05 |
Ireland | 1.98 |
Kenya | 1.93 |
Slovakia | 1.9 |
Brazil | 1.85 |
Tajikistan | 1.8 |
Benin | 1.78 |
Turkey | 1.78 |
Austria | 1.7 |
Chile | 1.63 |
Lebanon | 1.63 |
Mozambique | 1.63 |
Saint Lucia | 1.62 |
Somalia | 1.62 |
New Caledonia | 1.62 |
Mali | 1.61 |
Guatemala | 1.59 |
Argentina | 1.57 |
Denmark | 1.53 |
El Salvador | 1.52 |
Sri Lanka | 1.49 |
Jamaica | 1.46 |
Panama | 1.43 |
Singapore | 1.43 |
Nigeria | 1.41 |
Bolivia | 1.39 |
Cote d'Ivoire | 1.38 |
Spain | 1.26 |
Zambia | 1.23 |
Croatia | 1.1 |
Macedonia | 1.09 |
Mexico | 1.04 |
Zimbabwe | 1.0 |
Greece | 0.99 |
Cuba | 0.94 |
Madagascar | 0.94 |
Kazakhstan | 0.92 |
Swaziland | 0.92 |
Syria | 0.84 |
Belgium | 0.82 |
Australia | 0.76 |
Dominican Republic | 0.72 |
Belize | 0.7 |
United Kingdom | 0.69 |
Haiti | 0.68 |
Mongolia | 0.68 |
Uruguay | 0.68 |
Portugal | 0.67 |
Philippines | 0.61 |
Egypt | 0.6 |
Jordan | 0.6 |
Slovenia | 0.6 |
Cameroon | 0.57 |
France | 0.52 |
Mauritius | 0.49 |
Thailand | 0.43 |
Peru | 0.41 |
South Africa | 0.38 |
Malaysia | 0.36 |
Samoa | 0.35 |
Iraq | 0.22 |
Honduras | 0.18 |
Sierra Leone | 0.17 |
Burkina Faso | 0.15 |
Congo, Republic of the | 0.15 |
Namibia | 0.12 |
Cyprus | 0.11 |
Korea, North | 0.11 |
Djibouti | 0.09 |
Costa Rica | 0.08 |
Guinea | 0.06 |
Morocco | 0.06 |
Bosnia and Herzegovina | 0.02 |
Chad | None |
Niger | None |
%%sql
SELECT name, ROUND(((CAST(area_water AS FLOAT) / CAST(area AS FLOAT)) * 100), 2) AS Ratio_of_Water
FROM facts
WHERE area_water != 0 AND Ratio_of_Water > 50.00
ORDER BY Ratio_of_Water DESC;
Done.
name | Ratio_of_Water |
---|---|
British Indian Ocean Territory | 99.89 |
Virgin Islands | 81.88 |
%%sql
SELECT name, ROUND(((CAST(area_water AS FLOAT) / CAST(area AS FLOAT)) * 100), 2) AS Ratio_of_Water
FROM facts
WHERE area_water != 0 AND Ratio_of_Water > 50.00
ORDER BY Ratio_of_Water DESC;
%%sql
SELECT name, population_growth
FROM facts
ORDER BY population_growth DESC
LIMIT 10;
Done.
name | 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 |
%%sql
SELECT name AS 'Countries with higher death rate vs birth rate'
FROM facts
WHERE death_rate > birth_rate;
Done.
Countries with higher death rate vs birth rate |
---|
Austria |
Belarus |
Bosnia and Herzegovina |
Bulgaria |
Croatia |
Czech Republic |
Estonia |
Germany |
Greece |
Hungary |
Italy |
Japan |
Latvia |
Lithuania |
Moldova |
Monaco |
Poland |
Portugal |
Romania |
Russia |
Serbia |
Slovenia |
Ukraine |
Saint Pierre and Miquelon |
%%sql
SELECT name, ROUND((CAST(population AS FLOAT) / CAST(area AS FLOAT)), 2) AS pop_per_area
FROM facts
ORDER BY pop_per_area DESC;
Done.
name | pop_per_area |
---|---|
Macau | 21168.96 |
Monaco | 15267.5 |
Singapore | 8141.28 |
Hong Kong | 6445.04 |
Gaza Strip | 5191.82 |
Gibraltar | 4876.33 |
Bahrain | 1771.86 |
Maldives | 1319.64 |
Malta | 1310.02 |
Bermuda | 1299.93 |
Sint Maarten | 1167.32 |
Bangladesh | 1138.07 |
Guernsey | 847.18 |
Jersey | 838.74 |
Barbados | 675.82 |
Mauritius | 656.78 |
Taiwan | 650.78 |
Aruba | 623.12 |
Lebanon | 594.68 |
Saint Martin | 588.04 |
San Marino | 541.31 |
Korea, South | 492.53 |
Rwanda | 480.74 |
West Bank | 475.32 |
Nauru | 454.29 |
Tuvalu | 418.04 |
Netherlands | 407.96 |
Marshall Islands | 398.85 |
Israel | 387.55 |
Burundi | 386.0 |
India | 380.77 |
Belgium | 370.94 |
Haiti | 364.33 |
Comoros | 349.43 |
Philippines | 336.66 |
Sri Lanka | 336.13 |
Japan | 335.84 |
Curacao | 334.25 |
Grenada | 321.78 |
Guam | 297.4 |
El Salvador | 291.88 |
Vietnam | 284.86 |
American Samoa | 273.08 |
Jamaica | 268.42 |
Saint Lucia | 266.11 |
Saint Vincent and the Grenadines | 263.82 |
United Kingdom | 263.08 |
Puerto Rico | 260.92 |
Pakistan | 250.08 |
Trinidad and Tobago | 238.37 |
Liechtenstein | 235.15 |
Germany | 226.47 |
British Virgin Islands | 221.55 |
Luxembourg | 220.52 |
Dominican Republic | 215.3 |
Nepal | 214.37 |
Cayman Islands | 212.47 |
Antigua and Barbuda | 209.13 |
Korea, North | 207.26 |
Italy | 205.27 |
Seychelles | 203.14 |
Sao Tome and Principe | 201.25 |
Saint Kitts and Nevis | 198.99 |
Switzerland | 196.76 |
Nigeria | 196.55 |
Qatar | 189.44 |
Andorra | 182.86 |
Anguilla | 180.42 |
Gambia, The | 174.13 |
Kosovo | 171.85 |
Kuwait | 156.5 |
Uganda | 153.92 |
Isle of Man | 153.05 |
Malawi | 151.62 |
Micronesia, Federated States of | 149.88 |
Tonga | 142.57 |
China | 142.49 |
Guatemala | 137.01 |
Cabo Verde | 135.38 |
Czech Republic | 134.97 |
Indonesia | 134.41 |
Togo | 133.0 |
Thailand | 132.48 |
Kiribati | 130.35 |
Denmark | 129.52 |
Cyprus | 128.55 |
Akrotiri | 127.64 |
Poland | 123.33 |
Dhekelia | 120.77 |
European Union | 118.84 |
Portugal | 117.55 |
Azerbaijan | 112.94 |
Northern Mariana Islands | 112.81 |
Tokelau | 111.42 |
Slovakia | 111.04 |
Ghana | 110.37 |
Wallis and Futuna | 109.95 |
Hungary | 106.39 |
Albania | 105.37 |
Moldova | 104.78 |
France | 103.38 |
Austria | 103.32 |
Armenia | 102.76 |
Turkey | 101.35 |
Cuba | 99.51 |
Dominica | 98.01 |
Slovenia | 97.84 |
Spain | 95.27 |
Costa Rica | 94.21 |
Benin | 92.78 |
Serbia | 92.63 |
Malaysia | 92.51 |
Syria | 92.15 |
Romania | 90.89 |
Jordan | 90.86 |
Ethiopia | 90.07 |
Egypt | 88.36 |
Cambodia | 86.77 |
Iraq | 84.54 |
Burma | 83.24 |
Timor-Leste | 82.77 |
Swaziland | 82.68 |
Sierra Leone | 81.95 |
Greece | 81.66 |
Macedonia | 81.52 |
Kenya | 79.13 |
Croatia | 78.89 |
Honduras | 78.03 |
Bosnia and Herzegovina | 75.53 |
Morocco | 74.62 |
Brunei | 74.53 |
Ukraine | 73.61 |
Cote d'Ivoire | 72.24 |
Senegal | 71.04 |
Georgia | 70.75 |
Samoa | 69.86 |
Ireland | 69.62 |
United Arab Emirates | 69.14 |
Burkina Faso | 69.04 |
French Polynesia | 67.84 |
Tunisia | 67.46 |
Uzbekistan | 65.27 |
Bulgaria | 64.82 |
Lesotho | 64.16 |
Mexico | 61.97 |
Norfolk Island | 61.39 |
Tajikistan | 56.85 |
Ecuador | 55.96 |
Eritrea | 55.51 |
Virgin Islands | 54.23 |
Tanzania | 53.89 |
Turks and Caicos Islands | 53.04 |
Montserrat | 51.38 |
Yemen | 50.64 |
Afghanistan | 49.93 |
Cameroon | 49.93 |
Fiji | 49.76 |
Iran | 49.64 |
Panama | 48.49 |
Guinea | 47.91 |
Guinea-Bissau | 47.78 |
Montenegro | 46.85 |
Palau | 46.33 |
Belarus | 46.19 |
Nicaragua | 45.32 |
Lithuania | 44.17 |
South Africa | 44.03 |
Cocos (Keeling) Islands | 42.57 |
Cook Islands | 41.69 |
Colombia | 41.04 |
Madagascar | 40.56 |
Liberia | 37.67 |
Zimbabwe | 36.42 |
Faroe Islands | 36.03 |
Djibouti | 35.7 |
Congo, Democratic Republic of the | 33.85 |
United States | 32.7 |
Venezuela | 32.1 |
Mozambique | 31.65 |
Latvia | 30.76 |
Laos | 29.19 |
Kyrgyzstan | 28.33 |
Estonia | 27.98 |
Equatorial Guinea | 26.41 |
Saint Helena, Ascension, and Tristan da Cunha | 25.31 |
Brazil | 23.99 |
Peru | 23.69 |
Bahamas, The | 23.39 |
Saint Pierre and Miquelon | 23.38 |
Chile | 23.16 |
Vanuatu | 22.34 |
Sweden | 21.77 |
Solomon Islands | 21.54 |
Zambia | 20.02 |
Sudan | 19.4 |
Bhutan | 19.32 |
Uruguay | 18.96 |
South Sudan | 18.69 |
Paraguay | 16.68 |
Somalia | 16.65 |
Algeria | 16.6 |
New Zealand | 16.58 |
Finland | 16.2 |
Norway | 16.08 |
Angola | 15.74 |
Argentina | 15.62 |
Belize | 15.13 |
New Caledonia | 14.62 |
Papua New Guinea | 14.42 |
Congo, Republic of the | 13.9 |
Mali | 13.67 |
Saudi Arabia | 12.91 |
Christmas Island | 11.33 |
Turkmenistan | 10.72 |
Oman | 10.62 |
Bolivia | 9.83 |
Central African Republic | 8.65 |
Russia | 8.33 |
Kazakhstan | 6.66 |
Gabon | 6.37 |
Niue | 4.58 |
Botswana | 3.75 |
Libya | 3.64 |
Suriname | 3.54 |
Canada | 3.52 |
Mauritania | 3.49 |
Guyana | 3.42 |
Iceland | 3.22 |
Australia | 2.94 |
Namibia | 2.68 |
Western Sahara | 2.15 |
Mongolia | 1.91 |
Pitcairn Islands | 1.02 |
Falkland Islands (Islas Malvinas) | 0.28 |
Greenland | 0.03 |
Svalbard | 0.03 |
Chad | None |
Niger | None |
Holy See (Vatican City) | None |
Ashmore and Cartier Islands | None |
Coral Sea Islands | None |
Heard Island and McDonald Islands | None |
Clipperton Island | None |
French Southern and Antarctic Lands | None |
Saint Barthelemy | None |
Bouvet Island | None |
Jan Mayen | None |
British Indian Ocean Territory | None |
South Georgia and South Sandwich Islands | None |
Navassa Island | None |
Wake Island | None |
United States Pacific Island Wildlife Refuges | None |
Antarctica | None |
Paracel Islands | None |
Spratly Islands | None |
Arctic Ocean | None |
Atlantic Ocean | None |
Indian Ocean | None |
Pacific Ocean | None |
Southern Ocean | None |
World | None |