World Populations Challenge Project (SQL)

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 :slight_smile:

– This is the first query:

SELECT DISTINCT year from population_years;

– Add your additional queries below:

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 :slight_smile:

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:

[https://gist.github.com/42e19f48d07c3f3c6edc1be9900ff15d]

– 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;

Here are my solutions:

Interested to learn and use the JOIN clause in some of the other solutions.

My solutions for ‘World Population SQL Practice’ :

Here are the solution queries for the World_Populations Project.
I am still learning to use DB Browser and I am still practice using sqlite.exe.
I decided to complete the project on the Codecademy website.
I added another query at the top to get an idea of how many countries where represented in the database.

I have a question. How do you determine what tables are available in the file from the sqlite> prompt?

My Code:

My solution:

– This is the first query:

SELECT DISTINCT year from population_years;

– Add your additional queries below:

– Largest population size for Gabon

SELECT country, population

FROM population_years

WHERE country = ‘Gabon’

ORDER BY population DESC

LIMIT 1;

– 10 lowest population countries in 2005

SELECT *

FROM population_years

WHERE year = 2005

ORDER BY population ASC

LIMIT 10;

– Countries with population of over 100 million in 2010

SELECT DISTINCT country, population

FROM population_years

WHERE population > 100

AND year = 2010;

– Countries that have word ‘Islands’ in their name

SELECT count(*)

FROM population_years

WHERE country LIKE ‘%Islands%’;

–Difference in population

SELECT population

FROM population_years

WHERE population BETWEEN 2000 AND 2010;

heres mine:

I am so glad to finish this project!

Glad to finish project.