Codecademy export

population_queries.sqlite

SELECT COUNT(id) FROM countries;

SELECT countries.continent, SUM(population_years.population)*1000000 AS population FROM population_years JOIN countries ON population_years.country_id = countries.id
WHERE countries.continent LIKE "Oceania" AND population_years.year = "2005";

SELECT countries.continent, AVG(population_years.population*1000000) AS population FROM population_years JOIN countries ON population_years.country_id = countries.id
WHERE countries.continent LIKE "South America" AND population_years.year = "2003";

SELECT countries.name AS country,population_years.population*1000000 AS population FROM population_years JOIN countries ON population_years.country_id = countries.id
WHERE population_years.year = "2007" 
ORDER BY population_years.population*1000000 ASC
LIMIT 1 ;

SELECT countries.name AS country, AVG(population_years.population*1000000) AS population FROM population_years JOIN countries ON population_years.country_id = countries.id
WHERE countries.name LIKE "Poland"
GROUP BY countries.name;

SELECT COUNT(countries.name) FROM population_years JOIN countries ON population_years.country_id = countries.id
WHERE countries.name LIKE "%The%";

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