Codecademy export

population_queries.sqlite

--How many countries are from Africa

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

--Total population of Oceania in 2005

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

--Average population of countries in S.A in 2003

SELECT AVG(population) FROM population_years
INNER JOIN countries ON
countries.id = population_years.country_id
WHERE year = 2003
AND continent = 'South America';

--Country with smallest population in 2007

SELECT name, year, MIN(population)
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2007;

--Average population of Poland

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

--How many countries have "The" in the name

SELECT name
FROM countries
WHERE name LIKE "%The";

--Total population of each continent in 2010

SELECT continent, SUM(population)
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
GROUP BY continent
ORDER BY SUM(population);