LOAD the DATA

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

EXPLORE THE DATA

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)
In [14]:
%%sql
SELECT *
FROM facts
Limit 5

Done.
Out[14]:
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 [20]:
%%sql
SELECT MIN(population),MAX(population),MIN(population_growth),MAX(population_growth)
FROM facts

Done.
Out[20]:
MIN(population) MAX(population) MIN(population_growth) MAX(population_growth)
0 7256490011 0.0 4.02
In [27]:
%%sql
SELECT *
FROM facts
where population == (select min(population)from facts)

Done.
Out[27]:
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

Write a query that returns the countrie(s) with the minimum population.

In [26]:
%%sql
SELECT *
FROM facts
where population == (select max(population)from facts)

Done.
Out[26]:
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

Write a query that returns the countrie(s) with the maximum population.

In [37]:
%%sql
SELECT name,MIN(population),MAX(population),MIN(population_growth),MAX(population_growth)
FROM facts
WHERE name <> 'World'

Done.
Out[37]:
name MIN(population) MAX(population) MIN(population_growth) MAX(population_growth)
South Sudan 0 1367485388 0.0 4.02
In [39]:
%%sql 
SELECT AVG(population) , AVG(area)
FROM facts
WHERE name <> 'World'

Done.
Out[39]:
AVG(population) AVG(area)
32242666.56846473 555093.546184739

Finding Densely Populated Countries¶ To finish, we'll build on the query above to find countries that are densely populated. We'll identify countries that have:

Above average values for population. Below average values for area.

In [42]:
%%sql
SELECT *
FROM facts
WHERE population > (SELECT AVG(population)
FROM facts
WHERE name <> 'World')
AND
area < ( SELECT AVG(area)
FROM facts
WHERE name <> 'World')

Done.
Out[42]:
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
80 iz Iraq 438317 437367 950 37056169 2.93 31.45 3.77 1.62
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
91 ks Korea, South 99720 96920 2800 49115196 0.14 8.19 6.75 0.0
120 mo Morocco 446550 446300 250 33322699 1.0 18.2 4.81 3.36
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
163 sp Spain 505370 498980 6390 48146134 0.89 9.64 9.04 8.31
173 th Thailand 513120 510890 2230 67976405 0.34 11.19 7.8 0.0
182 ug Uganda 241038 197100 43938 37101745 3.24 43.79 10.69 0.74
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 [ ]: