%%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), MAX(population), MIN(population_growth), MAX(population_growth)
FROM facts;
Done.
MIN(population) | MAX(population) | MIN(population_growth) | MAX(population_growth) |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
We can see that there's a country with a population of 0 and a country with a population of more than 7 billion people. Let's look closer to this ones:
%%sql
SELECT *
FROM facts
WHERE population == (SELECT MIN(population) FROM facts) OR
population == (SELECT MAX(population) FROM facts);
Done.
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 |
It seems like the table contains a row for the whole world, which explains the population of over 7.2 billion and also a row for Antarctica, which explains the population of 0.
%%sql
SELECT MIN(population), MAX(population), MIN(population_growth), MAX(population_growth)
FROM facts
WHERE code NOT IN ('ay', 'xx');
Done.
MIN(population) | MAX(population) | MIN(population_growth) | MAX(population_growth) |
---|---|---|---|
48 | 1367485388 | 0.0 | 4.02 |
%%sql
SELECT AVG(population), AVG(area)
FROM facts;
Done.
AVG(population) | AVG(area) |
---|---|
62094928.32231405 | 555093.546184739 |
%%sql
SELECT name, population, area
FROM facts
WHERE population > (SELECT AVG(population) FROM facts)
AND area < (SELECT AVG(area) FROM facts)
ORDER BY population;
Done.
name | population | area |
---|---|---|
United Kingdom | 64088222 | 243610 |
Thailand | 67976405 | 513120 |
Germany | 80854408 | 357022 |
Vietnam | 94348835 | 331210 |
Philippines | 100998376 | 300000 |
Japan | 126919659 | 377915 |
Bangladesh | 168957745 | 148460 |
Which country has the most people? Which country has the highest growth rate?
%%sql
SELECT name, population
FROM facts
WHERE code NOT IN ('ay', 'xx')
ORDER BY population DESC
LIMIT 1
Done.
name | population |
---|---|
China | 1367485388 |
%%sql
SELECT name, population_growth
FROM facts
WHERE code NOT IN ('ay', 'xx')
ORDER BY population_growth DESC
LIMIT 1
Done.
name | population_growth |
---|---|
South Sudan | 4.02 |
China has the higher population while South Sudan has the highest growth rate.
Which countries have the highest ratios of water to land? Which countries have more water than land?
%%sql
SELECT name, area_land, area_water, area_water / area_land AS ratio
FROM facts
WHERE code NOT IN ('ay', 'xx')
ORDER BY ratio DESC
LIMIT 5
Done.
name | area_land | area_water | ratio |
---|---|---|---|
British Indian Ocean Territory | 60 | 54340 | 905 |
Virgin Islands | 346 | 1564 | 4 |
Afghanistan | 652230 | 0 | 0 |
Albania | 27398 | 1350 | 0 |
Algeria | 2381741 | 0 | 0 |
%%sql
SELECT name, area_land, area_water
FROM facts
WHERE area_water > area_land
AND code NOT IN ('ay', 'xx')
ORDER BY area_water DESC
LIMIT 5
Done.
name | area_land | area_water |
---|---|---|
British Indian Ocean Territory | 60 | 54340 |
Virgin Islands | 346 | 1564 |
For both questions we have British Indian Ocean Territory and Virgin Islands with the higher ratios of water to land and more water than land!
Which countries will add the most people to their populations next year?
%%sql
SELECT name, population_growth*population AS growth
FROM facts
WHERE code NOT IN ('ay', 'xx')
ORDER BY growth DESC
LIMIT 10
Done.
name | growth |
---|---|
India | 1527068612.48 |
China | 615368424.6 |
Nigeria | 444827037.20000005 |
Pakistan | 290665336.62 |
Ethiopia | 287456216.91 |
Bangladesh | 270332392.0 |
United States | 250667713.92000002 |
Indonesia | 235514180.08 |
Congo, Democratic Republic of the | 194469083.20000002 |
Philippines | 162607385.36 |
India will add the most people to her population.
Which countries have a higher death rate than birth rate?
%%sql
SELECT name, death_rate, birth_rate
FROM facts
WHERE death_rate > birth_rate
AND code NOT IN ('ay', 'xx')
ORDER BY death_rate DESC
LIMIT 5
Done.
name | death_rate | birth_rate |
---|---|---|
Ukraine | 14.46 | 10.72 |
Bulgaria | 14.44 | 8.92 |
Latvia | 14.31 | 10.0 |
Lithuania | 14.27 | 10.1 |
Russia | 13.69 | 11.6 |
Ukraine has a higher death rate than birth rate.
Which countries have the highest population/area ratio, and how does it compare to list we found in the previous screen?
%%sql
SELECT name, population, area, population / area AS ratio
FROM facts
WHERE code NOT IN ('ay', 'xx')
ORDER BY ratio DESC
LIMIT 10
Done.
name | population | area | ratio |
---|---|---|---|
Macau | 592731 | 28 | 21168 |
Monaco | 30535 | 2 | 15267 |
Singapore | 5674472 | 697 | 8141 |
Hong Kong | 7141106 | 1108 | 6445 |
Gaza Strip | 1869055 | 360 | 5191 |
Gibraltar | 29258 | 6 | 4876 |
Bahrain | 1346613 | 760 | 1771 |
Maldives | 393253 | 298 | 1319 |
Malta | 413965 | 316 | 1310 |
Bermuda | 70196 | 54 | 1299 |
Macau has the highest population/area ratio.