Codecademy export

population_queries.sqlite

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

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

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

SELECT countries.name
  AS 'Smallest Country (by population)'
FROM countries
JOIN population_years
ON countries.id =
  population_years.country_id
WHERE population_years.population IS NOT NULL
AND population_years.year = 2007
ORDER BY population_years.population ASC
LIMIT 1;

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

SELECT COUNT(*)
  AS 'Countries with "The" in the title'
FROM countries
WHERE name LIKE '%The%';

SELECT countries.continent
    AS 'Continet Name',
  SUM(population_years.population) 
    AS 'Total Population by Continent (in 2010)'
FROM countries
JOIN population_years
ON countries.id =
  population_years.country_id
GROUP BY countries.continent
ORDER BY SUM(population_years.population) DESC;