Codecademy export

population_queries.sqlite

SELECT COUNT(*) AS '#Countries in Africa'
FROM countries
WHERE continent = 'Africa';


SELECT countries.continent,
ROUND(SUM(population_years.population), 2) AS 'total population'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = '2005'
  AND
    countries.continent = 'Oceania';
    

SELECT countries.name,
ROUND(AVG(population_years.population), 2) AS 'AVG population'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = '2003'
  AND
    countries.continent = 'South America'
GROUP BY countries.name;


SELECT countries.name,
MIN(population_years.population) AS 'population'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2007;

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


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


SELECT countries.continent,
ROUND(AVG(population_years.population), 2) AS 'AVG population'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2010
GROUP BY 1;