Codecademy export

population_queries.sqlite

SELECT COUNT(*) AS '# Countries in Africa'
FROM countries
WHERE continent = 'Africa';

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

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

SELECT name AS 'Smallest Population in 2007', MIN(population_years.population) AS 'Population(mil)'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2007;

SELECT AVG(population_years.population) AS 'Average Population Poland (all years, mil)'
FROM population_years
JOIN countries
ON countries.id = population_years.country_id
WHERE countries.name = 'Poland';

SELECT COUNT(*) AS 'Number of countries with The in name'
FROM countries
WHERE name LIKE '%The%';

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