%%capture
%load_ext sql
%sql sqlite:///factbook.db
'Connected: None@factbook.db'
%%sql
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
SELECT *
FROM facts
LIMIT 5;
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 |
%%sql
SELECT MIN(population) minp, MAX(population) maxp, MIN(population_growth) mingr, MAX(population_growth) maxgr
FROM facts;
Done.
minp | maxp | mingr | maxgr |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
%%sql
SELECT name minpop
FROM facts
WHERE population = (SELECT MIN(population)
FROM facts);
Done.
minpop |
---|
Antarctica |
%%sql
SELECT name maxpop
FROM facts
WHERE population = (SELECT MAX(population)
FROM facts
WHERE name != 'World');
Done.
maxpop |
---|
China |
%%sql
SELECT AVG(population) ap, AVG(area) aa
FROM facts
WHERE name != 'World';
Done.
ap | aa |
---|---|
32242666.56846473 | 555093.546184739 |
%%sql
SELECT name
FROM facts
WHERE population > (SELECT AVG(population) FROM facts) AND area < (SELECT AVG(area) FROM facts);
Done.
name |
---|
Bangladesh |
Germany |
Japan |
Philippines |
Thailand |
United Kingdom |
Vietnam |
%%sql
SELECT name, ROUND(area_water/area_land,4) wlratio
FROM facts
WHERE wlratio > 1
ORDER BY wlratio DESC;
Done.
name | wlratio |
---|---|
British Indian Ocean Territory | 905.0 |
Virgin Islands | 4.0 |
%%sql
SELECT name, CAST(ROUND(population_growth*population) AS INTEGER) realgrowth
FROM facts
WHERE name != 'World'
ORDER BY realgrowth DESC
LIMIT 10;
Done.
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 |
%%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.
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 |