Codecademy export

population_queries.sqlite

SELECT COUNT(*) AS 'From Africa' FROM countries
WHERE continent = 'Africa';

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

SELECT ROUND(AVG(population_years.population),5) AS 'Avg pop South America' FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'South America' AND population_years.year = 2003;

SELECT name , MIN(population_years.population) AS 'Smallest poulation' FROM countries
JOIN population_years
ON countries.id = population_years.country_id;

SELECT ROUND(AVG(population_years.population),5) AS 'Avg pop South Poland' FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.name = 'Poland' AND population_years.year BETWEEN 2000 AND 2010;

SELECT COUNT(*) FROM countries 
WHERE name LIKE '%The%';

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