In [1]:
%%capture
%load_ext sql
%sql sqlite:///factbook.db
Out[1]:
'Connected: [email protected]'
In [7]:
%%sql
SELECT *
FROM sqlite_master
WHERE type = 'table';
Done.
Out[7]:
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)
In [10]:
%%sql
SELECT *
FROM facts
LIMIT 5
Done.
Out[10]:
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
In [12]:
%%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.
Out[12]:
Minimum Population Maximum Population Minimum population growth Maximum population growth
0 7256490011 0.0 4.02
In [17]:
%%sql
SELECT name, population
  FROM facts
 ORDER BY population DESC
 LIMIT 25;
Done.
Out[17]:
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
In [16]:
%%sql
SELECT name, population
  FROM facts
 ORDER BY population
 LIMIT 25;
Done.
Out[16]:
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
In [19]:
%%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.
Out[19]:
Minimum Population Maximum Population Minimum population growth Maximum population growth
0 1367485388 0.0 4.02
In [20]:
%%sql
SELECT AVG(population) AS avg_pop,
       AVG(area) AS avg_area
  FROM facts;
Done.
Out[20]:
avg_pop avg_area
62094928.32231405 555093.546184739
In [15]:
%%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.
Out[15]:
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
In [31]:
%%sql
SELECT name, MAX(population)
  FROM facts
 WHERE (name != 'World' AND name != 'European Union');
Done.
Out[31]:
name MAX(population)
China 1367485388
In [32]:
%%sql
SELECT name, MAX(population_growth)
  FROM facts
 WHERE (name != 'World' AND name != 'European Union');
Done.
Out[32]:
name MAX(population_growth)
South Sudan 4.02
In [46]:
%%capture
%load_ext sql
%sql sqlite:///factbook.db
Out[46]:
'Connected: [email protected]'
In [73]:
%%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.
Out[73]:
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
In [74]:
%%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.
Out[74]:
name Ratio_of_Water
British Indian Ocean Territory 99.89
Virgin Islands 81.88
In [ ]:
%%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;
In [6]:
%%sql
SELECT name, population_growth
  FROM facts
 ORDER BY population_growth DESC
 LIMIT 10;
Done.
Out[6]:
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
In [8]:
%%sql
SELECT name AS 'Countries with higher death rate vs birth rate'
  FROM facts
 WHERE death_rate > birth_rate;
Done.
Out[8]:
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
In [14]:
%%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.
Out[14]:
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
In [ ]: