Codecademy export

population_queries.sqlite

SELECT * FROM countries LIMIT 3;
SELECT * FROM population_years LIMIT 3;

SELECT COUNT(*)
FROM countries
WHERE continent LIKE 'AfrIca';

SELECT countries.continent,
  SUM(population_years.population) AS 'total pop'
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE population_years.year = 2005
  AND countries.continent LIKE 'oceania'
GROUP BY countries.continent;

SELECT countries.continent,
  ROUND(AVG(population_years.population), 2) AS 'average pop'
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE population_years.year = 2003
  AND countries.continent LIKE 'South America';
  
SELECT countries.name,
  MIN(population_years.population) AS 'min pop'
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE population_years.year = 2007;

SELECT countries.name,
  ROUND(AVG(population_years.population), 2) AS 'avg pop'
FROM countries
JOIN population_years
  ON countries.id = population_years.country_id
WHERE countries.name LIKE 'poland';

/*
SELECT id, name
FROM countries
WHERE name LIKE 'poland';

SELECT id, population, year
FROM population_years
WHERE country_id = 76
GROUP BY year;
*/

SELECT name
FROM countries
WHERE name LIKE '% the%';

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