Codecademy export

population_queries.sqlite

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

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

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

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

SELECT AVG(population_years.population)
  AS 'Poland Avg Population'
FROM population_years
JOIN countries ON countries.id = population_years.country_id
WHERE countries.name = 'Poland';

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

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