Codecademy export

population_queries.sqlite

--Table overview
select * 
from countries
limit 10;

select * 
from population_years
limit 10;

--Number of countries in Africa
select count(*)
from countries
where continent = 'Africa';

--Population of Oceania
with oceania_countries as (
  select id, name
  from countries
  where continent = 'Oceania'
)
select round(avg(population_years.population), 2)  as 'Average Oceania Population in 2005 (in millions)'
from population_years
join oceania_countries
where population_years.country_id = oceania_countries.id
and year = 2005;

--Average population of South American countries in 2003
select round(avg(population_years.population), 2)
from countries
join population_years
on countries.id = population_years.country_id
where countries.continent = 'South America'
and population_years.year = 2003;

--Smallest population country in 2007
select countries.name, min(population_years.population) as 'Country with smallest population in 2007'
from countries
join population_years
on countries.id = population_years.country_id
where population_years.year = 2007;

--Average population of Poland during all years
select round(avg(population_years.population), 2) as 'Average population of Poland 2000-2010 (in millions)'
from countries
join population_years
on countries.id = population_years.country_id
where countries.name = 'Poland';

--Number of countries with "The" in the name
select count(name)
from countries
where name like '% The %' or name like '% The' or name like 'The %';

--Total population of each continent in 2010
select countries.continent, sum(population_years.population) as 'population in 2010'
from countries
join population_years
on countries.id = population_years.country_id
where population_years.year = 2010
group by continent;