Codecademy export

population_queries.sqlite

SELECT * FROM countries LIMIT 10;
SELECT * FROM population_years LIMIT 10;

-- 3. How many entries in the countries table are from Africa?
SELECT continent, COUNT(*)
FROM countries
GROUP BY continent;

-- 4. What was the total population of the continent of Oceania in 2005?
WITH population_2005 AS (
  SELECT *
  FROM population_years
  WHERE year = 2005
)
SELECT countries.continent, ROUND(SUM(population_2005.population),2) AS '2005 population'
FROM countries
JOIN population_2005
ON countries.id = population_2005.country_id
GROUP BY 1;

-- 5. What is the average population of countries in South America in 2003?
WITH population_2003 AS (
  SELECT *
  FROM population_years
  WHERE year = 2003
)
SELECT countries.continent, ROUND(AVG(population_2003.population),2) AS '2003 Average Population'
FROM countries
JOIN population_2003
ON countries.id = population_2003.country_id
WHERE countries.continent = 'South America';


-- 6. What country had the smallest population in 2007
WITH population_2007 AS (
  SELECT *
  FROM population_years
  WHERE year = 2007
)
SELECT countries.name, population_2007.population
FROM countries
JOIN population_2007
ON countries.id = population_2007.country_id
ORDER BY population_2007.population
LIMIT 10;

-- 7. What is the average population of Poland during the time period covered by this dataset?
WITH countries_Poland AS (
  SELECT *
  FROM countries
  WHERE name = 'Poland'
)
SELECT countries_Poland.name, AVG(population_years.population) AS 'Average Population'
FROM countries_Poland
JOIN population_years
ON countries_Poland.id = population_years.country_id;

-- 8. How many countries have teh word 'The' in their name?
SELECT COUNT(*)
FROM countries
WHERE name LIKE '%The%';

-- 9.What was the total population of each continent in 2010?
WITH population_2010 AS (
  SELECT *
  FROM population_years
  WHERE year = 2010
)
SELECT countries.continent, ROUND(SUM(population_2010.population),2) AS 'Continent Population'
FROM countries
JOIN population_2010
ON countries.id = population_2010.country_id
GROUP BY countries.continent;