Codecademy export

population_queries.sqlite

-- How many entries in the database are from Africa?
SELECT count(name) FROM
countries
WHERE continent = "Africa";
--56

-- What was the total population of Oceania in 2005?
SELECT SUM(population) FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'Oceania' AND population_years.year = '2005';
-- 32.66417
-- What is the average population of countries in South America in 2003?
SELECT avg(population) FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'South America' AND population_years.year = '2003';
-- 25.8906514285714
-- What country had the smallest population in 2007?
SELECT MIN(population), name FROM population_years
JOIN countries ON
countries.id = population_years.country_id
WHERE year = 2007;

-- What is the average population of Poland during the time period covered by this dataset?
SELECT avg(population) FROM population_years
JOIN countries 
ON countries.id = population_years.country_id
WHERE 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 continent, sum(population) AS 'regional population' FROM population_years
JOIN countries 
ON countries.id = population_years.country_id
WHERE year = '2010'
GROUP BY continent
ORDER BY 2 DESC;