Codecademy export

population_queries.sqlite

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


SELECT continent, ROUND(SUM(population),3) AS 'population'
FROM population_years, countries
WHERE population_years.year = 2005
AND countries.continent = 'Oceania'
AND population_years.country_id = countries.id
GROUP BY countries.continent  
;

SELECT continent, ROUND(AVG(population),3) AS 'Average population'
FROM population_years, countries
WHERE population_years.year = 2003
AND countries.continent = 'South America'
AND population_years.country_id = countries.id
;

SELECT name, MIN(population) AS 'smallest population'
FROM population_years, countries
WHERE population_years.year = 2007
AND population_years.country_id = countries.id;

SELECT name, ROUND(AVG(population),1) AS 'Polands avg population'
FROM population_years, countries
WHERE countries.name = 'Poland'
AND population_years.country_id = countries.id;

SELECT COUNT(name) AS 'The countries with "The"'
FROM countries
WHERE name LIKE '%The%';

SELECT continent, ROUND(SUM(population),2) AS 'total population in 2010'
FROM population_years, countries
WHERE population_years.year = 2010 
AND population_years.country_id = countries.id
GROUP BY countries.continent;