!pip install ipython-sql --quiet
WARNING: You are using pip version 21.1.3; however, version 21.2.2 is available.
You should consider upgrading via the '/root/venv/bin/python -m pip install --upgrade pip' command.
%%capture
%load_ext sql
%sql sqlite:///factbook.db
%%sql
SELECT *
FROM facts
LIMIT 5;
* sqlite:///factbook.db
Done.
%%sql
SELECT MIN(population), MAX(population), MIN(population_growth), MAX(population_growth)
FROM facts
* sqlite:///factbook.db
Done.
%%sql
SELECT name AS Lowest_populated_country
FROM facts
WHERE population = (SELECT MIN(population) from facts);
* sqlite:///factbook.db
Done.
%%sql
SELECT name as Highest_populated_country
FROM facts
WHERE population = (SELECT MAX(population) from facts);
* sqlite:///factbook.db
Done.
%%sql
SELECT MIN(population), MAX(population), MIN(population_growth), MAX(population_growth)
FROM facts
WHERE population != (SELECT MAX(population) from facts);
* sqlite:///factbook.db
Done.
%%sql
SELECT AVG(population) AS Average_population,
AVG(area) as Average_area
FROM facts
WHERE population != (SELECT MAX(population) from facts);
* sqlite:///factbook.db
Done.
%%sql
SELECT name AS Countries
FROM facts
WHERE population > (SELECT AVG(population) from facts)
AND area < (SELECT AVG(area) from facts)
* sqlite:///factbook.db
Done.
%%sql
SELECT name AS Most_populated_country
FROM facts
WHERE population = (SELECT MAX(population)
FROM facts
WHERE population != (SELECT MAX(population) from facts)
)
* sqlite:///factbook.db
Done.
%%sql
SELECT name AS Highest_growth_rate
FROM facts
WHERE population_growth = (SELECT MAX(population_growth) from facts)
* sqlite:///factbook.db
Done.
%%sql
SELECT name AS countries_with_highest_water_to_land_ratio, CAST(area_water AS Float)/CAST(area_land AS Float) as ratio
FROM facts
WHERE area_water > area_land
* sqlite:///factbook.db
Done.
%%sql
SELECT name
FROM facts
WHERE death_rate > birth_rate
* sqlite:///factbook.db
Done.