Codecademy export

population_queries.sqlite

-- SELECT * FROM population_years;

-- TASK 3
SELECT COUNT(*) AS 'NO OF COUNTRIES FROM AFRICA'
FROM countries
WHERE continent = 'Africa';

-- TASK 4
-- METHOD 1
WITH population_count AS (
  SELECT country_id, population
  FROM population_years
  WHERE year = 2005
)
SELECT *
FROM population_count
JOIN countries
 ON population_count.country_id = countries.id
 WHERE countries.continent = 'Oceania';

-- METHOD 2
WITH population_count AS (
  SELECT *
  FROM population_years
  WHERE year = 2005
)
SELECT SUM(population) AS 'Total Population of Oceania in 2005'
FROM population_count
JOIN countries
 ON population_count.country_id = countries.id
 WHERE countries.continent = 'Oceania';
 
 -- TASK 5
 WITH population_count AS (
  SELECT *
  FROM population_years
  WHERE year = 2003
)
SELECT AVG(population) AS 'Average Population of South America in 2003'
FROM population_count
JOIN countries
 ON population_count.country_id = countries.id
 WHERE countries.continent = 'South America';
 
 -- TASK 6
WITH population_count AS (
  SELECT *
  FROM population_years
  WHERE year = 2007 AND population IS NOT NULL
)
SELECT countries.name, population
FROM population_count
JOIN countries
 ON population_count.country_id = countries.id
 ORDER BY population ASC
 LIMIT 1;

-- TASK 7
WITH population_count AS (
  SELECT *
  FROM population_years
)
SELECT AVG(population)  AS 'Average Population of Poland'
FROM population_count
JOIN countries
 ON population_count.country_id = countries.id
WHERE countries.name = 'Poland';

-- TASK 8
SELECT COUNT(name)
FROM countries
WHERE name LIKE '%The%';

-- TASK 9
SELECT continent, 
  SUM(population) AS 'Total Population of Continents in 2010'
FROM countries
CROSS JOIN population_years
WHERE population_years.country_id = countries.id 
  AND population_years.year =2010
GROUP BY countries.continent ;