Codecademy export

population_queries.sqlite

--number of countries in Africa
SELECT COUNT(name) AS number_countries_africa
FROM countries
WHERE continent = 'Africa';

--total population of the continent Oceania
WITH p_oceania AS (
  SELECT *
  FROM countries
  JOIN population_years
    ON countries.id = population_years.country_id)
SELECT SUM(population) AS t_p_oceania
FROM p_oceania
WHERE continent = 'Oceania'; 

--average population of the continent South America
WITH a_p_south_america AS (
  SELECT *
  FROM countries
  JOIN population_years
    ON countries.id = population_years.country_id)
SELECT AVG (population) AS a_p_s_america
FROM a_p_south_america
WHERE continent = 'South America'
    AND year = 2003; 
    
--smallest population country in 2007
WITH smallest_p_c AS (
  SELECT *
  FROM countries
  JOIN population_years
    ON countries.id = population_years.country_id)
SELECT MIN(population) AS s_P_country
FROM smallest_p_c
WHERE year = 2007;