Codecademy export

population_queries.sqlite

SELECT COUNT(*) FROM countries
WHERE continent = 'Africa';
WITH previous_query AS (
SELECT * FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT SUM(population) FROM previous_query
WHERE continent = 'Oceania'
AND year = 2005;
WITH previous_query AS (
SELECT * FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT AVG(population) FROM previous_query
WHERE continent = 'South America'
AND year = 2003;
WITH previous_query AS (
SELECT * FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT name FROM previous_query
WHERE year = 2007
ORDER BY population ASC
LIMIT 1;
WITH previous_query AS (
SELECT * FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT AVG(population)
FROM previous_query
WHERE name = 'Poland';
SELECT name FROM countries
WHERE name LIKE '%The%';
WITH previous_query AS (
SELECT * FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT continent, SUM(population)
FROM previous_query
GROUP BY 1
HAVING year = 2010;