Codecademy export

population_queries.sqlite

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

SELECT SUM(population_years.population) AS 'Population', countries.continent AS 'Oceania'
FROM population_years 
JOIN countries 
ON population_years.country_id=countries.id
WHERE countries.continent = 'Oceania'
AND population_years.year = '2005';

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

SELECT MIN(population_years.population) AS 'Population', countries.name AS 'Country with smallest population' 
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = '2007';

SELECT ROUND(AVG(population_years.population), 1), countries.name
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE name = 'Poland';

SELECT COUNT(*)
FROM countries
WHERE name LIKE '%The%';

SELECT ROUND(SUM(population_years.population),1) AS 'Avarage population', countries.continent AS 'Continents'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = '2010'
GROUP BY continent;