Codecademy export

population_queries.sqlite

--3.How many entries in the countries table are from Africa?
SELECT count(*) as 'Answer#3' FROM countries where continent='Africa';

--4.What was the total population of the continent of Oceania in 2005?
SELECT sum(population_years.population)  as 'Answer#4'
from countries JOIN population_years
on
countries.id=population_years.country_id
WHERE population_years.year=2005 and countries.continent='Oceania';

--5.What is the average population of countries in South America in 2003?
SELECT avg(population_years.population)  as 'Answer#5'
from countries JOIN population_years
on
countries.id=population_years.country_id
WHERE population_years.year=2003 and countries.continent='South America';

--6.What country had the smallest population in 2007?
SELECT countries.name, min(population_years.population)  as 'Answer#6'
from countries JOIN population_years
on
countries.id=population_years.country_id
WHERE population_years.year=2007;

--7.What is the average population of Poland during the time period covered by this dataset
SELECT avg(population_years.population)  as 'Answer#7'
from countries JOIN population_years
on
countries.id=population_years.country_id
WHERE countries.name='Poland';

--8.How many countries have the word “The” in their name?
SELECT count(*) as 'Answer#8'
from countries 
WHERE name like '% The%';

--9.What was the total population of each continent in 2010?
SELECT countries.continent, sum(population_years.population)  as 'Answer#9'
from countries JOIN population_years
on
countries.id=population_years.country_id
where population_years.year=2010
GROUP by 1;