Codecademy export

population_queries.sqlite

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

--4. What was the total population of the continent of Oceania in 2005?

SELECT SUM(p.population)
FROM population_years p
JOIN countries c
ON p.country_id = c.id
WHERE c.continent = 'Oceania' AND
    p.year = 2005;

-- 5. What is the average population of countries in South America in 2003?

SELECT AVG(population)
FROM population_years p
JOIN countries c
ON p.country_id = c.id
WHERE c.continent = 'South America' AND
  p.year = 2003;

-- 6.What country had the smallest population in 2007?

SELECT MIN(population)
FROM population_years p
JOIN countries c
ON p.country_id = c.id
WHERE p.year = 2007;

-- 7. What is the average population of Poland during the time period covered by this dataset?

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

-- 8. How many countries have the word “The” in their name?

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

SELECT c.continent, SUM(p.population)
FROM population_years p
JOIN countries c
ON p.country_id = c.id
WHERE p.year = 2010
GROUP BY c.continent;