World Populations Challenge Project (SQL)

Projects Challenge Projects
1

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.

image
image990×109 4.29 KB

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.
11 Likes
2

Hi! I am having trouble accessing the solution code. I downloaded DB Browser for SQLite and tried to open the file titled “population_queries_final” provided in first link on the page but the browser said “Cound not open database file. Reason: file is not a database”. Could someone please help? Thank you!

3

Hey, this seems to be outdated, i where referred to this from a project with the same name but where the questions are completely diferent to the solution here, my last question is “.
What was the total population of each continent in 2010?”

2 Likes
4

Hey there! its true. So I share my solution here to see if it matches yours :slight_smile:

5

Sharing my solution as well!

3 Likes
6
1 Like
7
8

Oops–looks like we were sending part I and part II of the world populations SQL practice projects to this same post! Part two’s solution forum is here: World Populations II Challenge Project (SQL)

Thanks for letting us know–the solution link in part II has now been fixed.

1 Like
9

Total number of African Countries
SELECT COUNT(*) AS ‘Total number of African Countries’ FROM countries WHERE continent = ‘Africa’;
56

SELECT countries.continent, SUM(population_years.population) AS ‘Total population of Oceania continent in 2005’
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE continent = ‘Oceania’ AND
year = 2005;
continent Total population of Oceania continent in 2005
Oceania 32.66417

SELECT countries.continent, AVG(population_years.population) AS ‘Average population of countries in South America in 2003’
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE continent = ‘South America’ AND
year = 2003;
continent Average population of countries in South America in 2003
South America 25.8906514285714

SELECT countries.name, MIN(population_years.population) AS ‘Smallest population in 2007’
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2007;
name Smallest population in 2007
Niue 0.00216

SELECT countries.name, AVG(population_years.population) AS ‘Average population of Poland from 2000-2010’
FROM countries JOIN population_years
ON countries.id =
population_years.country_id
WHERE name = ‘Poland’;
name Average population of Poland from 2000-2010
Poland 30.4598338672377

WITH countries_with_the AS (
SELECT DISTINCT *
FROM countries
WHERE countries.name LIKE ‘%The%’
)

SELECT COUNT(*) AS ‘Countries with the word “The” in their name’
FROM countries_with_the;
Countries with the word “The” in their name
4

SELECT countries.continent, SUM(population_years.population) AS ’ Total population of each continent in 2010’
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2010
GROUP BY 1;
continent Total population of each continent in 2010
Africa 1015.47846
Asia 4133.09148
Europe 723.06044
North America 539.79456
Oceania 34.95696
South America 396.58235

10

This is what I did to arrive at my answers for the problem. For the final one I just used my computer calculator to subtract year 2000’s population from 2010 for the population difference.
the – at the start of each item can be deleted to review the code (for those that don’t know about the function). I mainly used the – Function to separate the different portions of the project.

– This is the first query:

–SELECT DISTINCT year from population_years;

– Add your additional queries below:

–Largest population size for Gabon
–SELECT *
–FROM population_years
–WHERE country = ‘Gabon’
–ORDER BY population DESC;

–10 lowest populated countries in 2005
–SELECT *
–FROM population_years
–WHERE year = ‘2005’
–ORDER BY population ASC
–LIMIT 10;

–distinct countries with 100 million in 2010
–SELECT DISTINCT country, population, year
–FROM population_years
–WHERE population >= ‘1000.00000’
–ORDER BY year DESC
–LIMIT 5;

–Distinct country with “Islands” in name
–SELECT DISTINCT country
–FROM population_years
–WHERE country LIKE ‘%Islands%’;

–Population years for Indonesia, the difference between 2000 and 2010 is 2,829,173 people.
–SELECT DISTINCT country, population, year
–FROM population_years
–WHERE country = ‘Indonesia’
–ORDER BY year ASC;

1 Like
11

My solution as below

12

Hi,

Here are my answer to today’s question for SQL Practice I

–4. What is the largest population size for Gabon in this dataset?
select population from population_years where country = “Gabon” order by population DESC limit 1;

–5. What were the 10 lowest population countries in 2005?
select population from population_years where year = 2005 order by population asc limit 10;

–6. What are all the distinct countries with a population of over 100 million in the year 2010?
select distinct(country) from population_years where population > 100 and year = 2010;

–7. How many countries in this dataset have the word “Islands” in their name?
select count(*) from (select distinct(country) from population_years) where country like ‘%Islands%’;

–8. What is the difference in population between 2000 and 2010 in Indonesia?
select (population - (select population from population_years where country = ‘Indonesia’ and year = 2000)) from population_years where country = ‘Indonesia’ and year = 2010;

and the one for The Practice II

–3. How many entries in the countries table are from Africa?
select count(*) as ‘Total countries in Africa’ from countries where continent = ‘Africa’;

–4. What was the total population of the continent of Oceania in 2005?
select sum(population_years.population) as ‘Total population Oceania - 2005’ from population_years join countries on countries.id = population_years.country_id and population_years.year = 2005 and countries.continent = ‘Oceania’;

–5. What is the average population of countries in South America in 2003?
select avg(py.population) as ‘Average South America population - 2003’ from population_years as py
join countries as co on co.id = py.country_id
where co.continent = ‘South America’ and py.year = 2003;

–6. What country had the smallest population in 2007?
select co.name as ‘Country Name’ from countries as co
join population_years py on py.country_id = co.id
where py.year = 2007
order by py.population ASC
limit 1;

–7. What is the average population of Poland during the time period covered by this dataset?
select avg(py.population) as ‘Average Poland population’ from population_years py
join countries as co on co.id = py.country_id
where co.name = ‘Poland’;

–8. How many countries have the word “The” in their name?
select count(*) as ‘Countries with The in their name’ from countries where name like ‘%The%’;

–9. What was the total population of each continent in 2010?
select co.continent as ‘Continent Name’, sum(py.population) as ‘Total Continent Population’ from countries as co
join population_years py on py.country_id = co.id
where py.year = 2010
group by co.continent
order by co.continent ASC;

3 Likes
13

My solutions, nicely named and formatted. Hope this helps! :))

14

Please, checkout my project solution on git hub and tell me what you think.
Thanks.

15

I was confused for a second there.
I thought i was doing something totally different.

16

Sharing my solution code (using only knowledge from the first 2 lessons in LEARN SQL)

17
18

– This is the first query:

SELECT DISTINCT year from population_years;

– Add your additional queries below:

SELECT *
FROM population_years
WHERE country LIKE ‘%Gabon%’
ORDER BY population DESC;

SELECT *
FROM population_years
WHERE year = 2005
ORDER BY population ASC
LIMIT 10;

SELECT *
FROM population_years
WHERE year = 2010 AND population > 100
ORDER BY population ASC;

SELECT DISTINCT country
FROM population_years
WHERE country LIKE ‘%Islands%’;

SELECT *
FROM population_years
WHERE country LIKE ‘%Indonesia%’
ORDER by year ASC;

19
20