World Populations II Challenge Project (SQL)

Hi all,

Here are my answers: https://github.com/allangibbins/sql_practice_II/blob/master/queries.sql

Hey all, please find below my answer, any comment or suggestion is more than welcomed!!

World Populations SQL Practice II

Here’s my solution!

I believe I need to practice aggregates more - I really love how some of you used Round - I did not:

  1. How many entries in the countries table are from Africa?
    SELECT COUNT(*) FROM countries WHERE continent = ‘Africa’;

  2. What was the total population of the continent of Oceania in 2005?
    SELECT SUM(population) FROM countries LEFT JOIN population_years ON countries.id = population_years.country_id WHERE continent = ‘Oceania’ AND year = 2005;

  3. What is the average population of countries in South America in 2003?
    SELECT SUM(population) FROM countries LEFT JOIN population_years ON countries.id = population_years.country_id WHERE continent = ‘Oceania’ AND year = 2005;

  4. What country had the smallest population in 2007?
    SELECT name, year, MIN(population) FROM countries LEFT JOIN population_years ON countries.id = population_years.country_id WHERE year = 2007;

  5. What is the average population of Poland during the time period covered by this dataset?
    SELECT AVG(population) FROM countries LEFT JOIN population_years ON countries.id = population_years.country_id WHERE name = ‘Poland’;

  6. How many countries have the word “The” in their name?
    SELECT COUNT(name) FROM countries WHERE name LIKE ‘%The%’;

  7. What was the total population of each continent in 2010?
    SELECT countries.continent, SUM(population_years.population) FROM population_years LEFT JOIN countries ON population_years.country_id = countries.id WHERE year = 2010 GROUP BY countries.continent;

Here’s my solution:

Hi there, here is my answer!

Hi everyone,
Here is my SOLUTION to the World Populations II Change Project.

Hello everyone, here is my answer :slight_smile:

Hello people, this are my solutions to the project

and here is mine :slight_smile:

Hi! Here is my solution :slight_smile:

Hello All,
My code is not pretty, but I’d like to share anyways… Hope you guys are staying safe and a BIG s/o to CodeAcademy for offering Pro to students!

Here is my code:

Here is my code!!!

Sharing my solution…

Here is my solution:


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

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

SELECT MIN(population) as 'Query_6'
FROM population_years
WHERE year=2007;

SELECT AVG(population_years.population) AS Query_07
FROM population_years
JOIN countries ON countries.id=population_years.country_id
WHERE countries.name='Poland';

SELECT COUNT(*) AS Query_08
FROM countries
WHERE name LIKE '%The%';
--Query 09
SELECT continent ,SUM(population_years.population) total
FROM population_years
JOIN countries ON countries.id=population_years.country_id
WHERE population_years.year=2010
GROUP BY 1;