Codecademy export

population_queries.sqlite

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

SELECT SUM(population_years.population)
FROM population_years
JOIN countries
  ON countries.id = population_years.country_id
WHERE population_years.year = 2005
  AND countries.continent = 'Oceania';
  
SELECT ROUND(AVG(population_years.population), 2)
AS 'Avg S America pop'
FROM population_years
JOIN countries
  ON countries.id = population_years.country_id
WHERE countries.continent = 'South America'
  AND population_years.year = 2003;
  
SELECT countries.name,
  MIN(population_years.population)
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE population_years.year = 2007;

SELECT AVG(population_years.population)
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE countries.name = 'Poland';

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

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