In this project, we will be working with data from the CIA WORLD FACTBOOK, a compedium of statistics about all of the countries on earth. The factbook contains demographic information like:
The database was prepared by dataquest with sqlite and can be downloaded here
we need to importall the neccesssary extensions to jupyternotebbok which will allow it to run sql
they are
%%capture %load_ext sql %sql sqlite:///factbook.db
%%capture
%load_ext sql
%sql sqlite:///factbook.db
'Connected: None@factbook.db'
The code below was used to introduce the name of the tsble and what the table looked like.
%%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;
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 |
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 |
Here are the descriptions for some of the columns above
%%sql
SELECT min(population),max(population),min(population_growth),max(population_growth)
FROM FACTS;
Done.
min(population) | max(population) | min(population_growth) | max(population_growth) |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
The query above returned a query of the minimum population, maximum population,minimum population_growth, maximum population growth.
From here we could see that there are some outliers, we have a minimun population which is zero and the maximum population which is about 7.2 billlion which is weird. let us explore this in detail
%%sql
select *
from facts
where population = (select min(population) from facts)
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
250 | ay | Antarctica | None | 280000 | None | 0 | None | None | None | None |
%%sql
select *
from facts
where population = (select max(population) from facts)
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
261 | xx | World | None | None | None | 7256490011 | 1.08 | 18.6 | 7.8 | None |
We could see that the a column was assigned to the whole world, and antarctica corresponds to the zero population column, which is quite correct. from the CIA website it says "no indigenous inhabitants, but there are both permanent and summer-only staffed research stations"
%%sql
select avg(population),avg(area) from facts
Done.
avg(population) | avg(area) |
---|---|
62094928.32231405 | 555093.546184739 |
%%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 |
---|---|---|---|---|---|---|---|---|---|---|
24 | br | Brazil | 8515770 | 8358140 | 157630 | 204259812 | 0.77 | 14.46 | 6.58 | 0.14 |
37 | ch | China | 9596960 | 9326410 | 270550 | 1367485388 | 0.45 | 12.49 | 7.53 | 0.44 |
40 | cg | Congo, Democratic Republic of the | 2344858 | 2267048 | 77810 | 79375136 | 2.45 | 34.88 | 10.07 | 0.27 |
53 | eg | Egypt | 1001450 | 995450 | 6000 | 88487396 | 1.79 | 22.9 | 4.77 | 0.19 |
58 | et | Ethiopia | 1104300 | None | 104300 | 99465819 | 2.89 | 37.27 | 8.19 | 0.22 |
61 | fr | France | 643801 | 640427 | 3374 | 66553766 | 0.43 | 12.38 | 9.16 | 1.09 |
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 |
114 | mx | Mexico | 1964375 | 1943945 | 20430 | 121736809 | 1.18 | 18.78 | 5.26 | 1.68 |
129 | ni | Nigeria | 923768 | 910768 | 13000 | 181562056 | 2.45 | 37.64 | 12.9 | 0.22 |
132 | pk | Pakistan | 796095 | 770875 | 25220 | 199085847 | 1.46 | 22.58 | 6.49 | 1.54 |
143 | rs | Russia | 17098242 | 16377742 | 720500 | 142423773 | 0.04 | 11.6 | 13.69 | 1.69 |
179 | tu | Turkey | 783562 | 769632 | 13930 | 79414269 | 1.26 | 16.33 | 5.88 | 2.16 |
186 | us | United States | 9826675 | 9161966 | 664709 | 321368864 | 0.78 | 12.49 | 8.15 | 3.86 |
197 | ee | European Union | 4324782 | None | None | 513949445 | 0.25 | 10.2 | 10.2 | 2.5 |
the above query returned the all counties that are densely populated, i.e higher than the avg popuation and and area
lets check to see counties tat have the highest ratios of water to land
%%sql
select name, cast(area_water as float)/cast(area_land as float) water_to_land_ratio
from facts
where water_to_land_ratio = (select max(cast(area_water as float)/cast(area_land as float)) from facts)
Done.
name | water_to_land_ratio |
---|---|
British Indian Ocean Territory | 905.6666666666666 |
alas we could see that the british indian ocean territory has the highest ratio of water to land. Well, which country will dare have a higher ratio than one that has ocean attached to its name
%%sql
select name, population_growth
from facts
where population_growth = (select max(population_growth) from facts)
Done.
name | population_growth |
---|---|
South Sudan | 4.02 |
From above we could see that the country that will add the most people to thier population is south sudan with a high population growth of about 4.02
%%sql
select name,death_rate,birth_rate
from facts
where death_rate > birth_rate
Done.
name | death_rate | birth_rate |
---|---|---|
Austria | 9.42 | 9.41 |
Belarus | 13.36 | 10.7 |
Bosnia and Herzegovina | 9.75 | 8.87 |
Bulgaria | 14.44 | 8.92 |
Croatia | 12.18 | 9.45 |
Czech Republic | 10.34 | 9.63 |
Estonia | 12.4 | 10.51 |
Germany | 11.42 | 8.47 |
Greece | 11.09 | 8.66 |
Hungary | 12.73 | 9.16 |
Italy | 10.19 | 8.74 |
Japan | 9.51 | 7.93 |
Latvia | 14.31 | 10.0 |
Lithuania | 14.27 | 10.1 |
Moldova | 12.59 | 12.0 |
Monaco | 9.24 | 6.65 |
Poland | 10.19 | 9.74 |
Portugal | 11.02 | 9.27 |
Romania | 11.9 | 9.14 |
Russia | 13.69 | 11.6 |
Serbia | 13.66 | 9.08 |
Slovenia | 11.37 | 8.42 |
Ukraine | 14.46 | 10.72 |
Saint Pierre and Miquelon | 9.72 | 7.42 |
The above query shows country with a higher death_rate than birth_rate