Codecademy export

population_queries.sqlite

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

SELECT SUM(population_years.population) AS 'total population in Oceania at 2005'
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE year = '2005'
  AND countries.continent = 'Oceania';

SELECT AVG(population_years.population) AS 'average population in South America at 2003'
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE year = '2003'
  AND countries.continent = 'South America';

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

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

SELECT COUNT(*)
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE countries.name LIKE '%The%';

SELECT DISTINCT countries.continent, SUM(population_years.population) AS 'total_population_per_continent'
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
GROUP BY countries.continent
ORDER BY total_population_per_continent DESC;