Codecademy export

population_queries.sqlite

SELECT *
FROM countries;

SELECT *
FROM population_years;

/** no.3**/
SELECT COUNT(*)
FROM countries
WHERE continent="Africa";

/** no.4**/
SELECT SUM(population)
FROM countries
LEFT JOIN population_years
  ON countries.id = population_years.country_id
WHERE continent="Oceania"
  AND year=2005;

/** no.5**/
SELECT AVG(population)
FROM countries
LEFT JOIN population_years
  ON countries.id = population_years.country_id
WHERE continent="South America"
  AND year=2003;

/** no.6**/
SELECT name, MIN(population)
FROM countries
LEFT JOIN population_years
  ON countries.id = population_years.country_id
WHERE year=2007;

/** no.7**/
SELECT AVG(population)
FROM countries
LEFT JOIN population_years
  ON countries.id = population_years.country_id
WHERE name="Poland";

/** no.8**/
SELECT COUNT(*)
FROM countries
WHERE name LIKE '%The%';

/** no.9**/
SELECT continent, SUM(population)
FROM countries
LEFT JOIN population_years
  ON countries.id = population_years.country_id
WHERE year=2010
GROUP BY continent;