# World Populations Challenge Project (SQL)

Hi! Here is my solution to the project: Feel free to see how mine fairs against yours!

Here is my Solution for the first sql challenging project.

Hello all! I am showing my solutions here, about project I. Any feedback or interaction here or on GitHub would be so appreciated!

Excellent Challenge Project.

This is my solutions!

Hello!
First Project done.

I thought outside the box in the last one.
Please tell me how I could have solved it with less effort.

Thank you!

Thanks for this support!!

Hi, is it my code correct?
Could you guy give me some advice?
Thank you you guy in advance.

I’M SORRY FOR ASKING,
how can i see the solution.
i downloaded file of solution which is given,
but it is just a database. there is no solution.

HERE IS MY SOLUTION I WANT TO COMPARE MINE WITH SOLUTION… THAT’S WHY I NEED HELP.

–Q3. What years are covered by the dataset?

SELECT DISTINCT year from population_years;

–A3. from 2000 to 2010 total 11years recorded

–Q4. What is the largest population size for Gabon in this dataset?

select * from population_years

where country = ‘Gabon’

order by population desc

limit 1;

–A4. on 2010, 1.54526 recorded

–Q5. What were the 10 lowest population countries in 2005?

select * from population_years

where year = ‘2005’

order by population

limit 10;

–A5. FROM the lowest,

–Niue, Falkland, Montserrat, Saint Pierre and Miquelon, Saint Helena, Nauru, Cook Islands, Turks and Caicos Islands, Virgin Islands, Gibraltar

–Q6. What are all the distinct countries with a population of over 100 million in the year 2010?

select distinct country, population, year

from population_years

where population >= 100

and year = ‘2010’;

–A6. Mexico, United States, Brazil, Russia, Nigeria, Bangladesh, China, India, Indonesia, Japan, Pakistan

–Q7. How many countries in this dataset have the word “Islands” in their name?

select distinct country

from population_years

where country like “%Islands%”;

–A7. Total 7. 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

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

select *

from population_years

where (country = ‘Indonesia’ and year = 2000)

or (country = ‘Indonesia’ and year = 2010);

–A8. in 2000: 214.67661 in 2010: 242.96834 so the difference is 28.29173(million)

Here’s my first project – let me know how it looks

– This is the first query:

SELECT DISTINCT year from population_years;

SELECT country, population, year FROM population_years WHERE country=‘Gabon’ ORDER BY population DESC LIMIT 1;

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

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

SELECT DISTINCT country FROM population_years WHERE country LIKE “%Islands%”;

SELECT country, population, year, CASE
WHEN year=2000 THEN population
WHEN year=2010 THEN “El restado”
ELSE “Otro año”
END AS resta FROM population_years
WHERE country =“Indonesia” AND (year=2000 OR year=2010);

/* Intento de hacer la resta
SELECT * FROM (
SELECT a2010.population-a2000.population AS resultado FROM population_years AS a2000
LEFT JOIN population_years AS a2010 ON (a2000.year<>a2010.year)
WHERE a2000.year =2000 AND a2010.year =2010
) a2000 WHERE resultado>0;
*/

Hello all. this is my queries for the world population project.

My solution

I Completed 1st Own my project in my data science career path

Hey guys,
Here are my solutions:

``````-- 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 country, 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(DISTINCT country) FROM population_years
WHERE country LIKE '%Islands%';

--8.What is the difference in population between 2000 and 2010 in Indonesia?

-- pulling all the date from 2000 to 2000:
SELECT *
FROM population_years
WHERE country = 'Indonesia'
ORDER BY year ASC;

-- calculate difference: 242.96834 - 214.67661 = 28.29173`Preformatted text`
``````

Sharing my results for part I:

– 4.What is the largest population size for Gabon in this dataset?

SELECT COUNT (DISTINCT year)
from population_years;

– 4.What is the largest population size for Gabon in this dataset?

SELECT country, population, year
FROM population_years
WHERE country = ‘Gabon’
ORDER BY population DESC;

SELECT country, MAX (population), year
FROM population_years
WHERE country = ‘Gabon’
ORDER BY population DESC;

–5.What were the 10 lowest population countries in 2005?

SELECT country, population, year
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 country, population
FROM population_years
WHERE year = 2010
AND population > 100
ORDER BY population DESC;

–7.How many countries in this dataset have the word “Islands” in their name?

SELECT COUNT (DISTINCT country)
FROM population_years
WHERE country LIKE ‘%Islands%’
ORDER BY country ASC;

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

SELECT population AS ‘Population Indonesia’, year
FROM population_years
WHERE country = ‘Indonesia’
AND year BETWEEN 2000 AND 2010;

SELECT
(SELECT population_years.population FROM population_years WHERE country = ‘Indonesia’ AND year=2000) AS ‘Population 2000’,
(SELECT population_years.population FROM population_years WHERE country = ‘Indonesia’ AND year=2010) AS ‘Population 2010’,
(SELECT population_years.population FROM population_years WHERE country = ‘Indonesia’ AND year=2000) -
(SELECT population_years.population FROM population_years WHERE country = ‘Indonesia’ AND year=2010) AS difference;