World Populations II Challenge Project (SQL)

Hello everyone,

here is the link to my codecademy repository:

The files relevant to this project can be found here:

./sql-population-queries-part-ii-starting

In this subfolder you will find my solution sql file as well as the provided solution. In addition you will find the database that is been used and the results of both sql files as .txt documents.

I have one thing to mention:
The first question is, in my opinion, ambiguous. I counted respective database entries in population_years rather than in countries.

I would really like your thoughts on this one as well as maybe a statement from @annajiali.

Hi everybody, here’s my solution to the exercise:

My code ^^

-----------------------------World Populations SQL Practice II-----------------------------------------

select count(*) as ‘Africa_continent’ from countries where continent = ‘Africa’;

select population_years.year,
sum(population_years.population),
countries.continent
from population_years
join countries
on population_years.country_id = countries.id
where continent = ‘Oceania’ and year = 2005;

select population_years.year,
avg(population_years.population),
countries.continent
from population_years
join countries
on population_years.country_id = countries.id
where continent = ‘South America’ and year = 2003;

select population_years.year,
population_years.population,
countries.continent
from population_years
join countries
on population_years.country_id = countries.id
where year = 2007 and population is not null order by population ASC limit 1 ;

select avg(population_years.population) as ‘Poland_avg_population’,
countries.name from population_years join countries
on population_years.country_id = countries.id
where name = ‘Poland’;

select count(*) as ‘The_name_country’ from countries where name like ‘%The%’ ;

select population_years.year,
sum(population_years.population) as ‘total_population’,
countries.continent from population_years join countries
on population_years.country_id = countries.id where year = 2010 group by continent order by sum(population_years.population) DESC;

this is my code for this project

Hi, all! This is my solution.
https://gist.github.com/3a42fad1b036bce95725caf609800717
/* How many entries in the countries table are from Africa?

56 */

select count(*)

from countries

where continent like ‘africa’;

/* What was the total population of the continent of Oceania in 2005?

33.0*/

select round(sum(population_years.population),0)

from countries

join population_years

on countries.id = population_years.country_id

where countries.continent like ‘Oceania’

and population_years.year = 2005;

/* What is the average population of countries in South America in 2003?

26.0*/

select round(avg(population_years.population),0)

from countries

join population_years

on countries.id = population_years.country_id

where countries.continent like ‘south america’

and population_years.year = 2003;

/* What country had the smallest population in 2007?

Niue, 0.00216*/

select countries.name, min(population_years.population)

from countries

join population_years

on countries.id = population_years.country_id

where population_years.year = 2007;

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

39.0*/

select round(avg(population_years.population),0)

from countries

join population_years

on countries.id = population_years.country_id

where countries.name like ‘poland’;

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

4*/

select count(*)

from countries

where name like ‘%the%’;

/* What was the total population of each continent in 2010?

continent population

Asia 4133.0

Africa 1015.0

Europe 723.0

North America 540.0

South America 397.0

Oceania 35.0*/

select countries.continent, round(sum(population_years.population),0) as ‘population’

from countries

join population_years

on countries.id = population_years.country_id

where population_years.year = 2010

group by countries.continent

order by 2 desc;

1 Like

Here is my GitHub link https://github.com/AzawStephens/World-population-SQL-project

Here is my code, although on question 6, I think that the question should describe better if null values should be considered and in question 8, if the word has to be alone or if the word the in Netherlands, for instance should be considered as well.

Here’s mine
SELECT COUNT(*)
FROM countries
WHERE continent = ‘Africa’;

SELECT ROUND(SUM(population), 1)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE continent = ‘Oceania’
AND year = 2005;

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

SELECT name, population
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2007
AND population IS NOT NULL
ORDER BY population ASC
LIMIT 1;

SELECT name, ROUND(AVG(population),1)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE name = ‘Poland’;

SELECT COUNT(name)
FROM countries
WHERE name LIKE ‘% The%’;

SELECT SUM(population), continent
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2010
GROUP BY continent;

Here’s my code: https://gist.github.com/codecademydev/1b61c6ec31aea66ed7973fd901221de4

Cheers!

Here’s the link to my code - https://gist.github.com/ec4d638a6725be7b32d3dc8c9a36ce4f

Here is the link to my code -

My biggest challenge? - Making everything way too complicated.
Haven’t tried answering all using a single query yet. Yet…

Nice Challenge!