World Populations Challenge Project (SQL)

World Populations SQL Practice

Instructions

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

    The schema of the database is:

    Column Type Notes
    country STRING
    population NUMBER (in millions)
    year NUMBER
  2. 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.

    -- This is the first query:
    
    SELECT DISTINCT year from population_years;
    
    -- Add your additional queries below:
    
    year
    2000
    2001
    2002
    2003
    2004
    2005
    2006
    2007
    2008
    2009
    2010
  3. What is the largest population size for Gabon in this dataset?

    SELECT
    country,
    Max(population) AS 'Gabon Maximum Population',
    year
    from
    population_years
    WHERE
    country = 'Gabon';
    
    country Gabon Maximum Population year
    Gabon 1.54526 2010
  4. What were the 10 lowest population countries in 2005?

    SELECT
    *
    from
    population_years
    WHERE
    YEAR = 2005
    ORDER BY
    2
    LIMIT
    10;
    
    country population year
    Niue 0.00216 2005
    Falkland Islands (Islas Malvinas) 0.00297 2005
    Montserrat 0.00453 2005
    Saint Pierre and Miquelon 0.0062 2005
    Saint Helena 0.00748 2005
    Nauru 0.01001 2005
    Cook Islands 0.0136 2005
    Turks and Caicos Islands 0.02057 2005
    Virgin Islands, British 0.02268 2005
    Gibraltar 0.02846 2005
  5. What are all the distinct countries with a population of over 100 million in
    the year 2010?

    SELECT
      *
    from
      population_years
    WHERE
      year = 2010
      AND population > 100
    ORDER BY
      2;
    
    country population year
    Mexico 112.46886 2010
    Japan 126.80443 2010
    Russia 139.39021 2010
    Nigeria 152.21734 2010
    Bangladesh 156.11846 2010
    Pakistan 184.40479 2010
    Brazil 201.10333 2010
    Indonesia 242.96834 2010
    United States 310.23286 2010
    India 1173.10802 2010
    China 1330.14129 2010
  6. How many countries in this dataset have the word “Islands” in their name?

    SELECT
    *
    from
    population_years
    WHERE
    country LIKE '%Islands%'
    GROUP BY
    1;
    
    country population year
    Cayman Islands 0.05021 2010
    Cook Islands 0.01149 2010
    Falkland Islands (Islas Malvinas) 0.00314 2010
    Faroe Islands 0.04906 2010
    Solomon Islands 0.5592 2010
    Turks and Caicos Islands 0.02353 2010
    U.S. Pacific Islands 0.24221 2010
    Virgin Islands, U.S. 0.10975 2010
    Virgin Islands, British 0.02494 2010
  7. What is the difference in population between 2000 and 2010 in Indonesia?

    SELECT
    *
    FROM
    population_years
    WHERE
    country = 'Indonesia'
    AND (
        year = 2000
        OR year = 2010
    );
    
    country population year
    Indonesia 214.67661 2000
    Indonesia 242.96834 2010

Link to code above. I was a bit confused by the solution code for question 7 as it appears to only return the unique names of the countries with ‘Island’ in the name. Has the question changed since the solution code was written?

Cheers
Andy

Here is mine please review it if you have time :slight_smile:

my solution to World Populations Challenge Project SQL part 1

I think I got it right for the most part but I could have make a few things more concise.

My Answers to the World Population Challenge Project(SQL)

SELECT DISTINCT year from population_years;

SELECT MAX(population)

FROM population_years

WHERE country = ‘Gabon’;

SELECT country, year, population

FROM population_years

WHERE year = ‘2005’

ORDER BY population ASC

LIMIT 10;

SELECT DISTINCT country, year, population

FROM population_years

WHERE population > 100

AND year = ‘2010’;

SELECT country, year, population

FROM population_years

WHERE country LIKE ‘%Islands%’;

SELECT population, year

FROM population_years

WHERE country = ‘Indonesia’

AND (year = 2010 OR year = 2000);

WITH pop_2000 AS

(SELECT population

FROM population_years

WHERE year = 2000

AND country = ‘Indonesia’

),

pop_2010 AS

(SELECT population

FROM population_years

WHERE year = 2010

AND country = ‘Indonesia’

)

SELECT pop_2010.population - pop_2000.population AS ‘population difference’

FROM pop_2000, pop_2010;

– This is the first query:

SELECT DISTINCT year from population_years;

– Add your additional queries below:

SELECT COUNT(DISTINCT year) from population_years;

SELECT year, country, MAX(population) FROM population_years

WHERE country = ‘Gabon’;

SELECT country, population FROM population_years

WHERE year = 2005

ORDER BY population ASC

LIMIT 10;

SELECT DISTINCT country FROM population_years

WHERE (population > 100) AND (year = 2010);

SELECT COUNT(country) FROM population_years

WHERE country LIKE ‘%Islands%’;

SELECT year, population FROM population_years

WHERE (year BETWEEN 2000 AND 2010) AND (country = ‘Indonesia’)

ORDER by year;

This is what I’ve gotten: https://gist.github.com/4f78bf7386ffd1e10c8454702bc577d9

Any feedback is welcome :slight_smile:

My solution to the World Populations SQL Project:

Tips for clean code:
No need parenthesis between 2 ___ and ___. The priority of the logical statement is basically the same as other languages: not > and > or. Thus the system will deal with the condition of ‘and’ first, then come to ‘or’.