Codecademy export

population_queries.sqlite

SELECT COUNT(id)
FROM countries
WHERE continent = 'Africa';

-- 56 countries in Africa

SELECT ROUND(SUM(population),2) AS 'Population of Oceania in 2005'
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2005
AND continent = 'Oceania';

-- 32.66 MILLION

SELECT ROUND(AVG(population),2) AS 'Average Population of Countries in South America in 2003'
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2003
AND continent = 'South America';

-- 25.89 MILLION

SELECT MIN(population) AS 'Population in 2007', name AS 'Country'
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2007;

-- Niue

SELECT AVG(population) AS 'Average Population', name AS 'Country' 
FROM population_years
INNER JOIN countries 
ON countries.id = population_years.country_id
WHERE name = 'Poland';

-- 38.56 million

SELECT COUNT(*)
FROM countries
WHERE name LIKE '% The%';

-- 2 countries with 'The' in their name (not including countries like Netherlands)

SELECT ROUND(SUM(population),2) AS 'Population in 2010', continent AS 'Continent'
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = '2010'
GROUP BY continent;