Analyzing CIA Factbook Data Using SQL

In this project, we'll work with data from the CIA World Factbook, a compendium of statistics about all of the countries on Earth. The Factbook contains demographic information like:

  • population - The population as of 2015.
  • population_growth - The annual population growth rate, as a percentage.
  • area - The total land and water area.

Connecting Jupyter Notebook to our Database File

In [1]:
%%capture
%load_ext sql
%sql sqlite:///factbook.db
Out[1]:
'Connected: [email protected]'

Description of Tables in the Database

In [5]:
%%sql
SELECT * FROM sqlite_master WHERE type='table';
Done.
Out[5]:
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)

First 5 rows of the Facts Table in the Database

In [6]:
%%sql
SELECT * FROM facts limit 5;
Done.
Out[6]:
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

Here are the descriptions for some of the columns:

  • name - The name of the country.
  • area - The total land and sea area of the country.
  • population - The country's population.
  • population_growth- The country's population growth as a percentage.
  • birth_rate - The country's birth rate, or the number of births a year per 1,000 people.
  • death_rate - The country's death rate, or the number of death a year per 1,000 people.
  • area- The country's total area (both land and water).
  • area_land - The country's land area in square kilometers.
  • area_water - The country's waterarea in square kilometers.

Total Number Of Rows In Facts Table

In [34]:
%%sql
SELECT COUNT(*) AS ROWS FROM facts;
Done.
Out[34]:
ROWS
261

Summary Statistics of Facts Table

In [8]:
%%sql
SELECT MIN(population) AS MIN_POPULATION,
        MAX(population) AS MAX_POPULATION,
    MIN(population_growth) AS MIN_POPULATION_GROWTH,
    MAX(population_growth) AS MAX_POPULATION_GROWTH FROM facts;
Done.
Out[8]:
MIN_POPULATION MAX_POPULATION MIN_POPULATION_GROWTH MAX_POPULATION_GROWTH
0 7256490011 0.0 4.02

Outlier Countries

It seems like the table contains a row for the whole world, which explains the population of over 7.2 billion. It also seems like the table contains a row for Antarctica, which explains the population of 0.
This seems to match the CIA Factbook page for Antarctica:

In [14]:
%%sql
SELECT name,population FROM facts where population=(SELECT MIN(population) FROM facts);
Done.
Out[14]:
name population
Antarctica 0
In [15]:
%%sql
SELECT name,population FROM facts where population=(SELECT MAX(population) FROM facts);
Done.
Out[15]:
name population
World 7256490011

Exploring Average population and Average Area.

In [16]:
%%sql
SELECT AVG(population) as AVG_POPULATION,
AVG(area) AS AVG_AREA FROM facts;
Done.
Out[16]:
AVG_POPULATION AVG_AREA
62094928.32231405 555093.546184739

Finding countries that are densely populated.

In [17]:
%%sql
SELECT * FROM facts
WHERE population > (
    SELECT AVG(population)
    FROM facts
)
  AND area < (
    SELECT AVG(area)
    FROM facts
)
Done.
Out[17]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
14 bg Bangladesh 148460 130170 18290 168957745 1.6 21.14 5.61 0.46
65 gm Germany 357022 348672 8350 80854408 0.17 8.47 11.42 1.24
85 ja Japan 377915 364485 13430 126919659 0.16 7.93 9.51 0.0
138 rp Philippines 300000 298170 1830 100998376 1.61 24.27 6.11 2.09
173 th Thailand 513120 510890 2230 67976405 0.34 11.19 7.8 0.0
185 uk United Kingdom 243610 241930 1680 64088222 0.54 12.17 9.35 2.54
192 vm Vietnam 331210 310070 21140 94348835 0.97 15.96 5.93 0.3

Bangladesh,Germany,Japan,Philippines,Thailand,United Kingdom, Vietnam are the densely populated countries as of 2015 stated in Central Intelligence Agency(CIA) Factbook Data.

Finding countries that are sparsely populated.

In [18]:
%%sql
SELECT * FROM facts
WHERE population < (
    SELECT AVG(population)
    FROM facts
)
  AND area > (
    SELECT AVG(area)
    FROM facts
)
Done.
Out[18]:
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
3 ag Algeria 2381741 2381741 0 39542166 1.84 23.67 4.31 0.92
5 ao Angola 1246700 1246700 0 19625353 2.78 38.78 11.49 0.46
7 ar Argentina 2780400 2736690 43710 43431886 0.93 16.64 7.33 0.0
9 as Australia 7741220 7682300 58920 22751014 1.07 12.15 7.14 5.65
21 bl Bolivia 1098581 1083301 15280 10800882 1.56 22.76 6.52 0.62
23 bc Botswana 581730 566730 15000 2182719 1.21 20.96 13.39 4.56
28 bm Burma 676578 653508 23070 56320206 1.01 18.39 7.96 0.28
32 ca Canada 9984670 9093507 891163 35099836 0.75 10.28 8.42 5.66
34 ct Central African Republic 622984 622984 0 5391539 2.13 35.08 13.8 0.0
36 ci Chile 756102 743812 12290 17508260 0.82 13.83 6.0 0.34
38 co Colombia 1138910 1038700 100210 46736728 1.04 16.47 5.4 0.64
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
100 ly Libya 1759540 1759540 0 6411776 2.23 18.03 3.58 7.8
105 ma Madagascar 587041 581540 5501 23812681 2.58 32.61 6.81 0.0
109 ml Mali 1240192 1220190 20002 16955536 2.98 44.99 12.89 2.26
112 mr Mauritania 1030700 1030700 0 3596702 2.23 31.34 8.2 0.83
118 mg Mongolia 1564116 1553556 10560 2992908 1.31 20.25 6.35 0.84
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
137 pe Peru 1285216 1279996 5220 30444999 0.97 18.28 6.01 2.53
151 sa Saudi Arabia 2149690 2149690 0 27752316 1.46 18.51 3.33 0.55
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
165 su Sudan 1861484 None None 36108853 1.72 29.19 7.66 4.29
172 tz Tanzania 947300 885800 61500 51045882 2.79 36.39 8.0 0.54
183 up Ukraine 603550 579330 24220 44429471 0.6 10.72 14.46 2.25
191 ve Venezuela 912050 882050 30000 29275460 1.39 19.16 5.31 0.0
194 za Zambia 752618 743398 9220 15066266 2.88 42.13 12.67 0.68
207 gl Greenland 2166086 2166086 None 57733 0.0 14.48 8.49 5.98

These are the sparsely populated countries as of 2015 stated in Central Intelligence Agency(CIA) Factbook Data.

Countries having more water than land

In [20]:
%%sql
SELECT * FROM facts WHERE area_water>area_land;
Done.
Out[20]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
228 io British Indian Ocean Territory 54400 60 54340 None None None None None
247 vq Virgin Islands 1910 346 1564 103574 0.59 10.31 8.54 7.67

Countries having highest ratios of water to land

In [29]:
%%sql
SELECT * from facts
WHERE ((area_water*1000)/area_land)>(SELECT AVG((area_water*1000)/area_land) FROM facts);
Done.
Out[29]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
228 io British Indian Ocean Territory 54400 60 54340 None None None None None
247 vq Virgin Islands 1910 346 1564 103574 0.59 10.31 8.54 7.67

Countries having higher death rate than birth rate

In [23]:
%%sql
SELECT name, death_rate,birth_rate FROM facts WHERE death_rate>birth_rate;
Done.
Out[23]:
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

Countries which will add the most people to their population next year

In [30]:
%%sql
SELECT * from facts
WHERE ((birth_rate*1000)/death_rate)>(SELECT AVG((birth_rate*1000)/death_rate) FROM facts);
Done.
Out[30]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
3 ag Algeria 2381741 2381741 0 39542166 1.84 23.67 4.31 0.92
5 ao Angola 1246700 1246700 0 19625353 2.78 38.78 11.49 0.46
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
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
21 bl Bolivia 1098581 1083301 15280 10800882 1.56 22.76 6.52 0.62
25 bx Brunei 5765 5265 500 429646 1.62 17.32 3.52 2.43
27 uv Burkina Faso 274200 273800 400 18931686 3.03 42.03 11.72 0.0
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
33 cv Cabo Verde 4033 4033 0 545993 1.36 20.33 6.11 0.63
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
49 dj Djibouti 23200 23180 20 828324 2.2 23.65 7.73 6.06
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
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
63 ga Gambia, The 11300 10120 1180 1967709 2.16 30.86 7.15 2.12
66 gh Ghana 238533 227533 11000 26327649 2.18 31.09 7.22 2.02
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
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
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
82 is Israel 20770 20330 440 8049314 1.56 18.48 5.15 2.24
86 jo Jordan 89342 88802 540 8117564 0.83 25.37 3.79 13.24
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
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
97 le Lebanon 10400 10230 170 6184701 0.86 14.59 4.88 1.1
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
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
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
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
118 mg Mongolia 1564116 1553556 10560 2992908 1.31 20.25 6.35 0.84
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
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
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
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
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
141 qa Qatar 11586 11586 0 2194817 3.07 9.84 1.53 22.39
144 rw Rwanda 26338 24668 1670 12661733 2.56 33.75 8.96 0.85
148 ws Samoa 2831 2821 10 197773 0.58 20.87 5.32 9.78
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
155 sl Sierra Leone 71740 71620 120 5879098 2.35 37.03 10.81 2.77
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
162 od South Sudan 644329 None None 12042910 4.02 36.91 8.18 11.47
165 su Sudan 1861484 None None 36108853 1.72 29.19 7.66 4.29
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
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
180 tx Turkmenistan 488100 469930 18170 5231422 1.14 19.4 6.13 1.84
182 ug Uganda 241038 197100 43938 37101745 3.24 43.79 10.69 0.74
184 ae United Arab Emirates 83600 83600 0 5779760 2.58 15.43 1.97 12.36
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
191 ve Venezuela 912050 882050 30000 29275460 1.39 19.16 5.31 0.0
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
209 fp French Polynesia 4167 3827 340 282703 0.94 15.22 5.02 0.84
218 sk Sint Maarten 34 34 0 39689 1.51 13.0 4.51 6.63
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
245 cq Northern Mariana Islands 464 464 0 52344 2.18 18.32 3.71 7.16
251 gz Gaza Strip 360 360 0 1869055 2.81 31.11 3.04 0.0
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
In [ ]: