Dataset: https://www.cia.gov/library/publications/the-world-factbook/
Objective: Analyze data from the factbook.db database file to practice and showcase SQL basic skills learned
%%capture
%load_ext sql
%sql sqlite:///factbook.db
'Connected: None@factbook.db'
%%sql
/* open all tables in database */
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
/* open facts table from sqlite_master database */
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 |
%%sql
/* find population and population growth maximums and minimums */
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 |
%%sql
/* find the outlier country with the minimum population */
SELECT name, population
FROM facts
WHERE population = (SELECT MIN(population)
FROM facts
);
Done.
name | population |
---|---|
Antarctica | 0 |
%%sql
/* find the outlier country with the maximum population */
SELECT name, population
FROM facts
WHERE population = (SELECT MAX(population)
FROM facts
);
Done.
name | population |
---|---|
World | 7256490011 |
%%sql
/* requery removing outlier 'World' population statistics */
SELECT MIN(population), MAX(population), MIN(population_growth), MAX(population_growth)
FROM facts
WHERE population != (SELECT MAX(population)
FROM facts
);
Done.
MIN(population) | MAX(population) | MIN(population_growth) | MAX(population_growth) |
---|---|---|---|
0 | 1367485388 | 0.0 | 4.02 |
%%sql
/* Query average population and average area of all countries excluding 'World' */
SELECT CAST(AVG(population) AS int) AS 'Average Population', CAST(AVG(area) AS int) AS 'Average Area'
FROM facts
WHERE name != 'World';
Done.
Average Population | Average Area |
---|---|
32242666 | 555093 |
%%sql
SELECT name, population, area
FROM facts
WHERE population > (SELECT AVG(population)
FROM facts
)
AND area < (SELECT AVG(area)
FROM facts
);
Done.
name | population | area |
---|---|---|
Bangladesh | 168957745 | 148460 |
Germany | 80854408 | 357022 |
Japan | 126919659 | 377915 |
Philippines | 100998376 | 300000 |
Thailand | 67976405 | 513120 |
United Kingdom | 64088222 | 243610 |
Vietnam | 94348835 | 331210 |
%%sql
/* country with most people */
SELECT name, MAX(population)
FROM facts
WHERE name != 'World';
Done.
name | MAX(population) |
---|---|
China | 1367485388 |
%%sql
/* country with highest growth rate */
SELECT name, MAX(population_growth)
FROM facts
WHERE name != 'World';
Done.
name | MAX(population_growth) |
---|---|
South Sudan | 4.02 |
%%sql
/* Top 5 countries with highest ratios of water to land */
SELECT name, area, area_land, area_water, CAST(area_water AS Float)/(area_land) AS Q
FROM facts
WHERE name != 'World'
ORDER BY Q DESC
LIMIT 5;
Done.
name | area | area_land | area_water | Q |
---|---|---|---|---|
British Indian Ocean Territory | 54400 | 60 | 54340 | 905.6666666666666 |
Virgin Islands | 1910 | 346 | 1564 | 4.520231213872832 |
Puerto Rico | 13791 | 8870 | 4921 | 0.5547914317925592 |
Bahamas, The | 13880 | 10010 | 3870 | 0.3866133866133866 |
Guinea-Bissau | 36125 | 28120 | 8005 | 0.2846728307254623 |
%%sql
SELECT name AS 'Country', area_land AS 'Land Area', area_water AS 'Water Area', area_water - area_land AS 'Difference'
FROM facts
WHERE area_water > area_land
ORDER BY 'Difference' DESC;
Done.
Country | Land Area | Water Area | Difference |
---|---|---|---|
British Indian Ocean Territory | 60 | 54340 | 54280 |
Virgin Islands | 346 | 1564 | 1218 |
%%sql
SELECT name AS Country, population, population_growth, CAST(CAST(population AS int)*population_growth AS int) AS Population_Growth
FROM facts
WHERE Country != 'World'
ORDER BY Population_Growth DESC
LIMIT 5;
Done.
Country | population | population_growth | Population_Growth |
---|---|---|---|
India | 1251695584 | 1.22 | 1527068612 |
China | 1367485388 | 0.45 | 615368424 |
Nigeria | 181562056 | 2.45 | 444827037 |
Pakistan | 199085847 | 1.46 | 290665336 |
Ethiopia | 99465819 | 2.89 | 287456216 |
%%sql
SELECT name AS Country,
population AS Population_Now,
population_growth,
CAST(CAST(population AS int)*population_growth AS int) AS Population_Next_Year,
CAST(CAST(population AS int)*population_growth AS int)-population AS Change_in_Population
FROM facts
WHERE Country != 'World'
AND population != 'None'
AND population_growth != 'None'
AND population > (SELECT AVG(population)
FROM facts
)
ORDER BY Population_Next_Year
LIMIT 5;
Done.
Country | Population_Now | population_growth | Population_Next_Year | Change_in_Population |
---|---|---|---|---|
Russia | 142423773 | 0.04 | 5696950 | -136726823 |
Germany | 80854408 | 0.17 | 13745249 | -67109159 |
Japan | 126919659 | 0.16 | 20307145 | -106612514 |
Thailand | 67976405 | 0.34 | 23111977 | -44864428 |
France | 66553766 | 0.43 | 28618119 | -37935647 |
%%sql
SELECT name AS Country, birth_rate, death_rate, ROUND(death_rate-birth_rate, 2) AS Death_Difference
FROM facts
WHERE birth_rate !='None' AND death_rate !='None'
ORDER BY Death_Difference DESC
LIMIT 5;
Done.
Country | birth_rate | death_rate | Death_Difference |
---|---|---|---|
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 |
%%sql
SELECT name AS Country, population, area, population/area AS Population_Density
FROM facts
WHERE name != 'World'
ORDER BY Population_Density DESC
LIMIT 5;
Done.
Country | population | area | Population_Density |
---|---|---|---|
Macau | 592731 | 28 | 21168 |
Monaco | 30535 | 2 | 15267 |
Singapore | 5674472 | 697 | 8141 |
Hong Kong | 7141106 | 1108 | 6445 |
Gaza Strip | 1869055 | 360 | 5191 |