Codecademy export

population_queries.sqlite

  WITH oceania AS (
    SELECT *
  FROM countries
  WHERE continent = 'Oceania'
  )
  SELECT ROUND(SUM(population), 2)
  FROM population_years
  JOIN oceania
  ON oceania.id = population_years.country_id
  WHERE year = 2010;
  
  WITH sam AS (
    SELECT *
  FROM countries
  WHERE continent = 'South America'
  )
  SELECT ROUND(AVG(population), 3)
  FROM population_years
  JOIN sam
  ON sam.id = population_years.country_id
  WHERE year = 2010;

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

WITH average_list AS (
SELECT  country_id, ROUND(AVG(population_years.population), 3) AS 'AvgPop'
FROM population_years
GROUP BY country_id
)
SELECT *
FROM average_list
JOIN countries
 ON countries.id = average_list.country_id
 WHERE countries.name = 'Poland';

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

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