%%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 |
%%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 |
%%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 |
%%sql
Select Avg(population) average_population, Avg(area) averagea_area
from facts
where name <> 'World'
Done.
average_population | averagea_area |
---|---|
32242666.56846473 | 555093.546184739 |
%%sql
Select *
from facts
where population > (select AVG(population) from facts)
and area < (select AVG(area) from facts)
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 |
85 | ja | Japan | 377915 | 364485 | 13430 | 126919659 | 0.16 | 7.93 | 9.51 | 0.0 |
138 | rp | Philippines | 300000 | 298170 | 1830 | 100998376 | 1.61 | 24.27 | 6.11 | 2.09 |
173 | th | Thailand | 513120 | 510890 | 2230 | 67976405 | 0.34 | 11.19 | 7.8 | 0.0 |
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 |
COUNTRY WITH HIGHEST RATIOS OF WATER TO LAND
%%sql
select *, Max(area_land/area_water) ratio
from facts
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate | ratio |
---|---|---|---|---|---|---|---|---|---|---|---|
22 | bk | Bosnia and Herzegovina | 51197 | 51187 | 10 | 3867055 | 0.13 | 8.87 | 9.75 | 0.38 | 5118 |
COUNTRIES WITH MORE WATER THAN LAND
%%sql
select *
from facts
where area_water > area_land
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
228 | io | British Indian Ocean Territory | 54400 | 60 | 54340 | None | None | None | None | None |
247 | vq | Virgin Islands | 1910 | 346 | 1564 | 103574 | 0.59 | 10.31 | 8.54 | 7.67 |
COUNTRY THAT WILL ADD THE MOST PEOPLE TO THEIR POPULATION NEXT YEAR
%%sql
Select *, Max((population*population_growth)-population) as added_people
from facts
where name <> 'World'
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate | added_people |
---|---|---|---|---|---|---|---|---|---|---|---|
77 | in | India | 3287263 | 2973193 | 314070 | 1251695584 | 1.22 | 19.55 | 7.32 | 0.04 | 275373028.48 |
COUNTRIES WITH HIGHER DEATH RATE THAN BIRTH RATE
%%sql
select *
from facts
where death_rate > birth_rate
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
10 | au | Austria | 83871 | 82445 | 1426 | 8665550 | 0.55 | 9.41 | 9.42 | 5.56 |
16 | bo | Belarus | 207600 | 202900 | 4700 | 9589689 | 0.2 | 10.7 | 13.36 | 0.7 |
22 | bk | Bosnia and Herzegovina | 51197 | 51187 | 10 | 3867055 | 0.13 | 8.87 | 9.75 | 0.38 |
26 | bu | Bulgaria | 110879 | 108489 | 2390 | 7186893 | 0.58 | 8.92 | 14.44 | 0.29 |
44 | hr | Croatia | 56594 | 55974 | 620 | 4464844 | 0.13 | 9.45 | 12.18 | 1.39 |
47 | ez | Czech Republic | 78867 | 77247 | 1620 | 10644842 | 0.16 | 9.63 | 10.34 | 2.33 |
57 | en | Estonia | 45228 | 42388 | 2840 | 1265420 | 0.55 | 10.51 | 12.4 | 3.6 |
65 | gm | Germany | 357022 | 348672 | 8350 | 80854408 | 0.17 | 8.47 | 11.42 | 1.24 |
67 | gr | Greece | 131957 | 130647 | 1310 | 10775643 | 0.01 | 8.66 | 11.09 | 2.32 |
75 | hu | Hungary | 93028 | 89608 | 3420 | 9897541 | 0.22 | 9.16 | 12.73 | 1.33 |
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 |
96 | lg | Latvia | 64589 | 62249 | 2340 | 1986705 | 1.06 | 10.0 | 14.31 | 6.26 |
102 | lh | Lithuania | 65300 | 62680 | 2620 | 2884433 | 1.04 | 10.1 | 14.27 | 6.27 |
116 | md | Moldova | 33851 | 32891 | 960 | 3546847 | 1.03 | 12.0 | 12.59 | 9.67 |
117 | mn | Monaco | 2 | 2 | 0 | 30535 | 0.12 | 6.65 | 9.24 | 3.83 |
139 | pl | Poland | 312685 | 304255 | 8430 | 38562189 | 0.09 | 9.74 | 10.19 | 0.46 |
140 | po | Portugal | 92090 | 91470 | 620 | 10825309 | 0.09 | 9.27 | 11.02 | 2.67 |
142 | ro | Romania | 238391 | 229891 | 8500 | 21666350 | 0.3 | 9.14 | 11.9 | 0.24 |
143 | rs | Russia | 17098242 | 16377742 | 720500 | 142423773 | 0.04 | 11.6 | 13.69 | 1.69 |
153 | ri | Serbia | 77474 | 77474 | 0 | 7176794 | 0.46 | 9.08 | 13.66 | 0.0 |
158 | si | Slovenia | 20273 | 20151 | 122 | 1983412 | 0.26 | 8.42 | 11.37 | 0.37 |
183 | up | Ukraine | 603550 | 579330 | 24220 | 44429471 | 0.6 | 10.72 | 14.46 | 2.25 |
214 | sb | Saint Pierre and Miquelon | 242 | 242 | 0 | 5657 | 1.08 | 7.42 | 9.72 | 8.49 |