Codecademy export

population_queries.sqlite

-- This is the first query:

SELECT DISTINCT year from population_years;

-- Add your additional queries below:
SELECT population FROM population_years
ORDER BY country = 'Gabon' DESC
LIMIT 5;

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

SELECT country FROM population_years
 WHERE population > 100 AND year = 2010;
 
SELECT DISTINCT country FROM population_years
WHERE country LIKE '%Islands%';
 
SELECT population, year FROM population_years
 WHERE country = 'Indonesia' 
 AND year BETWEEN 2000 AND 2011
 ORDER BY year asc;

SELECT DISTINCT year
FROM population_years;

SELECT * FROM population_years
WHERE country = 'Gabon'
ORDER BY population DESC
LIMIT 1;

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

SELECT DISTINCT country
FROM population_years
WHERE population > '100.000000'
AND year = '2010';

SELECT * FROM population_years
WHERE country LIKE '%Islands%';

SELECT * FROM population_years
WHERE year BETWEEN '2000' AND '2011'
AND country = 'Indonesia';

SELECT MAX(population)
FROM population_years
WHERE year BETWEEN '2000' AND '2011'
AND country = 'Indonesia';

SELECT MIN(population)
FROM population_years
WHERE year BETWEEN '2000' AND '2011'
AND country = 'Indonesia';

-- This is the first query:

SELECT DISTINCT year from population_years;

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

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

SELECT DISTINCT country
FROM population_years
WHERE population > 100 AND year = 2010;

SELECT COUNT(DISTINCT country)
FROM population_years
WHERE country LIKE '%Islands%';

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

SELECT DISTINCT year from population_years;

-- Add your additional queries below:

SELECT country, population
FROM population_years
WHERE country LIKE 'gabon';

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

SELECT DISTINCT country
FROM population_years
WHERE population > 100.00000
AND year = 2010;

SELECT DISTINCT country
FROM population_years
WHERE country LIKE '%islands%';

SELECT *
FROM population_years
WHERE country = 'Indonesia'
AND year = 2000;

SELECT *
FROM population_years
WHERE country = 'Indonesia'
AND year = 2010;

https://gist.github.com/ebb8fdf6082083aaed75ee612086ef6b

-- This is the first query:

SELECT DISTINCT year from population_years;

-- Add your additional queries below:

SELECT population
FROM population_years
ORDER BY country = 'Gabon' DESC
LIMIT 5;

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

SELECT DISTINCT country
FROM population_years
WHERE population > '100.000000' AND year = 2010;

SELECT *
FROM population_years
WHERE country LIKE "%Islands%";

SELECT *
FROM population_years
WHERE year BETWEEN 2000 AND 2010 AND country = 'Indonesia';

SELECT MAX(population)
FROM population_years
WHERE year BETWEEN 2000 AND 2010 AND country = 'Indonesia';

SELECT MIN(population)
FROM population_years
WHERE year BETWEEN 2000 AND 2010 AND country = 'Indonesia';

-- This is the first query:

SELECT DISTINCT year from population_years;

-- Add your additional queries below:

--What is the largest population size for Gabon in this dataset?
SELECT * from population_years where country = 'Gabon' ORDER BY population desc LIMIT 1;

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

SELECT country, population from population_years where year =2005 ORDER BY population LIMIT 10;

--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 > 1000.00000 AND year = 2010;

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

SELECT distinct country from population_years where country LIKE '%Islands%';

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

Select * from population_years where year Between 2000 AND 2010 AND country = 'Indonesia' order by year ;

This is my first attempt at writing my own SQL code

SELECT * FROM population_years
WHERE country = 'Gabon'
ORDER BY population DESC
LIMIT 1;

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

SELECT * FROM population_years
WHERE year = 2010
AND population > 100;

SELECT DISTINCT country from population_years
WHERE country LIKE '%ISLANDS%';

SELECT * FROM population_years
WHERE country = 'Indonesia'
AND year BETWEEN 2000 AND 2010
ORDER BY year;

-- 4 --

SELECT country, MAX(population) as max_pop_gabon
FROM population_years
WHERE country="Gabon";

-- 5 --
SELECT country, MIN(population) as min_pop
FROM population_years
WHERE year='2005'
GROUP BY country
ORDER BY min_pop
LIMIT 10;

-- 6 --
SELECT DISTINCT country as large_countries
FROM population_years
WHERE population>100 AND year='2010';

-- 7 --
SELECT DISTINCT country as islands
FROM population_years
WHERE country LIKE '%Islands%';

-- 8 --
SELECT year, population, population - LAG(population, 1) OVER(ORDER BY year) AS difference
FROM population_years
WHERE country="Indonesia" AND (year="2000" OR year="2010");

--Some different queries with useful results:

SELECT MAX(population)
FROM population_years
WHERE country = 'Gabon';

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

SELECT DISTINCT country, population
FROM population_years
WHERE population > 100 AND year = 2010;

SELECT DISTINCT country
FROM population_years
WHERE country LIKE '%Islands%';

SELECT COUNT(DISTINCT country)
FROM population_years
WHERE country LIKE '%Islands%';

SELECT population
FROM population_years
WHERE country = 'Indonesia' AND year IN (2000, 2010);