Codecademy export

population_queries.sqlite

--3.
select count(*) AS "Countries in Africa"
from countries
where continent = 'Africa';

-- 4.
select sum(population) AS "total population in Oceania (2005)"
from countries 
join population_years
on countries.id = population_years.id
where year = 2005 AND 
continent = "Oceania";

--5.
select avg(population) AS "Avg South America Population in 2003"
from countries 
join population_years
on countries.id = population_years.id
where year = 2003 AND 
continent = "South America";
--6.

select min(population) AS "Min Population in 2007", continent
from countries 
join population_years
on countries.id = population_years.id
where year = 2007 ;

--7.

select avg(population) AS "avg Poland population"
from countries
join population_years
on countries.id = population_years.id
where name = "Poland"
;

--8.
select count(*) AS "country name with THE in it"
from countries
WHERE name LIKE "%The%";

--9.
select continent, sum(population) AS "total 2010 population"
from population_years
join countries
on countries.id = population_years.id
where year = 2010
group by continent
order by 2 DESC;

--
select count( DISTINCT continent)
from countries;