Codecademy export

population_queries.sqlite

SELECT COUNT(*) AS 'Qnt of Contries from Africa'
FROM countries
WHERE continent = 'Africa';

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

SELECT ROUND(AVG(population_years.population),6)
AS 'Average Pop of S. America in 2003'
FROM population_years
JOIN countries
ON countries.id = population_years.country_id
WHERE countries.continent = 'South America'
AND population_years.year = 2003;

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

SELECT ROUND(AVG(population_years.population),6)
AS 'Average Poland of 11 years'
FROM population_years
JOIN countries
ON countries.id = population_years.country_id
WHERE countries.name = 'Poland';

SELECT COUNT(*) AS 'The-ifyed Names'
FROM countries
WHERE name LIKE "% The";

SELECT countries.continent AS 'Continent',
SUM(population_years.population)
AS 'Total Pop'
FROM population_years
JOIN countries
ON countries.id = population_years.country_id
WHERE population_years.year = 2010
GROUP BY 1
ORDER BY 2 DESC;