In [1]:
%%capture
%load_ext sql
%sql sqlite:///factbook.db
Out[1]:
'Connected: [email protected]'
In [2]:
%%sql
SELECT * 
  FROM sqlite_master
 WHERE type = 'table';
Done.
Out[2]:
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 [5]:
%%sql
SELECT MIN(population) minp, MAX(population) maxp, MIN(population_growth) mingr, MAX(population_growth) maxgr
  FROM facts;
Done.
Out[5]:
minp maxp mingr maxgr
0 7256490011 0.0 4.02
In [7]:
%%sql
SELECT name minpop
  FROM facts
 WHERE population = (SELECT MIN(population)
                       FROM facts);
Done.
Out[7]:
minpop
Antarctica
In [18]:
%%sql
SELECT name maxpop
  FROM facts
 WHERE population = (SELECT MAX(population)
                       FROM facts
                     WHERE name != 'World');
Done.
Out[18]:
maxpop
China
In [19]:
%%sql

SELECT AVG(population) ap, AVG(area) aa
  FROM facts
 WHERE name != 'World';
Done.
Out[19]:
ap aa
32242666.56846473 555093.546184739
In [15]:
%%sql
SELECT name
  FROM facts
 WHERE population > (SELECT AVG(population) FROM facts) AND area < (SELECT AVG(area) FROM facts);
Done.
Out[15]:
name
Bangladesh
Germany
Japan
Philippines
Thailand
United Kingdom
Vietnam
In [22]:
%%sql
SELECT name, ROUND(area_water/area_land,4) wlratio
  FROM facts
 WHERE wlratio > 1  
 ORDER BY wlratio DESC;
Done.
Out[22]:
name wlratio
British Indian Ocean Territory 905.0
Virgin Islands 4.0
In [28]:
%%sql
SELECT name, CAST(ROUND(population_growth*population) AS INTEGER) realgrowth
  FROM facts
 WHERE name != 'World'
 ORDER BY realgrowth DESC
 LIMIT 10;
Done.
Out[28]:
name realgrowth
India 1527068612
China 615368425
Nigeria 444827037
Pakistan 290665337
Ethiopia 287456217
Bangladesh 270332392
United States 250667714
Indonesia 235514180
Congo, Democratic Republic of the 194469083
Philippines 162607385
In [36]:
%%sql
SELECT name drworse, ROUND(death_rate-birth_rate,2) drspread
  FROM facts
 WHERE name != 'World' AND birth_rate < death_rate
 ORDER BY drspread DESC;
Done.
Out[36]:
drworse drspread
Bulgaria 5.52
Serbia 4.58
Latvia 4.31
Lithuania 4.17
Ukraine 3.74
Hungary 3.57
Germany 2.95
Slovenia 2.95
Romania 2.76
Croatia 2.73
Belarus 2.66
Monaco 2.59
Greece 2.43
Saint Pierre and Miquelon 2.3
Russia 2.09
Estonia 1.89
Portugal 1.75
Japan 1.58
Italy 1.45
Bosnia and Herzegovina 0.88
Czech Republic 0.71
Moldova 0.59
Poland 0.45
Austria 0.01
In [ ]: