Codecademy export

population_queries.sqlite

--How many entries in the countries table are from Africa?
SELECT COUNT(*)
FROM countries
WHERE continent = 'Africa';

--What was the total population of the continent of Oceania in 2005?
SELECT SUM(population)
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE continent = 'Oceania' AND population_years.year = '2005';

--What is the average population of countries in South America in 2003?
SELECT ROUND(AVG(population_years.population),3)
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE continent = 'South America' AND
population_years.year = '2003';

--What country had the smallest population in 2007?
SELECT countries.name, ROUND(MIN(population_years.population),3), year
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE year = '2007';

--What is the average population of Poland during the time period covered by this dataset?
SELECT ROUND(AVG(population_years.population),3), countries.name
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE country.name = 'POLAND';

--How many countries have the word “The” in their name?
SELECT COUNT(name)
FROM countries
WHERE name LIKE '%THE%';

--What was the total population of each continent in 2010?
SELECT ROUND(SUM(population_years.population),3) AS 'TOTAL POPULATION'
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE population_years.year = '2010'
GROUP BY countries.continent;