CodeAcademy sql basic query practice for World population dataset

gistfile1.txt

SELECT DISTINCT year AS 'Years covered by dataset'
from population_years;

-- Add your additional queries below:
-- Largest population size for Gabon
SELECT MAX(population) AS 'Largest population size for gabon'
FROM population_years
WHERE country == 'Gabon';

-- 10 lowest population countries in 2005
SELECT country, population AS '10 lowest population in 2005 (in millions)'
FROM population_years
WHERE year == 2005
ORDER BY population ASC
LIMIT 10;

-- Distinct countries with a population of over 100 million in year 2010
SELECT DISTINCT country AS 'Country with more than 100 million people', population AS 'Population (in millions)'
from population_years
WHERE year == 2010 and population > 100
ORDER BY population;

--Number of countries with islands in their name
SELECT COUNT(country) AS 'Number of countries with islands in their name'
FROM population_years
WHERE country LIKE '%islands%';

--Difference in population between 2000 and 2010 in indonesia
--First get population in 2010
SELECT population AS 'Indonesia population in 2010'
FROM population_years
WHERE country == 'Indonesia' and year ==2010;

--Substract population obtained from previous query
SELECT population - 242.96834 AS 'Difference in population between 2010 and 2000 in Indonesia'
FROM population_years
WHERE country == 'Indonesia' and year == 2000;