Codecademy export

population_queries.sqlite

-- First I check the tables's structures
SELECT * FROM countries LIMIT 5;
SELECT * FROM population_years LIMIT 5;

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


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


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


-- What country had the smallest population in 2007?
SELECT countries.name, MIN(population) 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 countries.name='Poland';


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


-- What was the total population of each continent in 2010?
SELECT countries.continent, SUM(population) FROM population_years
JOIN countries ON countries.id = population_years.country_id
GROUP BY 1;