%%capture
%load_ext sql
%sql sqlite:///factbook.db
%%sql
SELECT *
FROM facts
limit 5;
* sqlite:///factbook.db 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 |
Se muestran las primeras 5 filas de la tabla, para familiarizarnos con los datos
%%sql
SELECT MIN(population) AS min_pop,
MAX(population) AS max_pop,
MIN(population_growth) AS min_growth,
MAX(population_growth) AS max_growth
FROM facts;
* sqlite:///factbook.db Done.
min_pop | max_pop | min_growth | max_growth |
---|---|---|---|
0 | 7256490011 | 0.0 | 4.02 |
Calculamos algunas estadisticas interesantes como min_pop : Poblacion Maxima max_pop : Poblacion Minima min_growth : Crecimiento minimo de poblacion max_growth : Crecimiento maximo de poblacion
%%sql
SELECT *
FROM Facts
WHERE population == (select MIN(POPULATION) FROM Facts);
* sqlite:///factbook.db 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 |
Averiguamos el pais cuya poblacion es igual a la poblacion minima.
%%sql
SELECT *
FROM Facts
WHERE population == (select MAX(POPULATION) FROM Facts);
* sqlite:///factbook.db 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 |
Averiguamos que pais es igual a la poblacion Maxima
%%sql
SELECT MIN(population) AS min_pop,
MAX(population) AS max_pop,
MIN(population_growth) AS min_growth,
MAX(population_growth) AS max_growth
FROM Facts
WHERE population !=(SELECT MAX(population)
FROM Facts);
* sqlite:///factbook.db Done.
min_pop | max_pop | min_growth | max_growth |
---|---|---|---|
0 | 1367485388 | 0.0 | 4.02 |
Como el existe una fila que se refiere a World de forma golabal, no nos interesa ya que no es una estadistica de un pais inidividual como las otras filas. Lo que hicimos fue excluir dicha fila, y calculamos nuevamente los indicadores anteriores.
Otra forma que pudiesemos hacer lo antere es que: WHERE name != 'World'. Daria el mismo resultado.
%%sql
SELECT AVG(population) AS Avg_population, AVG(area) AS Avg_area
FROM Facts;
* sqlite:///factbook.db Done.
Avg_population | Avg_area |
---|---|
62094928.32231405 | 555093.546184739 |
Calculamos otras dos estadisticas muy interesantes Avg_population : Promedio de poblacion Avg_area : Promedio de area
%%sql
SELECT *
FROM Facts
WHERE population > (SELECT AVG(population)
FROM Facts) AND area < (SELECT AVG(area)
FROM Facts);
* sqlite:///factbook.db 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 |
Filtramos para conocer los paises que cumplian con las siguientes dos condiciones: