World Populations Challenge Project (SQL)

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

Did mine using the pandas and sqlite3 python libraries. (Also consulted DB Browser a tiny bit!)

1 Like

any questions/comments welcome!