World Populations Challenge Project (SQL)

My version!

https://gist.github.com/602bfc7ce665683301656f0f7b1507cf

1 Like

Hi,

I’m having trouble as well…I’m prompted for a password here :confused: ?
I mean, I’m happy with my solutions but I was just curious about some other possible solutions :woman_shrugging:

Thanks!

World Populations SQL Practice II

1 Like

sharing my answer for project II. https://gist.github.com/b780059a992d92d9ec157d74908647c7

Hi, I had a similar problem with DB browser on OSx - I was able to view the file by right clicking > open with > TextEdit

Should be a similar process for Windows, but maybe a different program (notepad I think).

Hope this helps!

my version!

1 Like

Population size gabon:
SELECT population FROM population_years WHERE country = ‘Gabon’ ORDER BY population DESC;
1.545.260

Countries with lowest population in 2005:
SELECT DISTINCT country FROM population_years WHERE year IS 2005 ORDER BY population ASC LIMIT 10;
Timor-Leste (East Timor)
Niue
Falkland Islands (Islas Malvinas)
Montserrat
Saint Pierre and Miquelon
Saint Helena
Nauru
Cook Islands
Turks and Caicos Islands
Virgin Islands, British

Countries with a population of over 100 million in the year 2010:
SELECT DISTINCT country FROM population_years WHERE population > 100 AND year IS 2010 ORDER BY population DESC;
China
India
United States
Indonesia
Brazil
Pakistan
Bangladesh
Nigeria
Russia
Japan
Mexico

Countries with the worlds islands:
SELECT DISTINCT country FROM population_years WHERE country LIKE ‘%islands%’;
Cayman Islands
Falkland Islands (Islas Malvinas)
Turks and Caicos Islands
Virgin Islands, U.S.
Virgin Islands, British
Faroe Islands
Cook Islands
Solomon Islands
U.S. Pacific Islands
Total: 9

Difference in population between 2000 and 2010 in Indonesia:
SELECT country, population, year FROM population_years WHERE country = ‘Indonesia’;
Indonesia|214.67661|2000
Indonesia|217.83628|2001
Indonesia|220.97191|2002
Indonesia|223.06967|2003
Indonesia|226.00413|2004
Indonesia|228.89575|2005
Indonesia|231.82024|2006
Indonesia|234.694|2007
Indonesia|237.51236|2008
Indonesia|242.96834|2010
Indonesia|240.27152|2009

1 Like

– This is the first query:

SELECT DISTINCT year from population_years;

– Add your additional queries below:

– MAX Gabon population:
SELECT MAX(population)
FROM population_years
WHERE country = “Gabon”;

– 10 Lowest population countries

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

– DISTINCT countries with population of 100 in 2010
SELECT DISTINCT country
FROM population_years
WHERE population > 100
AND year = 2010;

– Countries with “Islands” in their name
SELECT DISTINCT country
FROM population_years
WHERE country LIKE “%Islands%”
ORDER BY country ASC;

– Population difference in Indonesia from 2000 and 2010 (JOIN may have been useful)
SELECT country, year, population
FROM population_years
WHERE country = “Indonesia”
AND year = 2000
OR country = “Indonesia”
AND year = 2010;

1 Like

Actually, I was still directed here from Part II!
So either it’s still not fixed or somehow reverted…

Sharing my solution as well :slight_smile:

Open to thoughts and suggestion
SELECT DISTINCT year FROM population_years;
– What years are covered by the dataset? 2000-2010

SELECT DISTINCT country, population FROM population_years
WHERE country = ‘Gabon’
ORDER BY population DESC
LIMIT 3;
– What is the largest population size for Gabon in this dataset?
– 1.54526 million in 2010

SELECT DISTINCT country FROM population_years
ORDER BY population ASC
LIMIT 10;

– What were the 10 lowest population countries in 2005?
– Timor-Leste (East Timor); Niue; Falkland Islands (Islas Malvinas); Montserrat; Saint Pierre and Miquelon; Saint Helena; Nauru; Cook Islands; Turks and Caicos Islands; Virgin Islands, British;

SELECT DISTINCT country FROM population_years
WHERE country LIKE ‘%Islands%’;
– How many countries in this dataset have the word “Islands” in their name?
– 9 countries

SELECT DISTINCT country, population FROM population_years
WHERE population > 100. AND year = 2010;

– What are all the distinct countries with a population of over 100 million in the year 2010?
– mexico, US, japan, india, china, russia, brazil, nigeria, mexico, and pakistan, bangladesh

Select Distinct country, population From population_years
Where country = ‘Indonesia’ and year = 2010
Select Distinct country, population From population_years
Where country = ‘Indonesia’ and year = 2000;

–What is the difference in population between 2000 and 2010 in Indonesia?
–28.29173

select population
from population_years
where country = ‘Gabon’
order by population desc
limit 1;

A: 1.54526

select country, population
from population_years
where year = 2005
order by population asc
limit 10;

A: Niue, Falkland Islands (Islas Malvinas), Montserrat, Saint Pierre and Miquelon, Saint Helena, Nauru, Cook Islands, Turks and Caicos Islands, Virgin Islands- British, Gibraltar

select distinct country as ‘6_country’
from population_years
where population > 100
and year = 2010;

A: Mexico, United States, Brazil, Russia, Nigeria, Bangladesh, China, India, Indonesia, Japan, Pakistan

select distinct country
from population_years
where country like ‘%Islands%’;

A: Cayman Islands, Falkland Islands (Islas Malvinas), Turks and Caicos Islands, Virgin Islands, U.S., Virgin Islands, British, Faroe Islands, Cook Islands, Solomon Islands, U.S. Pacific Islands

select *
from population_years
where country = ‘Indonesia’ and year between 1999 and 2010;

A: 242.96834 - 214.67661 = 28.29173

– What years are covered by the dataset?

SELECT DISTINCT year from population_years
ORDER BY year ASC;

–What is the largest population size for Gabon in this dataset?
SELECT * FROM population_years
WHERE country = ‘Gabon’
ORDER BY population DESC
LIMIT 1;

–What were the 10 lowest population countries in 2005?
SELECT country, population FROM population_years
WHERE year = 2005
ORDER BY population ASC
LIMIT 10;

–What are all the distinct countries with a population of over 100 million in the year 2010?
SELECT country
FROM population_years
WHERE year = 2010
AND population > 100
ORDER BY country;

–How many countries in this dataset have the word “Islands” in their name?
SELECT DISTINCT country FROM population_years
WHERE country LIKE ‘%Islands%’;

–What is the difference in population between 2000 and 2010 in Indonesia?
SELECT population, year FROM population_years
WHERE country = ‘Indonesia’
ORDER BY year ASC;