Codecademy export

population_queries.sqlite

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

-- SELECT SUM(population_years.population)
-- AS 'Population Oceania 2005'
-- FROM countries
-- LEFT JOIN population_years
--   ON countries.id = population_years.country_id
--   WHERE population_years.year = 2005
--   AND countries.continent = 'Oceania';

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

-- SELECT countries.name, MIN(population_years.population)
-- FROM countries
-- LEFT JOIN population_years
-- ON countries.id = population_years.country_id
-- WHERE population_years.year = 2007;

-- SELECT countries.name, ROUND(AVG(population_years.population), 2)
-- FROM countries
-- LEFT JOIN population_years
-- ON countries.id = population_years.country_id
-- WHERE countries.name = 'Poland';

-- SELECT name
-- FROM countries
-- WHERE name LIKE '%The%';

WITH continent_population AS (SELECT countries.continent, countries.name, countries.id, population_years.population, population_years.year
FROM countries
CROSS JOIN population_years)
-- SELECT continent, ROUND(SUM(population), 2) AS 'total_population'
SELECT continent, ROUND(SUM(population), 2) AS 'total_population', year
FROM continent_population
WHERE year = 2010
GROUP BY continent;