In [2]:
%%capture
%load_ext sql
%sql sqlite:///factbook.db
Out[2]:
'Connected: None@factbook.db'
In [3]:
%%sql
SELECT *
  FROM sqlite_master
Done.
Out[3]:
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 [4]:
%%sql
SELECT *
  FROM facts
 LIMIT 5
Done.
Out[4]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
1 af Afghanistan 652230 652230 0 32564342 2.32 38.57 13.89 1.51
2 al Albania 28748 27398 1350 3029278 0.3 12.92 6.58 3.3
3 ag Algeria 2381741 2381741 0 39542166 1.84 23.67 4.31 0.92
4 an Andorra 468 468 0 85580 0.12 8.13 6.96 0.0
5 ao Angola 1246700 1246700 0 19625353 2.78 38.78 11.49 0.46
In [6]:
%%sql
SELECT MIN(population), MAX(population),
       MIN(population_growth), MAX(population_growth)
  FROM facts
Done.
Out[6]:
MIN(population) MAX(population) MIN(population_growth) MAX(population_growth)
0 7256490011 0.0 4.02
In [7]:
%%sql
SELECT *
  FROM facts
 WHERE population = (SELECT MIN(population) FROM facts)
Done.
Out[7]:
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
In [8]:
%%sql
SELECT *
  FROM facts
 WHERE population = (SELECT MAX(population) FROM facts)
Done.
Out[8]:
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
In [11]:
%%sql
SELECT *
  FROM facts
 WHERE population = (SELECT MAX(population) FROM facts) OR
       population = (SELECT MIN(population) FROM facts)
Done.
Out[11]:
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
261 xx World None None None 7256490011 1.08 18.6 7.8 None
In [12]:
%%sql
SELECT MIN(population), MAX(population),
       MIN(population_growth), MAX(population_growth)
  FROM facts
 WHERE name <> 'World'
Done.
Out[12]:
MIN(population) MAX(population) MIN(population_growth) MAX(population_growth)
0 1367485388 0.0 4.02
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
In [16]:
%%sql
SELECT AVG(population) FROM facts
Done.
Out[16]:
AVG(population)
62094928.32231405
In [20]:
%%sql
SELECT *
  FROM facts
 WHERE population = (SELECT MAX(population) FROM facts
                      WHERE name <> 'World')
Done.
Out[20]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
37 ch China 9596960 9326410 270550 1367485388 0.45 12.49 7.53 0.44
In [22]:
%%sql
SELECT *
  FROM facts
 WHERE population = (SELECT MAX(population) FROM facts
                      WHERE name <> 'World') OR 
       population_growth = (SELECT MAX(population_growth) FROM facts
                      WHERE name <> 'World')
Done.
Out[22]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
37 ch China 9596960 9326410 270550 1367485388 0.45 12.49 7.53 0.44
162 od South Sudan 644329 None None 12042910 4.02 36.91 8.18 11.47
In [24]:
%%sql
SELECT *, area_water/area_land AS ratio_wl
  FROM facts
 WHERE ratio_wl = (SELECT MAX(area_water/area_land) FROM facts)
Done.
Out[24]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate ratio_wl
228 io British Indian Ocean Territory 54400 60 54340 None None None None None 905
In [25]:
%%sql
SELECT *
  FROM facts
 WHERE area_water > area_land
Done.
Out[25]:
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
In [45]:
%%sql
SELECT *, ROUND((population_growth/100) * population) AS addl_people
  FROM facts
 WHERE addl_people > (SELECT AVG((population_growth / 100) * population)
                        FROM facts
                       WHERE name <> 'World')
 ORDER BY addl_people
 LIMIT 10
Done.
Out[45]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate addl_people
29 by Burundi 27830 25680 2150 10742276 3.28 42.01 9.27 0.0 352347.0
7 ar Argentina 2780400 2736690 43710 43431886 0.93 16.64 7.33 0.0 403917.0
151 sa Saudi Arabia 2149690 2149690 0 27752316 1.46 18.51 3.33 0.55 405184.0
191 ve Venezuela 912050 882050 30000 29275460 1.39 19.16 5.31 0.0 406929.0
163 sp Spain 505370 498980 6390 48146134 0.89 9.64 9.04 8.31 428501.0
194 za Zambia 752618 743398 9220 15066266 2.88 42.13 12.67 0.68 433908.0
107 my Malaysia 329847 328657 1190 30513848 1.44 19.71 5.03 0.33 439399.0
43 iv Cote d'Ivoire 322463 318003 4460 23295302 1.91 28.67 9.55 0.0 444940.0
162 od South Sudan 644329 None None 12042910 4.02 36.91 8.18 11.47 484125.0
38 co Colombia 1138910 1038700 100210 46736728 1.04 16.47 5.4 0.64 486062.0
In [46]:
%%sql
SELECT *
  FROM facts
 WHERE death_rate > birth_rate
Done.
Out[46]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate
10 au Austria 83871 82445 1426 8665550 0.55 9.41 9.42 5.56
16 bo Belarus 207600 202900 4700 9589689 0.2 10.7 13.36 0.7
22 bk Bosnia and Herzegovina 51197 51187 10 3867055 0.13 8.87 9.75 0.38
26 bu Bulgaria 110879 108489 2390 7186893 0.58 8.92 14.44 0.29
44 hr Croatia 56594 55974 620 4464844 0.13 9.45 12.18 1.39
47 ez Czech Republic 78867 77247 1620 10644842 0.16 9.63 10.34 2.33
57 en Estonia 45228 42388 2840 1265420 0.55 10.51 12.4 3.6
65 gm Germany 357022 348672 8350 80854408 0.17 8.47 11.42 1.24
67 gr Greece 131957 130647 1310 10775643 0.01 8.66 11.09 2.32
75 hu Hungary 93028 89608 3420 9897541 0.22 9.16 12.73 1.33
83 it Italy 301340 294140 7200 61855120 0.27 8.74 10.19 4.1
85 ja Japan 377915 364485 13430 126919659 0.16 7.93 9.51 0.0
96 lg Latvia 64589 62249 2340 1986705 1.06 10.0 14.31 6.26
102 lh Lithuania 65300 62680 2620 2884433 1.04 10.1 14.27 6.27
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
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
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
153 ri Serbia 77474 77474 0 7176794 0.46 9.08 13.66 0.0
158 si Slovenia 20273 20151 122 1983412 0.26 8.42 11.37 0.37
183 up Ukraine 603550 579330 24220 44429471 0.6 10.72 14.46 2.25
214 sb Saint Pierre and Miquelon 242 242 0 5657 1.08 7.42 9.72 8.49
In [47]:
%%sql
SELECT *, population/area as population_area_ratio
  FROM facts
 WHERE population_area_ratio = (SELECT MAX(population/area) FROM facts) OR
       (population > (SELECT AVG(population) FROM facts WHERE name <> 'World') AND
        area < (SELECT AVG(area) FROM facts WHERE name <> 'World'))
Done.
Out[47]:
id code name area area_land area_water population population_growth birth_rate death_rate migration_rate population_area_ratio
14 bg Bangladesh 148460 130170 18290 168957745 1.6 21.14 5.61 0.46 1138
65 gm Germany 357022 348672 8350 80854408 0.17 8.47 11.42 1.24 226
80 iz Iraq 438317 437367 950 37056169 2.93 31.45 3.77 1.62 84
83 it Italy 301340 294140 7200 61855120 0.27 8.74 10.19 4.1 205
85 ja Japan 377915 364485 13430 126919659 0.16 7.93 9.51 0.0 335
91 ks Korea, South 99720 96920 2800 49115196 0.14 8.19 6.75 0.0 492
120 mo Morocco 446550 446300 250 33322699 1.0 18.2 4.81 3.36 74
138 rp Philippines 300000 298170 1830 100998376 1.61 24.27 6.11 2.09 336
139 pl Poland 312685 304255 8430 38562189 0.09 9.74 10.19 0.46 123
163 sp Spain 505370 498980 6390 48146134 0.89 9.64 9.04 8.31 95
173 th Thailand 513120 510890 2230 67976405 0.34 11.19 7.8 0.0 132
182 ug Uganda 241038 197100 43938 37101745 3.24 43.79 10.69 0.74 153
185 uk United Kingdom 243610 241930 1680 64088222 0.54 12.17 9.35 2.54 263
192 vm Vietnam 331210 310070 21140 94348835 0.97 15.96 5.93 0.3 284
205 mc Macau 28 28 0 592731 0.8 8.88 4.22 3.37 21168
In [ ]: