Codecademy export

population_queries.sqlite

/* 2. Display the tables */
SELECT * FROM countries
LIMIT 5;
SELECT * FROM population_years 
LIMIT 5;

/* Q3 How many entries in the countries table are from Africa? */
SELECT COUNT(*) AS 'No of country from Africa'
FROM countries
WHERE continent = 'Africa';

/* Q4 Total population of continent of Oceania in 2005 */
SELECT ROUND(SUM(pop.population),2) AS 'Total population of Oceania, 2005'
FROM population_years AS pop
JOIN countries AS c
ON pop.country_id = c.id
WHERE pop.year = 2005 AND
c.continent = 'Oceania';

/* Q5 Avg populatoin of countries in South America in 2003 */
SELECT ROUND(AVG(pop.population),2) AS
'AVG population of countries in South America, 2003'
FROM population_years AS pop
JOIN countries AS c
ON pop.country_id = c.id
WHERE pop.year = 2003 AND
c.continent = 'South America';

/* Q6 Country with smallest populatin in 2007 */
SELECT c.name AS 'Country', c.continent, pop.population, pop.year
FROM population_years AS pop
INNER JOIN countries AS c
ON pop.country_id = c.id
WHERE pop.year = 2007 AND
pop.population IS NOT NULL
ORDER BY pop.population
LIMIT 1;

/* Q7 AVG population of Poland */
SELECT ROUND(AVG(pop.population),2) AS 'AVG population in Poland'
FROM population_years AS pop
JOIN countries AS c
ON pop.country_id = c.id
WHERE c.name = 'Poland';

/* range of years in population table */
SELECT MIN(DISTINCT year) AS 'year from', MAX(DISTINCT year) AS 'year to', MAX(DISTINCT year)-MIN(DISTINCT year) AS 'year span'
FROM population_years;

/* 8. How many countries have teh word "The" in their name? */
SELECT COUNT(name) AS 'No of country with "The" in their country name incl'
FROM countries
WHERE name LIKE '% The';

SELECT name
FROM countries
WHERE name LIKE '% The';

/* Total population of each continent in 2010 */
SELECT c.continent, ROUND(SUM(pop.population),2) AS 'Total population'
FROM population_years AS pop
INNER JOIN countries AS c
ON pop.country_id = c.id
WHERE pop.year = 2010
GROUP BY c.continent;