Codecademy export

population_queries.sqlite

/* 
How many entries in the countries table are from Africa?
*/
SELECT continent, count(countries.id) AS 'number_of_countries'
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE continent = 'Africa';

/*
What was the total population of the continent of Oceania in 2005?
*/
SELECT continent, 
round(sum(population)) AS 'total_pop', 
year
FROM countries
JOIN population_years
  ON countries.id = population_years.id
WHERE continent = 'Oceania' AND
      year = 2005;

/*
What is the average population of countries in South America in 2003?
*/
SELECT continent,
round(avg(population)) AS 'avg_pop'
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE continent = 'South America' AND
      year = 2003;

/*
What country had the smallest population in 2007?
*/
SELECT continent,
min(population) AS 'min_pop',
year
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE year = 2007;

/*
What is the average population of Poland during the time period covered by this dataset?
*/
SELECT name, 
round(avg(population)) AS 'avg_pop'
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE name = 'Poland';

/*
How many countries have the word “The” in their name?
*/
SELECT count(DISTINCT name) AS 'names_incl_the'
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE name LIKE '%the%';