Codecademy export

population_queries.sqlite

SELECT COUNT(*) AS 'Number of African Countries'
FROM countries
WHERE continent = 'Africa'
;

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

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

SELECT countries.name AS 'Least Populated country in 2007'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2007
ORDER BY population_years.population
LIMIT 1
;

SELECT ROUND(AVG(population),2) AS 'Average population of Poland over the years'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.name = 'Poland' 
;

SELECT COUNT(*) AS 'Countries with THE in their name'
FROM countries
WHERE name LIKE '%The%'
;

SELECT countries.continent, SUM(population) AS 'Population of Each Continent'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2010
GROUP BY countries.continent
;