World Populations II Challenge Project (SQL)

Congratulations on completing your project!

Compare your project to our solution code and share your project below! Your solution might not look exactly like ours, and that’s okay! The most important thing right now is to get your code working as it should (you can always refactor more later). There are multiple ways to complete these projects and you should exercise your creative abilities in doing so.

This is a safe space for you to ask questions about any sample solution code and share your work with others! Simply reply to this thread to get the conversation started. Feedback is a vital component in getting better with coding and all ability levels are welcome here, so don’t be shy!

About community guidelines: This is a supportive and kind community of people learning and developing their skills. All comments here are expected to keep to our community guidelines


How do I share my own solutions?

  • If you completed the project off-platform, you can upload your project to your own GitHub and share the public link on the relevant project topic.
  • If you completed the project in the Codecademy learning environment, use the share code link at the bottom of your code editor to create a gist, and then share that link here.

Do I really need to get set up on GitHub?
Yes! Both of these sharing methods require you to get set up on GitHub, and trust us, it’s worth your time. Here’s why:

  1. Once you have your project in GitHub, you’ll be able to share proof of your work with potential employers, and link out to it on your CV.
  2. It’s a great opportunity to get your feet wet using a development tool that tech workers use on the job, every day.

Not sure how to get started? We’ve got you covered - read this article for the easiest way to get set up on GitHub.

Best practices for asking questions about the sample solution

  • Be specific! Reference exact line numbers and syntax so others are able to identify the area of the code you have questions about.

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

Hi everyone!

my answers are below:

It is amazing for me how all of us have different ways to approach this project.

This is my solution for the last question :

WITH total_population AS (SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
AND year = ‘2010’)

SELECT ROUND (SUM (population), 2) AS ‘Total Population 2010’, continent
FROM total_population
GROUP BY 2;

I found using the WITH clause very useful throughout the project and I hope someone can look over it, and tell me if this is the wrong approach. I first found the relationship between my two tables and use WITH clause to enclose it and manipulate that data in another query ( This may be lengthier, however, it was how my head wrapped around this project )

Thanks,
Victor F

1 Like

Hello everyone!

These are my answers: https://gist.github.com/ksontoktin/4510cefa4a8e9ba2f9e9930ca5c82b54

It matches with solution almost in every question but in 8th.
In solution there are countries like Netherlands and Netherlands Antilles included, so I used these code in order to exclude them:

SELECT COUNT(*) FROM countries
where name LIKE “% The%”;

Also, thanks for the interesting projects!

1 Like

Hi,

Hey Everyone,
It was great seeing all of your responses. I’m really impressed at how complex some of your answers are as well as the simplicity of some others.

I hope y’all have a great day! Keep up the great work :slight_smile:

Hi!

And the first part of the challenge: https://github.com/HeikkiHauskaviita/Codecademy/blob/master/World%20Populations%20SQL%20Practice

Hello, everyone!

I’m new here, and I would like to share my work. I hope it will help during your projects! Have a great day!

Hello everyone!

These are my answers :

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!