%%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 |
The first 5 rows of the facts table
%%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 |
%%sql
SELECT *
FROM facts
WHERE population = (SELECT MIN(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 |
Antarctica is the country with the minimum population (n = 0)
%%sql
SELECT *
FROM facts
WHERE population = (SELECT MAX(population)
FROM facts)
Done.
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 |
As we are looking country specific data, we should exclude 'World'.
%%sql
SELECT MIN(population),
MAX(population),
MIN(population_growth),
MAX(population_growth)
FROM facts
WHERE name <> 'World'
Done.
MIN(population) | MAX(population) | MIN(population_growth) | MAX(population_growth) |
---|---|---|---|
0 | 1367485388 | 0.0 | 4.02 |
%%sql
SELECT AVG(population),
AVG(area)
FROM facts
Done.
AVG(population) | AVG(area) |
---|---|
62094928.32231405 | 555093.546184739 |
Average value for population and area columns
%%sql
SELECT AVG(population) AS avg_population,
AVG(area) AS avg_area
FROM facts
WHERE name <> 'World'
Done.
avg_population | avg_area |
---|---|
32242666.56846473 | 555093.546184739 |
%%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.
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 |
These are the countries where population is above the average of the other countries and area is below average of the other countries.
%%sql
SELECT *
FROM facts
WHERE name <> 'World'
ORDER BY population DESC
LIMIT 5
Done.
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 |
77 | in | India | 3287263 | 2973193 | 314070 | 1251695584 | 1.22 | 19.55 | 7.32 | 0.04 |
197 | ee | European Union | 4324782 | None | None | 513949445 | 0.25 | 10.2 | 10.2 | 2.5 |
186 | us | United States | 9826675 | 9161966 | 664709 | 321368864 | 0.78 | 12.49 | 8.15 | 3.86 |
78 | id | Indonesia | 1904569 | 1811569 | 93000 | 255993674 | 0.92 | 16.72 | 6.37 | 1.16 |
China and India are the most populous countries.
%%sql
SELECT *
FROM facts
WHERE name <> 'World' AND name <> 'European Union'
ORDER BY population_growth DESC
LIMIT 5
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
162 | od | South Sudan | 644329 | None | None | 12042910 | 4.02 | 36.91 | 8.18 | 11.47 |
106 | mi | Malawi | 118484 | 94080 | 24404 | 17964697 | 3.32 | 41.56 | 8.41 | 0.0 |
29 | by | Burundi | 27830 | 25680 | 2150 | 10742276 | 3.28 | 42.01 | 9.27 | 0.0 |
128 | ng | Niger | None | 1266700 | 300 | 18045729 | 3.25 | 45.45 | 12.42 | 0.56 |
182 | ug | Uganda | 241038 | 197100 | 43938 | 37101745 | 3.24 | 43.79 | 10.69 | 0.74 |
South Sudan is the country with the highest population growth.
%%sql
SELECT name, ((CAST(area_water AS FLOAT) / CAST(area AS FLOAT)) * 100) AS Ratio_of_Water
FROM facts
WHERE area_water != 0
ORDER BY Ratio_of_Water DESC
LIMIT 5
Done.
name | Ratio_of_Water |
---|---|
British Indian Ocean Territory | 99.88970588235294 |
Virgin Islands | 81.8848167539267 |
Puerto Rico | 35.6826916104706 |
Bahamas, The | 27.881844380403457 |
Guinea-Bissau | 22.15916955017301 |
British Indian Ocean Teritory and Virgin Islands have the highest ratios of water to land. Also to say, theese 2 countries have more water than land.
%%sql
SELECT name, ROUND(birth_rate - death_rate, 1) AS ratio_death_to_birth_rate
FROM facts
WHERE death_rate NOT NULL AND birth_rate NOT NULL
ORDER BY ratio_death_to_birth_rate DESC
LIMIT 10;
Done.
name | ratio_death_to_birth_rate |
---|---|
Malawi | 33.2 |
Uganda | 33.1 |
Niger | 33.0 |
Burundi | 32.7 |
Mali | 32.1 |
Burkina Faso | 30.3 |
Zambia | 29.5 |
Ethiopia | 29.1 |
South Sudan | 28.7 |
Tanzania | 28.4 |
These are the top 10 countries that have higher death rate than to birth rate.
%%sql
SELECT name, ROUND((CAST(population AS FLOAT) / CAST(area AS FLOAT)), 2) AS population_density
FROM facts
WHERE name != 'World'
ORDER BY population_density DESC
LIMIT 20
Done.
name | population_density |
---|---|
Macau | 21168.96 |
Monaco | 15267.5 |
Singapore | 8141.28 |
Hong Kong | 6445.04 |
Gaza Strip | 5191.82 |
Gibraltar | 4876.33 |
Bahrain | 1771.86 |
Maldives | 1319.64 |
Malta | 1310.02 |
Bermuda | 1299.93 |
Sint Maarten | 1167.32 |
Bangladesh | 1138.07 |
Guernsey | 847.18 |
Jersey | 838.74 |
Barbados | 675.82 |
Mauritius | 656.78 |
Taiwan | 650.78 |
Aruba | 623.12 |
Lebanon | 594.68 |
Saint Martin | 588.04 |
Macau and Monaco have the highest population density per area.