World Populations II Challenge Project (SQL)

SELECT COUNT(*) AS ‘Number of African Countries’

FROM countries

WHERE continent = ‘Africa’

;

SELECT SUM(population) AS ‘Population of Oceania in 2005’

FROM countries

JOIN population_years

ON countries.id = population_years.country_id

WHERE continent = ‘Oceania’ AND

year = 2005

;

SELECT ROUND(AVG(population),2) AS ‘Average population of South America in 2003’

FROM countries

JOIN population_years

ON countries.id = population_years.country_id

WHERE continent = ‘South America’ AND

year = 2003

;

SELECT countries.name AS ‘Least Populated country in 2007’

FROM countries

JOIN population_years

ON countries.id = population_years.country_id

WHERE year = 2007

ORDER BY population_years.population

LIMIT 1

;

SELECT ROUND(AVG(population),2) AS ‘Average population of Poland over the years’

FROM countries

JOIN population_years

ON countries.id = population_years.country_id

WHERE countries.name = ‘Poland’

;

SELECT COUNT(*) AS ‘Countries with THE in their name’

FROM countries

WHERE name LIKE ‘%The%’

;

SELECT countries.continent, SUM(population) AS ‘Population of Each Continent’

FROM countries

JOIN population_years

ON countries.id = population_years.country_id

WHERE year = 2010

GROUP BY countries.continent

;

Hi everyone. We used different approaches, but I didn’t find answers on questions. Just to check for mistakes. So I added comments lines with my results. We can compare them.

After comparing my code to the Solution, I realized that I overlooked something: you don’t have to always use table_name.column_name if the names are distinct, correct? For example, instead of saying countries.continent = ‘Europe’, I could just say continent = ‘Europe’ since the column ‘continent’ doesn’t overlap both tables. However, if ‘continent’ did exist in both tables, then I’d have to put the full countries.continent = ‘Europe.’ Is that correct?

My project

Here is my code

World Populations II Challenge SQL

Hi Everyone, Here’s my answer. Please let me know if you have any constructive feedback.

Here is my solution. Any feedback would be much appreciated. I am just getting started with this program.

Hi everyone, here are my solutions!!

I decided to make a big table called ‘complete’ with the data from both ‘countries’ and ‘population years’ to make this simple. :slight_smile:

–3--
select count(*) as ‘The Number of Countries in Africa per this list’
from countries
where continent = ‘Africa’;

–4--
with ‘complete’ as (select *
from population_years
left join countries
on population_years.country_id=countries.id)
select sum(complete.population) as ‘Total Population in Oceania in 2005’
from complete
where complete.continent =‘Oceania’ and complete.year = 2005;

–5--
with ‘complete’ as (select *
from population_years
left join countries
on population_years.country_id=countries.id)
select avg(complete.population) as ‘Average Population in South America in 2003’
from complete
where complete.continent =‘South America’ and complete.year = 2003;

–6--
with ‘complete’ as (select *
from population_years
left join countries
on population_years.country_id=countries.id)
select complete.name as ‘Country with the smallest population’
from complete
group by complete.name
order by complete.population asc
limit 1;

–7--
with ‘complete’ as (select *
from population_years
left join countries
on population_years.country_id=countries.id)
select avg(complete.population) as ‘Average Population of Poland across the Range of Years’
from complete
where complete.name= ‘Poland’;

–8--
with ‘complete’ as (select *
from population_years
left join countries
on population_years.country_id=countries.id)
select complete.name as ‘Countries with the word The in the name’
from complete
where complete.name like ‘%The%’
group by complete.name;

–9--
with ‘complete’ as (select *
from population_years
left join countries
on population_years.country_id=countries.id)
select complete.continent, sum(complete.population) as ‘2010 Total Population per Continent’
from complete
group by complete.continent;

Hi guys, this is my solution to the world populations II challenge project (SQL) :sunny: