Codecademy export

population_queries.sqlite

SELECT COUNT(*) as "Countries from Africa" 
FROM countries
WHERE continent LIKE "Africa";

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

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

SELECT countries.name AS "Country with the smallest population in 2007", MIN(population_years.population) AS "Population"
FROM countries
LEFT JOIN population_years
  ON countries.id = population_years.country_id
WHERE year="2007";

SELECT ROUND(AVG(population_years.population),2) AS "Average population of Poland" 
FROM countries
LEFT JOIN population_years
  ON countries.id = population_years.country_id
WHERE countries.name LIKE "Poland";

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

SELECT countries.continent AS "Continent", ROUND(SUM(population_years.population),2) AS "Total population in 2010"
FROM countries
LEFT JOIN population_years
  ON countries.id = population_years.country_id
WHERE year = "2010"
GROUP BY countries.continent;