Run this if SQL is not instatlled: !conda install -yc conda-forge ipython-sql

World Facts by the CIA

image.png

1

Introduction

The CIA World Factbook is a collection of statistics about all of the countries on Earth. The Factbook contains demographic information like the following:

  • population - Population in various countries around the world.
  • _populationgrowth - The annual growth rate in the different countries around the workd
  • area - The land and water area around the world.

The data has been captured in the factbook.db which will be opened and analyzed using SQLite.

In [44]:
%%capture
%load_ext sql
%sql sqlite:///factbook.db
In [45]:
%%sql
SELECT *
  FROM sqlite_master
 WHERE type='table';
 * sqlite:///factbook.db
Done.
Out[45]:
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 [46]:
%%sql

SELECT *
FROM facts
 * sqlite:///factbook.db
Done.
Out[46]:
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
6 ac Antigua and Barbuda 442 442 0 92436 1.24 15.85 5.69 2.21
7 ar Argentina 2780400 2736690 43710 43431886 0.93 16.64 7.33 0.0
8 am Armenia 29743 28203 1540 3056382 0.15 13.61 9.34 5.8
9 as Australia 7741220 7682300 58920 22751014 1.07 12.15 7.14 5.65
10 au Austria 83871 82445 1426 8665550 0.55 9.41 9.42 5.56
11 aj Azerbaijan 86600 82629 3971 9780780 0.96 16.64 7.07 0.0
12 bf Bahamas, The 13880 10010 3870 324597 0.85 15.5 7.05 0.0
13 ba Bahrain 760 760 0 1346613 2.41 13.66 2.69 13.09
14 bg Bangladesh 148460 130170 18290 168957745 1.6 21.14 5.61 0.46
15 bb Barbados 430 430 0 290604 0.31 11.87 8.44 0.3
16 bo Belarus 207600 202900 4700 9589689 0.2 10.7 13.36 0.7
17 be Belgium 30528 30278 250 11323973 0.76 11.41 9.63 5.87
18 bh Belize 22966 22806 160 347369 1.87 24.68 5.97 0.0
19 bn Benin 112622 110622 2000 10448647 2.78 36.02 8.21 0.0
20 bt Bhutan 38394 38394 0 741919 1.11 17.78 6.69 0.0
21 bl Bolivia 1098581 1083301 15280 10800882 1.56 22.76 6.52 0.62
22 bk Bosnia and Herzegovina 51197 51187 10 3867055 0.13 8.87 9.75 0.38
23 bc Botswana 581730 566730 15000 2182719 1.21 20.96 13.39 4.56
24 br Brazil 8515770 8358140 157630 204259812 0.77 14.46 6.58 0.14
25 bx Brunei 5765 5265 500 429646 1.62 17.32 3.52 2.43
26 bu Bulgaria 110879 108489 2390 7186893 0.58 8.92 14.44 0.29
27 uv Burkina Faso 274200 273800 400 18931686 3.03 42.03 11.72 0.0
28 bm Burma 676578 653508 23070 56320206 1.01 18.39 7.96 0.28
29 by Burundi 27830 25680 2150 10742276 3.28 42.01 9.27 0.0
30 cb Cambodia 181035 176515 4520 15708756 1.58 23.83 7.68 0.32
31 cm Cameroon 475440 472710 2730 23739218 2.59 36.17 10.11 0.15
32 ca Canada 9984670 9093507 891163 35099836 0.75 10.28 8.42 5.66
33 cv Cabo Verde 4033 4033 0 545993 1.36 20.33 6.11 0.63
34 ct Central African Republic 622984 622984 0 5391539 2.13 35.08 13.8 0.0
35 cd Chad None 1259200 24800 11631456 1.89 36.6 14.28 3.45
36 ci Chile 756102 743812 12290 17508260 0.82 13.83 6.0 0.34
37 ch China 9596960 9326410 270550 1367485388 0.45 12.49 7.53 0.44
38 co Colombia 1138910 1038700 100210 46736728 1.04 16.47 5.4 0.64
39 cn Comoros 2235 2235 0 780971 1.77 27.84 7.57 2.53
40 cg Congo, Democratic Republic of the 2344858 2267048 77810 79375136 2.45 34.88 10.07 0.27
41 cf Congo, Republic of the 342000 341500 500 4755097 2.0 35.85 10.0 5.9
42 cs Costa Rica 51100 51060 40 4814144 1.22 15.91 4.55 0.83
43 iv Cote d'Ivoire 322463 318003 4460 23295302 1.91 28.67 9.55 0.0
44 hr Croatia 56594 55974 620 4464844 0.13 9.45 12.18 1.39
45 cu Cuba 110860 109820 1040 11031433 0.15 9.9 7.72 3.66
46 cy Cyprus 9251 9241 10 1189197 1.43 11.41 6.62 9.48
47 ez Czech Republic 78867 77247 1620 10644842 0.16 9.63 10.34 2.33
48 da Denmark 43094 42434 660 5581503 0.22 10.27 10.25 2.2
49 dj Djibouti 23200 23180 20 828324 2.2 23.65 7.73 6.06
50 do Dominica 751 751 0 73607 0.21 15.41 7.91 5.38
51 dr Dominican Republic 48670 48320 350 10478756 1.23 18.73 4.55 1.91
52 ec Ecuador 283561 276841 6720 15868396 1.35 18.51 5.06 0.0
53 eg Egypt 1001450 995450 6000 88487396 1.79 22.9 4.77 0.19
54 es El Salvador 21041 20721 320 6141350 0.25 16.46 5.69 8.28
55 ek Equatorial Guinea 28051 28051 0 740743 2.51 33.31 8.19 0.0
56 er Eritrea 117600 101000 16600 6527689 2.25 30.0 7.52 0.0
57 en Estonia 45228 42388 2840 1265420 0.55 10.51 12.4 3.6
58 et Ethiopia 1104300 None 104300 99465819 2.89 37.27 8.19 0.22
59 fj Fiji 18274 18274 0 909389 0.67 19.43 6.04 6.75
60 fi Finland 338145 303815 34330 5476922 0.4 10.72 9.83 3.1
61 fr France 643801 640427 3374 66553766 0.43 12.38 9.16 1.09
62 gb Gabon 267667 257667 10000 1705336 1.93 34.49 13.12 2.03
63 ga Gambia, The 11300 10120 1180 1967709 2.16 30.86 7.15 2.12
64 gg Georgia 69700 69700 0 4931226 0.08 12.74 10.82 2.7
65 gm Germany 357022 348672 8350 80854408 0.17 8.47 11.42 1.24
66 gh Ghana 238533 227533 11000 26327649 2.18 31.09 7.22 2.02
67 gr Greece 131957 130647 1310 10775643 0.01 8.66 11.09 2.32
68 gj Grenada 344 344 0 110694 0.48 16.03 8.08 3.13
69 gt Guatemala 108889 107159 1730 14918999 1.82 24.89 4.77 1.97
70 gv Guinea 245857 245717 140 11780162 2.63 35.74 9.46 0.0
71 pu Guinea-Bissau 36125 28120 8005 1726170 1.91 33.38 14.33 0.0
72 gy Guyana 214969 196849 18120 735222 0.02 15.59 7.32 8.06
73 ha Haiti 27750 27560 190 10110019 1.17 22.31 7.83 2.76
74 ho Honduras 112090 111890 200 8746673 1.68 23.14 5.17 1.16
75 hu Hungary 93028 89608 3420 9897541 0.22 9.16 12.73 1.33
76 ic Iceland 103000 100250 2750 331918 1.21 13.91 6.28 4.43
77 in India 3287263 2973193 314070 1251695584 1.22 19.55 7.32 0.04
78 id Indonesia 1904569 1811569 93000 255993674 0.92 16.72 6.37 1.16
79 ir Iran 1648195 1531595 116600 81824270 1.2 17.99 5.94 0.07
80 iz Iraq 438317 437367 950 37056169 2.93 31.45 3.77 1.62
81 ei Ireland 70273 68883 1390 4892305 1.25 14.84 6.48 4.09
82 is Israel 20770 20330 440 8049314 1.56 18.48 5.15 2.24
83 it Italy 301340 294140 7200 61855120 0.27 8.74 10.19 4.1
84 jm Jamaica 10991 10831 160 2950210 0.68 18.16 6.7 4.66
85 ja Japan 377915 364485 13430 126919659 0.16 7.93 9.51 0.0
86 jo Jordan 89342 88802 540 8117564 0.83 25.37 3.79 13.24
87 kz Kazakhstan 2724900 2699700 25200 18157122 1.14 19.15 8.21 0.41
88 ke Kenya 580367 569140 11227 45925301 1.93 26.4 6.89 0.22
89 kr Kiribati 811 811 0 105711 1.15 21.46 7.12 2.87
90 kn Korea, North 120538 120408 130 24983205 0.53 14.52 9.21 0.04
91 ks Korea, South 99720 96920 2800 49115196 0.14 8.19 6.75 0.0
92 kv Kosovo 10887 10887 0 1870981 None None None None
93 ku Kuwait 17818 17818 0 2788534 1.62 19.91 2.18 1.58
94 kg Kyrgyzstan 199951 191801 8150 5664939 1.11 22.98 6.65 5.22
95 la Laos 236800 230800 6000 6911544 1.55 24.25 7.63 1.09
96 lg Latvia 64589 62249 2340 1986705 1.06 10.0 14.31 6.26
97 le Lebanon 10400 10230 170 6184701 0.86 14.59 4.88 1.1
98 lt Lesotho 30355 30355 0 1947701 0.32 25.47 14.89 7.36
99 li Liberia 111369 96320 15049 4195666 2.47 34.41 9.69 0.0
100 ly Libya 1759540 1759540 0 6411776 2.23 18.03 3.58 7.8
101 ls Liechtenstein 160 160 0 37624 0.84 10.45 7.12 5.08
102 lh Lithuania 65300 62680 2620 2884433 1.04 10.1 14.27 6.27
103 lu Luxembourg 2586 2586 0 570252 2.13 11.37 7.24 17.16
104 mk Macedonia 25713 25433 280 2096015 0.2 11.55 9.08 0.48
105 ma Madagascar 587041 581540 5501 23812681 2.58 32.61 6.81 0.0
106 mi Malawi 118484 94080 24404 17964697 3.32 41.56 8.41 0.0
107 my Malaysia 329847 328657 1190 30513848 1.44 19.71 5.03 0.33
108 mv Maldives 298 298 0 393253 0.08 15.75 3.89 12.68
109 ml Mali 1240192 1220190 20002 16955536 2.98 44.99 12.89 2.26
110 mt Malta 316 316 0 413965 0.31 10.18 9.09 1.98
111 rm Marshall Islands 181 181 0 72191 1.66 25.6 4.21 4.83
112 mr Mauritania 1030700 1030700 0 3596702 2.23 31.34 8.2 0.83
113 mp Mauritius 2040 2030 10 1339827 0.64 13.29 6.91 0.0
114 mx Mexico 1964375 1943945 20430 121736809 1.18 18.78 5.26 1.68
115 fm Micronesia, Federated States of 702 702 0 105216 0.46 20.54 4.23 20.93
116 md Moldova 33851 32891 960 3546847 1.03 12.0 12.59 9.67
117 mn Monaco 2 2 0 30535 0.12 6.65 9.24 3.83
118 mg Mongolia 1564116 1553556 10560 2992908 1.31 20.25 6.35 0.84
119 mj Montenegro 13812 13452 360 647073 0.42 10.42 9.43 None
120 mo Morocco 446550 446300 250 33322699 1.0 18.2 4.81 3.36
121 mz Mozambique 799380 786380 13000 25303113 2.45 38.58 12.1 1.98
122 wa Namibia 824292 823290 1002 2212307 0.59 19.8 13.91 0.0
123 nr Nauru 21 21 0 9540 0.55 24.95 5.87 13.63
124 np Nepal 147181 143351 3830 31551305 1.79 20.64 6.56 3.86
125 nl Netherlands 41543 33893 7650 16947904 0.41 10.83 8.66 1.95
126 nz New Zealand 267710 267710 None 4438393 0.82 13.33 7.36 2.21
127 nu Nicaragua 130370 119990 10380 5907881 1.0 18.03 5.08 3.0
128 ng Niger None 1266700 300 18045729 3.25 45.45 12.42 0.56
129 ni Nigeria 923768 910768 13000 181562056 2.45 37.64 12.9 0.22
130 no Norway 323802 304282 19520 5207689 1.13 12.14 8.12 7.25
131 mu Oman 309500 309500 0 3286936 2.07 24.44 3.36 0.43
132 pk Pakistan 796095 770875 25220 199085847 1.46 22.58 6.49 1.54
133 ps Palau 459 459 0 21265 0.38 11.05 7.99 0.71
134 pm Panama 75420 74340 1080 3657024 1.32 18.32 4.81 0.28
135 pp Papua New Guinea 462840 452860 9980 6672429 1.78 24.38 6.53 0.0
136 pa Paraguay 406752 397302 9450 6783272 1.16 16.37 4.68 0.07
137 pe Peru 1285216 1279996 5220 30444999 0.97 18.28 6.01 2.53
138 rp Philippines 300000 298170 1830 100998376 1.61 24.27 6.11 2.09
139 pl Poland 312685 304255 8430 38562189 0.09 9.74 10.19 0.46
140 po Portugal 92090 91470 620 10825309 0.09 9.27 11.02 2.67
141 qa Qatar 11586 11586 0 2194817 3.07 9.84 1.53 22.39
142 ro Romania 238391 229891 8500 21666350 0.3 9.14 11.9 0.24
143 rs Russia 17098242 16377742 720500 142423773 0.04 11.6 13.69 1.69
144 rw Rwanda 26338 24668 1670 12661733 2.56 33.75 8.96 0.85
145 sc Saint Kitts and Nevis 261 261 0 51936 0.76 13.5 7.09 1.21
146 st Saint Lucia 616 606 10 163922 0.34 13.7 7.42 2.93
147 vc Saint Vincent and the Grenadines 389 389 0 102627 0.28 13.57 7.18 9.17
148 ws Samoa 2831 2821 10 197773 0.58 20.87 5.32 9.78
149 sm San Marino 61 61 0 33020 0.82 8.63 8.45 8.03
150 tp Sao Tome and Principe 964 964 0 194006 1.84 34.23 7.24 8.63
151 sa Saudi Arabia 2149690 2149690 0 27752316 1.46 18.51 3.33 0.55
152 sg Senegal 196722 192530 4192 13975834 2.45 34.52 8.46 1.59
153 ri Serbia 77474 77474 0 7176794 0.46 9.08 13.66 0.0
154 se Seychelles 455 455 0 92430 0.83 14.19 6.89 1.0
155 sl Sierra Leone 71740 71620 120 5879098 2.35 37.03 10.81 2.77
156 sn Singapore 697 687 10 5674472 1.89 8.27 3.43 14.05
157 lo Slovakia 49035 48105 930 5445027 0.02 9.91 9.74 0.04
158 si Slovenia 20273 20151 122 1983412 0.26 8.42 11.37 0.37
159 bp Solomon Islands 28896 27986 910 622469 2.02 25.77 3.85 1.75
160 so Somalia 637657 627337 10320 10616380 1.83 40.45 13.62 8.49
161 sf South Africa 1219090 1214470 4620 53675563 1.33 20.75 9.91 2.42
162 od South Sudan 644329 None None 12042910 4.02 36.91 8.18 11.47
163 sp Spain 505370 498980 6390 48146134 0.89 9.64 9.04 8.31
164 ce Sri Lanka 65610 64630 980 22053488 0.84 15.85 6.11 1.35
165 su Sudan 1861484 None None 36108853 1.72 29.19 7.66 4.29
166 ns Suriname 163820 156000 7820 579633 1.08 16.34 6.13 0.56
167 wz Swaziland 17364 17204 160 1435613 1.11 24.67 13.56 0.0
168 sw Sweden 450295 410335 39960 9801616 0.8 11.99 9.4 5.42
169 sz Switzerland 41277 39997 1280 8121830 0.71 10.5 8.13 4.74
170 sy Syria 185180 183630 1550 17064854 0.16 22.17 4.0 19.79
171 ti Tajikistan 144100 141510 2590 8191958 1.71 24.38 6.18 1.15
172 tz Tanzania 947300 885800 61500 51045882 2.79 36.39 8.0 0.54
173 th Thailand 513120 510890 2230 67976405 0.34 11.19 7.8 0.0
174 tt Timor-Leste 14874 14874 0 1231116 2.42 34.16 6.1 3.86
175 to Togo 56785 54385 2400 7552318 2.69 34.13 7.26 0.0
176 tn Tonga 747 717 30 106501 0.03 23.0 4.85 17.84
177 td Trinidad and Tobago 5128 5128 0 1222363 0.13 13.46 8.56 6.25
178 ts Tunisia 163610 155360 8250 11037225 0.89 16.64 5.98 1.73
179 tu Turkey 783562 769632 13930 79414269 1.26 16.33 5.88 2.16
180 tx Turkmenistan 488100 469930 18170 5231422 1.14 19.4 6.13 1.84
181 tv Tuvalu 26 26 0 10869 0.82 23.74 8.74 6.81
182 ug Uganda 241038 197100 43938 37101745 3.24 43.79 10.69 0.74
183 up Ukraine 603550 579330 24220 44429471 0.6 10.72 14.46 2.25
184 ae United Arab Emirates 83600 83600 0 5779760 2.58 15.43 1.97 12.36
185 uk United Kingdom 243610 241930 1680 64088222 0.54 12.17 9.35 2.54
186 us United States 9826675 9161966 664709 321368864 0.78 12.49 8.15 3.86
187 uy Uruguay 176215 175015 1200 3341893 0.27 13.07 9.45 0.9
188 uz Uzbekistan 447400 425400 22000 29199942 0.93 17.0 5.3 2.37
189 nh Vanuatu 12189 12189 0 272264 1.95 25.04 4.09 1.47
190 vt Holy See (Vatican City) 0 0 0 842 0.0 None None None
191 ve Venezuela 912050 882050 30000 29275460 1.39 19.16 5.31 0.0
192 vm Vietnam 331210 310070 21140 94348835 0.97 15.96 5.93 0.3
193 ym Yemen 527968 527968 0 26737317 2.47 29.98 6.28 1.0
194 za Zambia 752618 743398 9220 15066266 2.88 42.13 12.67 0.68
195 zi Zimbabwe 390757 386847 3910 14229541 2.21 32.26 10.13 0.0
196 tw Taiwan 35980 32260 3720 23415126 0.23 8.47 7.11 0.89
197 ee European Union 4324782 None None 513949445 0.25 10.2 10.2 2.5
198 at Ashmore and Cartier Islands 5 5 0 None None None None None
199 kt Christmas Island 135 135 0 1530 1.11 None None None
200 ck Cocos (Keeling) Islands 14 14 0 596 0.0 None None None
201 cr Coral Sea Islands 3 3 0 None None None None None
202 hm Heard Island and McDonald Islands 412 412 0 None None None None None
203 nf Norfolk Island 36 36 0 2210 0.01 None None None
204 hk Hong Kong 1108 1073 35 7141106 0.38 9.23 7.07 1.68
205 mc Macau 28 28 0 592731 0.8 8.88 4.22 3.37
206 fo Faroe Islands 1393 1393 0 50196 0.51 13.77 8.71 0.0
207 gl Greenland 2166086 2166086 None 57733 0.0 14.48 8.49 5.98
208 ip Clipperton Island 6 6 0 None None None None None
209 fp French Polynesia 4167 3827 340 282703 0.94 15.22 5.02 0.84
210 fs French Southern and Antarctic Lands None None None None None None None None
211 nc New Caledonia 18575 18275 300 271615 1.38 15.33 5.52 4.01
212 tb Saint Barthelemy None None None 7237 None None None None
213 rn Saint Martin 54 54 None 31754 None None None None
214 sb Saint Pierre and Miquelon 242 242 0 5657 1.08 7.42 9.72 8.49
215 wf Wallis and Futuna 142 142 0 15613 0.33 13.45 5.06 5.06
216 aa Aruba 180 180 0 112162 1.33 12.56 8.18 8.92
217 cc Curacao 444 444 0 148406 0.43 13.8 8.2 1.4
218 sk Sint Maarten 34 34 0 39689 1.51 13.0 4.51 6.63
219 cw Cook Islands 236 236 0 9838 2.95 14.33 8.03 None
220 ne Niue 260 260 0 1190 0.03 None None None
221 tl Tokelau 12 12 0 1337 0.01 None None None
222 bv Bouvet Island 49 49 0 None None None None None
223 jn Jan Mayen 377 377 0 None None None None None
224 sv Svalbard 62045 62045 0 1872 0.03 None None None
225 ax Akrotiri 123 None None 15700 None None None None
226 av Anguilla 91 91 0 16418 2.03 12.67 4.57 12.18
227 bd Bermuda 54 54 0 70196 0.5 11.33 8.23 1.88
228 io British Indian Ocean Territory 54400 60 54340 None None None None None
229 vi British Virgin Islands 151 151 0 33454 2.32 10.91 4.99 17.28
230 cj Cayman Islands 264 264 0 56092 2.1 12.11 5.53 14.4
231 dx Dhekelia 130 None None 15700 None None None None
232 fk Falkland Islands (Islas Malvinas) 12173 12173 0 3361 0.01 10.9 4.9 None
233 gi Gibraltar 6 6 0 29258 0.24 14.08 8.37 3.28
234 gk Guernsey 78 78 0 66080 0.34 9.84 8.78 2.32
235 je Jersey 116 116 0 97294 0.8 11.91 7.68 3.76
236 im Isle of Man 572 572 0 87545 0.76 11.1 10.06 6.56
237 mh Montserrat 102 102 0 5241 0.5 11.26 6.3 0.0
238 pc Pitcairn Islands 47 47 0 48 0.0 None None None
239 sh Saint Helena, Ascension, and Tristan da Cunha 308 122 0 7795 0.24 9.88 7.44 0.0
240 sx South Georgia and South Sandwich Islands 3903 3903 0 None None None None None
241 tk Turks and Caicos Islands 948 948 0 50280 2.3 16.13 3.1 9.94
242 aq American Samoa 199 199 0 54343 0.3 22.89 4.75 21.13
243 gq Guam 544 544 0 161785 0.54 16.82 5.12 6.34
244 bq Navassa Island 5 5 0 None None None None None
245 cq Northern Mariana Islands 464 464 0 52344 2.18 18.32 3.71 7.16
246 rq Puerto Rico 13791 8870 4921 3598357 0.6 10.86 8.67 8.15
247 vq Virgin Islands 1910 346 1564 103574 0.59 10.31 8.54 7.67
248 wq Wake Island 6 6 0 None None None None None
249 um United States Pacific Island Wildlife Refuges None None None None None None None None
250 ay Antarctica None 280000 None 0 None None None None
251 gz Gaza Strip 360 360 0 1869055 2.81 31.11 3.04 0.0
252 pf Paracel Islands 7 7 0 None None None None None
253 pg Spratly Islands 5 5 0 None None None None None
254 we West Bank 5860 5640 220 2785366 1.95 22.99 3.5 0.0
255 wi Western Sahara 266000 266000 0 570866 2.82 30.24 8.34 None
256 xq Arctic Ocean None None None None None None None None
257 zh Atlantic Ocean None None None None None None None None
258 xo Indian Ocean None None None None None None None None
259 zn Pacific Ocean None None None None None None None None
260 oo Southern Ocean None None None None None None None None
261 xx World None None None 7256490011 1.08 18.6 7.8 None

2

Being Human...

Analysis on human population and its spread.

Population would be an interesting column to analyze. Given the considerable increase in world population and how it has affected the environment. It would be interesting to know which country is the most populated and which country has the most population growth rate.

To begin with we need to know what the maximum population and population growth look like.

In [47]:
%%sql

SELECT MIN(population) AS 'Least Population', 
       MAX(population) AS 'Most Population', 
       MIN(population_growth) AS 'Least Population Growth',
       MAX(population_growth) AS 'Most Population Growth'
FROM facts
 * sqlite:///factbook.db
Done.
Out[47]:
Least Population Most Population Least Population Growth Most Population Growth
0 7256490011 0.0 4.02

The values of Least and Most Population seem to be an oddity. It would be interesting to find the countries that these values relate to.

In [48]:
%%sql

SELECT name AS 'Least Populous Country'
FROM facts
WHERE population = 0
 * sqlite:///factbook.db
Done.
Out[48]:
Least Populous Country
Antarctica
In [49]:
%%sql

SELECT name AS 'Most Populous Country'
FROM facts
WHERE population = 7256490011
 * sqlite:///factbook.db
Done.
Out[49]:
Most Populous Country
World

The oddity has been explained. The database contains a record for the population of the entire world. This record will be skipped as the analysis continues. Let us try the same statistics after avoiding the World record.

In [50]:
%%sql

SELECT MIN(population) AS 'Least Population', 
       MAX(population) AS 'Most Population', 
       MIN(population_growth) AS 'Least Population Growth',
       MAX(population_growth) AS 'Most Population Growth'
FROM facts
WHERE name<>'World'
 * sqlite:///factbook.db
Done.
Out[50]:
Least Population Most Population Least Population Growth Most Population Growth
0 1367485388 0.0 4.02
In [51]:
%%sql

SELECT name AS 'Most Populous Country', 
       population AS 'Population', 
       population_growth AS 'Population Growth Rate'
FROM facts
WHERE population = (SELECT MAX(population)
                    FROM facts
                    WHERE name <> 'World')
 * sqlite:///factbook.db
Done.
Out[51]:
Most Populous Country Population Population Growth Rate
China 1367485388 0.45

While China is the most populous country in the world. Its growth rate seems quite low. It would be interesting to find out the population of the country with the highest growth rate.

In [52]:
%%sql

SELECT name AS 'Country with highest population growth rate', 
       population AS 'Population', 
       population_growth AS 'Population Growth Rate'
FROM facts
WHERE population_growth = (SELECT MAX(population_growth)
                    FROM facts
                    WHERE name <> 'World')
 * sqlite:///factbook.db
Done.
Out[52]:
Country with highest population growth rate Population Population Growth Rate
South Sudan 12042910 4.02

While China has a population that is 100 times more than South Sudan, its population growth rate is only a tenth of that of South Sudan. This raises the question of whether South Sudan might be an outlier with regards to population growth rate. An average of the population growth rate of the world should reveal a better understanding.bm

In [53]:
%%sql
  
SELECT ROUND(AVG(population_growth),3) AS 'Average Population Growth'
FROM facts
WHERE name<>'World'
 * sqlite:///factbook.db
Done.
Out[53]:
Average Population Growth
1.201

South Sudan's growth rate seems to be higher than average. It would be interesting to find out how many countries have above average growth rates.

In [54]:
%%sql

SELECT COUNT(*) AS 'Number of countries with more than average population growth'
FROM facts
WHERE population_growth > (SELECT AVG(population_growth)
                           FROM facts
                           WHERE name<>'World')
 * sqlite:///factbook.db
Done.
Out[54]:
Number of countries with more than average population growth
101

Since there are 101 countries that have more than the average growth rate, further analysis could help to identify those countries.

In [55]:
%%sql

SELECT name AS 'Country Name', population_growth AS 'Population Growth Rate'
FROM facts
WHERE population_growth>(SELECT AVG(population_growth)
                         FROM facts)
ORDER BY population_growth DESC
LIMIT 30
 * sqlite:///factbook.db
Done.
Out[55]:
Country Name Population Growth Rate
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
Ethiopia 2.89
Zambia 2.88
Western Sahara 2.82
Gaza Strip 2.81
Tanzania 2.79
Angola 2.78
Benin 2.78
Togo 2.69
Guinea 2.63
Cameroon 2.59
Madagascar 2.58
United Arab Emirates 2.58
Rwanda 2.56
Equatorial Guinea 2.51
Liberia 2.47
Yemen 2.47
Congo, Democratic Republic of the 2.45
Mozambique 2.45
Nigeria 2.45
Senegal 2.45

More than a fifth of the countries in the above list are in Africa.

Further research on the reason behind this phenomenon reveals that a significant number of countries in Africa have high growth rates because of high fertility rates. Apparently, fertility rates take a dive when there is economic growth because of many reasons including couples choosing to have smaller families. Read more

3

Have you all got a place?

Analysis of Area vs. Population

Earlier the CIA World data revealed a record containing a population value greater than 7 billion. This was found to be the population of the world. The question to then analyze would be is whether the countries around the world have the area to sustain the population.

We could begin by finding out the average area and population around the world.

In [56]:
%%sql

SELECT ROUND(AVG(population),3) AS 'Average Population', 
       ROUND(AVG(area),3) AS 'Average Area'
FROM FACTS
WHERE name!='World'
 * sqlite:///factbook.db
Done.
Out[56]:
Average Population Average Area
32242666.568 555093.546

Next, we can identify those countries wherein the population is greater than the average but the area held by them are less than average i.e. Densely populated countries.

In [57]:
%%sql
--#Densely Populated countries in descending order of population to area ratio
SELECT name AS Country, ROUND((CAST(area AS FLOAT)/population),5) AS 'Area/Population_Ratio', population AS Population, area AS Area
FROM facts
WHERE (population > (SELECT AVG(population)
                    FROM facts
                    WHERE name!= 'World')
       AND
       area < (SELECT AVG(area)
               FROM facts
               WHERE name!='World')
      )
ORDER BY population/area DESC
 * sqlite:///factbook.db
Done.
Out[57]:
Country Area/Population_Ratio Population Area
Bangladesh 0.00088 168957745 148460
Korea, South 0.00203 49115196 99720
Philippines 0.00297 100998376 300000
Japan 0.00298 126919659 377915
Vietnam 0.00351 94348835 331210
United Kingdom 0.0038 64088222 243610
Germany 0.00442 80854408 357022
Italy 0.00487 61855120 301340
Uganda 0.0065 37101745 241038
Thailand 0.00755 67976405 513120
Poland 0.00811 38562189 312685
Spain 0.0105 48146134 505370
Iraq 0.01183 37056169 438317
Morocco 0.0134 33322699 446550

Bangladesh has been identified as the most densely populated country in the world. This statistic leads to another question which is, what is the average population to area ratio and how many countries have a ratio that is higher than the average?

In [58]:
%%sql

SELECT ROUND(AVG(population/area),3) AS 'Average Population/Area ratio'
FROM FACTS
 * sqlite:///factbook.db
Done.
Out[58]:
Average Population/Area ratio
419.148
In [59]:
%%sql
SELECT name AS Country, Population/Area AS 'Population/Area Ratio', population AS Population, 
       area AS Area, 
       ROUND((CAST(area AS FLOAT)/population),5) AS 'Area/Population Ratio'
FROM facts
WHERE population/area > (SELECT AVG(population/area)
                         FROM facts)
ORDER BY population/area DESC
 * sqlite:///factbook.db
Done.
Out[59]:
Country Population/Area Ratio Population Area Area/Population Ratio
Macau 21168 592731 28 5e-05
Monaco 15267 30535 2 7e-05
Singapore 8141 5674472 697 0.00012
Hong Kong 6445 7141106 1108 0.00016
Gaza Strip 5191 1869055 360 0.00019
Gibraltar 4876 29258 6 0.00021
Bahrain 1771 1346613 760 0.00056
Maldives 1319 393253 298 0.00076
Malta 1310 413965 316 0.00076
Bermuda 1299 70196 54 0.00077
Sint Maarten 1167 39689 34 0.00086
Bangladesh 1138 168957745 148460 0.00088
Guernsey 847 66080 78 0.00118
Jersey 838 97294 116 0.00119
Barbados 675 290604 430 0.00148
Mauritius 656 1339827 2040 0.00152
Taiwan 650 23415126 35980 0.00154
Aruba 623 112162 180 0.0016
Lebanon 594 6184701 10400 0.00168
Saint Martin 588 31754 54 0.0017
San Marino 541 33020 61 0.00185
Korea, South 492 49115196 99720 0.00203
Rwanda 480 12661733 26338 0.00208
West Bank 475 2785366 5860 0.0021
Nauru 454 9540 21 0.0022

From the earlier statistics we may have naturally assumed that Bangladesh would have been at the top of this list.

However, there are many countries around the world that have a population to area ratio that is higher than Bangldesh. The reason why these countries did not come up is because they have a population thats much less than average world population.

4

Born one day, Dead on another...

Exploring birth and death rates

Birth rates are good indicators for population growth rates. More births mean that the population is growing. Earlier it was found that many African countries had higher birth rates. We could confirm the finding by verifying the countries that have higher birth rates than death rates.

In [60]:
%%sql
SELECT ROUND(AVG(birth_rate), 3) AS 'Average Birth Rate'
FROM facts
 * sqlite:///factbook.db
Done.
Out[60]:
Average Birth Rate
19.329
In [61]:
%%sql
SELECT name AS Country, 
       birth_rate AS 'Birth Rate', 
       death_rate AS 'Death Rate',
       ROUND(birth_rate - death_rate, 3) AS 'Birth Rate - Death Rate'
FROM facts
WHERE birth_rate > death_rate
ORDER BY birth_rate - death_rate DESC
LIMIT 30
 * sqlite:///factbook.db
Done.
Out[61]:
Country Birth Rate Death Rate Birth Rate - Death Rate
Malawi 41.56 8.41 33.15
Uganda 43.79 10.69 33.1
Niger 45.45 12.42 33.03
Burundi 42.01 9.27 32.74
Mali 44.99 12.89 32.1
Burkina Faso 42.03 11.72 30.31
Zambia 42.13 12.67 29.46
Ethiopia 37.27 8.19 29.08
South Sudan 36.91 8.18 28.73
Tanzania 36.39 8.0 28.39
Gaza Strip 31.11 3.04 28.07
Timor-Leste 34.16 6.1 28.06
Benin 36.02 8.21 27.81
Iraq 31.45 3.77 27.68
Angola 38.78 11.49 27.29
Sao Tome and Principe 34.23 7.24 26.99
Togo 34.13 7.26 26.87
Somalia 40.45 13.62 26.83
Mozambique 38.58 12.1 26.48
Guinea 35.74 9.46 26.28
Sierra Leone 37.03 10.81 26.22
Cameroon 36.17 10.11 26.06
Senegal 34.52 8.46 26.06
Congo, Republic of the 35.85 10.0 25.85
Madagascar 32.61 6.81 25.8
Equatorial Guinea 33.31 8.19 25.12
Congo, Democratic Republic of the 34.88 10.07 24.81
Rwanda 33.75 8.96 24.79
Nigeria 37.64 12.9 24.74
Liberia 34.41 9.69 24.72

The above confirms our understanding on growth rate. Many of the countries displayed are from Africa and have a birth rate that is higher than the global average.

This leads to a need to find countries that are at the other end of the spectrum. Countries that have a death rate that is greater than their birth rate.

In [62]:
%%sql
SELECT name AS Country, 
       birth_rate AS 'Birth Rate', 
       death_rate AS 'Death Rate',
       ROUND(death_rate - birth_rate, 2) AS 'Death Rate - Birth Rate'
FROM facts
WHERE death_rate > birth_rate
ORDER BY death_rate-birth_rate DESC
 * sqlite:///factbook.db
Done.
Out[62]:
Country Birth Rate Death Rate Death Rate - Birth Rate
Bulgaria 8.92 14.44 5.52
Serbia 9.08 13.66 4.58
Latvia 10.0 14.31 4.31
Lithuania 10.1 14.27 4.17
Ukraine 10.72 14.46 3.74
Hungary 9.16 12.73 3.57
Germany 8.47 11.42 2.95
Slovenia 8.42 11.37 2.95
Romania 9.14 11.9 2.76
Croatia 9.45 12.18 2.73
Belarus 10.7 13.36 2.66
Monaco 6.65 9.24 2.59
Greece 8.66 11.09 2.43
Saint Pierre and Miquelon 7.42 9.72 2.3
Russia 11.6 13.69 2.09
Estonia 10.51 12.4 1.89
Portugal 9.27 11.02 1.75
Japan 7.93 9.51 1.58
Italy 8.74 10.19 1.45
Bosnia and Herzegovina 8.87 9.75 0.88
Czech Republic 9.63 10.34 0.71
Moldova 12.0 12.59 0.59
Poland 9.74 10.19 0.45
Austria 9.41 9.42 0.01

What immediately sticks out is that many of the countries displayed were part of the now defunct USSR or Soviet Union. A study related to this points out that one of the reasons for a high death rate could have been the rapid transition to privatisation of many industries of the former Soviet Union.

Having been under communism, many of these countries had large workforces and nearly everyone was engaged. However with the rise in privatisation, many were left unemployed causing them to turn to alcohol and smoking. This in addition to poor health systems may have lead to higher mortality rates. Read more

It must be noted however, that not all countries in the former Soviet Union share the same fate. Countries like Poland and Czech Republic were differentiators. Poland had social systems in place to support the population and immigrant Poles to support financially. Whereas in the Czech Republic mass privatisation was done one enterprise at a time. Read more

5

Water, Water everywhere....

Countries with more water than land

There are countries around the world where water covers more area than the land.

In [63]:
%%sql

SELECT name, area_water, area_land
FROM facts
WHERE area_water > area_land
 * sqlite:///factbook.db
Done.
Out[63]:
name area_water area_land
British Indian Ocean Territory 54340 60
Virgin Islands 1564 346

Clearly the British Indian Ocean Territory has significant amount of area held by water. It would be interesting to know the top 10 countries where the ration of water to land is high.

In [64]:
%%sql

SELECT name AS 'Top 10', ROUND(CAST(area_water AS FLOAT)/area_land,3) AS 'Water Area/Land Area'
FROM facts
ORDER BY CAST(area_water AS FLOAT)/area_land DESC
LIMIT 10;
 * sqlite:///factbook.db
Done.
Out[64]:
Top 10 Water Area/Land Area
British Indian Ocean Territory 905.667
Virgin Islands 4.52
Puerto Rico 0.555
Bahamas, The 0.387
Guinea-Bissau 0.285
Malawi 0.259
Netherlands 0.226
Uganda 0.223
Eritrea 0.164
Liberia 0.156

6

Conclusion

The CIA World Factbook is a collection of demographic, geographical and other related facts of countries around the world. Based on the provided information we have undertaken analysis on a small set of data. The data was read and we've come up with many insights. Population growth is distributed variedly around the world. Population, however is not a good indicator of population growth. Birth rates, on the other hand are a good indicator of population growth.

Many African countries are projected to have a larger population over the coming years. On the other side of the spectrum, many European countries especially those reeling from the Post-Soviet era are suffering from a high death rate possibly due to lack of employment, bad health services and heavy consumption of alcohol.

7

Acknowledgement

This project is a guided project provided by Dataquest to understand and practice fundamentals in SQL. The project was done in SQLite