Codecademy export

population_queries.sqlite

/*3.
How many entries in the countries table are from Africa?*/
SELECT COUNT(*)
FROM countries
WHERE continent = "Africa";

/*4.
What was the total population of the continent of Oceania in 2005?*/
WITH cid AS (
  SELECT *
  FROM countries
  WHERE continent = "Oceania"
)
SELECT ROUND(SUM(population), 2) AS "OCEANIA 2005"
FROM population_years
JOIN cid
  ON cid.id = population_years.id
WHERE year = 2005;

/*5.
What is the average population of countries in South America in 2003?*/
WITH SAavg AS (
  SELECT *
  FROM countries
  WHERE continent = "South America"
)
SELECT ROUND(AVG(population), 2) AS "Avg Pop"
FROM population_years
JOIN SAavg
  ON SAavg.id = population_years.id
WHERE year = 2003;

/*6.
What country had the smallest population in 2007?*/

WITH pop_min_2007 AS (
  SELECT *
  FROM population_years
  WHERE year = 2007
  ORDER BY population DESC
  LIMIT 1
) 

SELECT name
FROM countries
JOIN pop_min_2007
  ON pop_min_2007.country_id = countries.id;

/*7.
What is the average population of Poland during the time period covered by this dataset?*/

SELECT MIN(year), MAX(year)
FROM population_years;

SELECT id
FROM countries
WHERE name = "Poland";

SELECT ROUND(AVG(population), 2) AS "Poland_AVG"
FROM population_years
WHERE country_id = 76
  AND year BETWEEN 2000 AND 2010;

/*8.
How many countries have the word “The” in their name?*/

SELECT COUNT(name)
FROM countries
WHERE name LIKE "%THE%";

/*9.
What was the total population of each continent in 2010?
This one was slightly tough, has some failed attempts*/

SELECT *
FROM countries
GROUP BY continent;


WITH Africa AS (
  SELECT *
  FROM countries
  WHERE continent = "Africa"
)

SELECT ROUND(SUM(population), 2) AS "AFRICA POP"
FROM population_years
JOIN Africa
  ON Africa.id = population_years.country_id
WHERE year = 2010;

SELECT ROUND(SUM(population), 2) AS "Total Pop", continent
FROM population_years
JOIN countries
  ON countries.id = population_years.country_id
WHERE year = 2010
GROUP BY countries.continent;


/*
WITH Asia AS (
  SELECT *
  FROM countries
  WHERE continent = "Asia"
)
WITH Europe AS (
  SELECT *
  FROM countries
  WHERE continent = "Europe"
)
WITH NorthAmerica AS (
  SELECT *
  FROM countries
  WHERE continent = "North America"
)
WITH Oceania AS (
  SELECT *
  FROM countries
  WHERE continent = "Oceania"
)
WITH SouthAmerica AS (
  SELECT *
  FROM countries
  WHERE continent = "SouthAmerica"
)
*/