The aims of this Project is to analyze the data from the CIA World Factbook. The data contains information such as:
%%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) AS minpopulation,MAX(population) AS max_population, MIN(population_growth) AS min_population_growth, MAX(population_growth) AS max_population_growth
FROM facts
Done.
minpopulation | max_population | min_population_growth | max_population_growth |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
The result shows that the minimum population is 0 and the maximum population is more than 7.2 billion people. Lets have a look of those country with the minimum and maximum population.
Country with the minimum population value
%%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 |
Country with the maximum population
%%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 |
The results above show that the row with more than 7.2 Billion people is the number of the people in the World. The row with the minimum population of zero correspond to the Antarctica, which always have a weather between -20 and -60. That is why, noone is living there.
%%sql
SELECT MIN(population) AS minpopulation,MAX(population) AS max_population, MIN(population_growth) AS min_population_growth, MAX(population_growth) AS max_population_growth
FROM facts
WHERE population != (SELECT MAX(population) FROM facts) AND population != (SELECT MIN(population) FROM facts)
Done.
minpopulation | max_population | min_population_growth | max_population_growth |
---|---|---|---|
48 | 1367485388 | 0.0 | 4.02 |
%%sql
SELECT AVG(population) AS avg_population, AVG(area) AS avg_area
FROM Facts
Done.
avg_population | avg_area |
---|---|
62094928.32231405 | 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 |
%%sql
SELECT *
FROM Facts
WHERE population == (SELECT MAX(population)
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 |
---|---|---|---|---|---|---|---|---|---|---|
37 | ch | China | 9596960 | 9326410 | 270550 | 1367485388 | 0.45 | 12.49 | 7.53 | 0.44 |
%%sql
SELECT *
FROM Facts
WHERE population_growth == (SELECT MAX(population_growth)
FROM facts
WHERE population_growth != (SELECT MAX(population_growth) FROM facts))
Done.
id | code | name | area | area_land | area_water | population | population_growth | birth_rate | death_rate | migration_rate |
---|---|---|---|---|---|---|---|---|---|---|
106 | mi | Malawi | 118484 | 94080 | 24404 | 17964697 | 3.32 | 41.56 | 8.41 | 0.0 |
%%sql
SELECT MAX(population)
FROM facts
WHERE population != (SELECT MAX(population) FROM facts)
Done.
MAX(population) |
---|
1367485388 |
The Country with the highest population is