%%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 name as 'country with min population'
from facts
where population=(select min(population) from facts)
Done.
country with min population |
---|
Antarctica |
%%sql
select name as 'country with the max population'
from facts
where population=(select max(population) from facts)
Done.
country with the max population |
---|
World |
%%sql
select AVG(population) as Average_population, AVG(area) as Average_area
from facts
Done.
Average_population | Average_area |
---|---|
62094928.32231405 | 555093.546184739 |
%%sql
select name as 'Dense Countries', area as 'Dense Area'
from facts
where population > (select AVG(population) from facts)
and
area > (select AVG(area) from facts)
Done.
Dense Countries | Dense Area |
---|---|
Brazil | 8515770 |
China | 9596960 |
Congo, Democratic Republic of the | 2344858 |
Egypt | 1001450 |
Ethiopia | 1104300 |
France | 643801 |
India | 3287263 |
Indonesia | 1904569 |
Iran | 1648195 |
Mexico | 1964375 |
Nigeria | 923768 |
Pakistan | 796095 |
Russia | 17098242 |
Turkey | 783562 |
United States | 9826675 |
European Union | 4324782 |
%%sql
select MAX(area_water/area_land) as 'Highest ratios of water to land'
from facts
Done.
Highest ratios of water to land |
---|
905 |
%%sql
select name as 'Countries have more water than land'
from facts
where area_land <area_water
Done.
Countries have more water than land |
---|
British Indian Ocean Territory |
Virgin Islands |
%%sql
select name as 'Top 3 countries with high population growth', population_growth
from facts
order by population_growth desc limit 3
Done.
Top 3 countries with high population growth | population_growth |
---|---|
South Sudan | 4.02 |
Malawi | 3.32 |
Burundi | 3.28 |
%%sql
select name as 'Countries with a higher death rate than birth rate'
from facts
where death_rate > birth_rate
Done.
Countries with a higher death rate than birth rate |
---|
Austria |
Belarus |
Bosnia and Herzegovina |
Bulgaria |
Croatia |
Czech Republic |
Estonia |
Germany |
Greece |
Hungary |
Italy |
Japan |
Latvia |
Lithuania |
Moldova |
Monaco |
Poland |
Portugal |
Romania |
Russia |
Serbia |
Slovenia |
Ukraine |
Saint Pierre and Miquelon |