Codecademy export

population_queries.sqlite

SELECT COUNT(*) AS 'Amount of entries in "countries" table from Africa'
FROM countries
WHERE continent = 'Africa';

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

WITH total_population AS (
SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT continent, AVG(population) AS 'Average population'
FROM total_population
WHERE continent = 'South America' AND year = 2003;

WITH total_population AS (
SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT continent, MIN(population) AS 'Minimum population'
FROM total_population
WHERE year = 2007;

WITH total_population AS (
SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT continent, AVG(population) AS 'Average population'
FROM total_population
WHERE continent = 'Poland';

SELECT COUNT(*) AS 'How many countries have the word "the" in their name'
FROM countries
WHERE name LIKE '%The%';

WITH total_population AS (
SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT continent, SUM(population) AS 'Total population in 2010'
FROM total_population
WHERE year = 2010
GROUP BY continent;