Codecademy export

population_queries.sqlite

-- How many entries in the database are from Africa
SELECT COUNT(continent) FROM countries WHERE continent like "Africa";


-- What was the total population of Oceania in 2005?
SELECT sum(population) FROM countries inner join population_years on countries.id =population_years.country_id WHERE countries.continent like "Oceania" and population_years.year =2005;


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

SELECT avg(population) FROM countries inner join population_years on countries.id =population_years.country_id WHERE countries.continent like "South America" and population_years.year = 2003;

-- What country had the smallest population in 2007?
SELECT name, min (population)FROM countries inner join population_years on countries.id =population_years.country_id WHERE population_years.year = 2003;


-- What is the average population of Poland during the time period covered by this dataset?
SELECT avg(population) FROM countries inner join population_years on countries.id =population_years.country_id WHERE name like "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 continent,sum(population) FROM countries inner join population_years on countries.id =population_years.country_id  WHERE year = 2010 group by continent ORDER by sum(population) desc;