World Population

This project is slightly different than others you have encountered thus far on Codecademy. Instead of a step-by-step tutorial, this project contains a series of open-ended requirements which describe the project you’ll be building. There are many possible ways to correctly fulfill all of these requirements, and you should expect to use the internet, Codecademy, and other resources when you encounter a problem that you cannot easily solve.

You’ll work with a dataset of world population by country data from recent years. You’ll write queries to retrieve interesting data and answer a set of specific questions.

Project 1

#load DBI lybrary to use SQL in R
library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "db.sqlite")

In this project, you’ll answer questions using a database of world population by country.

The schema of the database is:

• Column:
  • Country = STRING
  • population = NUMBER (in millions)
  • year = NUMBER

Refer to this schema as you write queries to the database.

When you finish this project, you should be able to answer each the questions that follow using a single SQL query.

1 - The first query has already been written for you to answer the following question:

What years are covered by the dataset? (you can manually count the number of years returned).

Continue adding your queries below the first one as you proceed.

SELECT DISTINCT year from population_years;

2 - What is the largest population size for Gabon in this dataset?

SELECT Max(population)
from population_years
WHERE country = "Gabon";

3 - What were the 10 lowest population countries in 2005?

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

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

5 - How many countries in this dataset have the word “Islands” in their name?

SELECT COUNT (*)
FROM population_years
WHERE country LIKE "%Islands%";

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

Note: it’s okay to figure out the difference by hand after pulling the correct data.

SELECT * 
FROM population_years
WHERE country = "Indonesia" and year = 2000
OR country = "Indonesia" and year = 2010;

Project 2

The schema of the database is:

countries:

• id = INTEGER (Primary Key)
• name = TEXT
• continent = TEXT

population_years:

• id = INTEGER (Primary Key)
• population = NUMBER (in millions)
• year = NUMBER
• country_id = INTEGER (Foreign Key)

Refer to this schema as you write queries to the database.

When you finish this project, you should be able to answer each the questions that follow using a single SQL query.

#load DBI lybrary to use SQL in R
library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "db2.sqlite")

1 - How many entries in the countries table are from Africa?

SELECT COUNT (*) 
FROM countries
WHERE continent = "Africa";

2 - What was the total population of the continent of Oceania in 2005?

SELECT SUM(population)
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2005
AND continent = 'Oceania';

3 - What is the average population of countries in South America in 2003?

SELECT AVG(population)
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2003
AND continent = 'South America';

4 - What country had the smallest population in 2007?

SELECT MIN (population),
name
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2007;

5 - What is the average population of Poland during the time period covered by this dataset?

SELECT AVG(population),
name
FROM population_years
INNER JOIN countries ON
countries.id = population_years.country_id
WHERE name= "Poland";

6 - How many countries have the word “The” in their name?

SELECT COUNT(*)
FROM countries
WHERE name LIKE "%The%";

7 - What was the total population of each continent in 2010?

SELECT SUM(population),
continent
FROM population_years
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2010
GROUP BY continent;