Codecademy export

population_queries.sqlite

-- How many entries in the countries table are from Africa?
SELECT COUNT(*) AS 'Countries in Africa'
FROM countries
WHERE continent LIKE 'Africa';
-- Answer: 56
--
--What was the total population of the continent of Ocenaia in 2005?
SELECT ROUND(SUM(population_years.population), 2) AS 'Population of Oceania in  2005'
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE countries.continent = 'Oceania'
  AND population_years.year = 2005
GROUP BY countries.continent;
-- 32.66 Million
-- What is the average population of countries in South America in 2003?
SELECT ROUND(AVG(population_years.population), 2) 'AVG South American Population in 2003'
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE countries.continent = 'South America'
  AND population_years.year = 2003
GROUP BY countries.continent;
-- 25.89 Million
-- What country had the smallest population in 2007?
SELECT MIN(population_years.population),
countries.name
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE population_years.year = 2007;
-- Niue
--What is the average population of Poland during the time period covered by this dataset?
SELECT ROUND(AVG(population_years.population), 2) 'AVG Population of Poland'
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE countries.name = 'Poland'
AND population_years.population IS NOT NULL
AND population_years.year IS NOT NULL;
--38.56 Million
-- How many countires have the word "The" in their name?
SELECT COUNT(*)
FROM countries
WHERE name LIKE '%The%';
-- 4 
-- What was the total population of each continent in 2010?
SELECT countries.continent,
  ROUND(SUM(population_years.population), 2) AS 'Population'
FROM population_years
JOIN countries
  ON population_years.country_id = countries.id
WHERE population_years.year = 2010
GROUP BY countries.continent
ORDER BY population DESC;
-- Asia 4133.09 Million
-- Africa 1015.48 Million
-- Europe 723.06 Million
-- North America 539.79 Million
-- South America 396.58 Million
-- Oceania 34.96 Million