answers to Week 1's exercises.

databases-week1-exercises.sql

-- 1. What are the names of countries with population greater than 8 million?

SELECT Name , Population 
FROM country
WHERE Population > 8000000

-- 2. What are the names of countries that have “land” in their names?

SELECT Name  
FROM country
WHERE name LIKE '%land%'

-- 3. What are the names of the cities with population in between 500,000 and 1 million?

SELECT Name, Population 
FROM city 
WHERE Population BETWEEN 500000 AND 1000000

-- 4. What's the name of all the countries on the continent ‘Europe’?

SELECT name, Continent
FROM country  
WHERE Continent = 'Europe'

-- 5. What are the names of all the cities in the Netherlands?

SELECT city.name AS city, country.name AS country
FROM city 
JOIN country 
ON city.CountryCode  = country.Code 
WHERE country.name = 'Netherlands'

-- 6. What is the population of Rotterdam?

SELECT Name , Population 
FROM city 
WHERE city.name = 'Rotterdam'

-- >593321

-- 7. Which countries don't have a head of state? Hint: looks for `NULL` and `''` values

SELECT name, HeadOfState 
FROM country 
WHERE HeadOfState IS NULL OR HeadOfState = ''

-- 8. What's the top 10 least populated cities? Return the name and population

SELECT name, Population 
FROM city 
ORDER BY Population LIMIT 10

-- 9. What countries in Africa have the local name the same as their common name?

SELECT name, LocalName, Continent 
FROM country  
WHERE Continent = 'Africa' AND name = LocalName 

-- 10. What countries have Spanish as official language? Hint: see `countrylanguage` table

SELECT country.Name, countrylanguage.`Language`, countrylanguage.IsOfficial
FROM countrylanguage   
JOIN country 
ON countrylanguage.CountryCode = country.Code 
WHERE countrylanguage.IsOfficial = 'T' AND countrylanguage.`Language` = 'spanish'

-- 11. What countries have *official* languages spoken between 1% and 10% of the population?

SELECT country.Name, countrylanguage.`Language`, countrylanguage.Percentage
FROM countrylanguage   
JOIN country 
ON countrylanguage.CountryCode = country.Code 
WHERE countrylanguage.IsOfficial = 'T' AND countrylanguage.Percentage BETWEEN 1 AND 10

-- 12. What languages are spoken by over 90% of the population of a country? Return just the language names, but don't repeat entries

SELECT DISTINCT countrylanguage.`Language`
FROM countrylanguage   
WHERE countrylanguage.Percentage > 90

-- 13. In which countries is 'Creole English' used? Order by descending percentage of speakers 

SELECT country.Name, countrylanguage.Percentage
FROM countrylanguage   
JOIN country 
ON countrylanguage.CountryCode = country.Code 
WHERE countrylanguage.`Language`  = 'Creole English' 
ORDER BY countrylanguage.Percentage DESC 

-- 14. What are the 5 oldest countries (by independence date) with some form of republic government? Tip: there are multiple types of republic

SELECT name, GovernmentForm, IndepYear
FROM country    
WHERE GovernmentForm LIKE '%republic%' OR GovernmentForm like 'Socialistic%'
ORDER BY IndepYear LIMIT 5

-- Bonus 

-- 1. What is the total population of the world?

SELECT SUM(Population) 
FROM country 

-- 2. What is the average population of countries in Europe?

SELECT AVG(Population) 
FROM country 
WHERE country.Continent = 'Europe'

-- 3. How many *official* languages are spoken in Belgium (`BEL`)? 
-- Return the country code, and the number of languages as "Number of Languages"

SELECT CountryCode, COUNT(`Language`) AS "Number of Languages"
FROM countrylanguage  
WHERE CountryCode = 'BEL' 
AND IsOfficial = 'T'
GROUP BY CountryCode;

-- MORE

-- 1. Retrieve the names of all the cities along with their respective country names, make sure the column names are easy to understand.

SELECT city.Name AS city, country.Name AS country
FROM city 
JOIN country 
ON city.CountryCode = country.Code 

-- 2. Find the average life expectancy of countries in the continent 'Europe'.

SELECT Continent, AVG(LifeExpectancy) 
FROM country    
WHERE Continent ='Europe'
GROUP BY Continent

-- 3. Get the names and populations of cities in the district 'California'.

SELECT name, Population, District 
FROM city    
WHERE District = 'California'

-- 4. Retrieve the capital city of each country along with the country name.

SELECT city.name AS city , country.Name AS country
FROM city
JOIN country 
ON country.Capital = city.id

-- 5. Find the largest city by population in each country.
-- Tip: It is possible to use nested queries, for example, if you wanted to get all the countries which population
-- is greater than Spain's you would do:
-- SELECT Name FROM country WHERE Population > (SELECT Population FROM country WHERE Name = 'Spain')

SELECT city.name AS City , city.Population
FROM city
WHERE (city.CountryCode, city.Population) IN (
    SELECT CountryCode, MAX(Population)
    FROM city
    GROUP BY CountryCode
);

SELECT city.name AS City , city.Population, country.name AS Country
FROM city
JOIN country 
ON country.Code = city.CountryCode
WHERE (city.CountryCode, city.Population) IN (
    SELECT CountryCode, MAX(Population)
    FROM city
    GROUP BY CountryCode
);


-- 6. List the names of all cities with a population of over 1 million in the continent of 'Asia'.

SELECT city.name AS City , city.Population, country.Continent AS Continent
FROM city
JOIN country 
ON country.Code = city.CountryCode
WHERE city.Population > 1000000 AND country.Continent = 'Asia'

-- 7. Get the names and continents of countries that do not have an official language recorded in the database.

SELECT country.name AS Country, country.Continent
FROM country
WHERE country.Code NOT IN (
    SELECT CountryCode
    FROM countrylanguage
    WHERE IsOfficial = 'T'
);

-- 8. List the countries in the 'Oceania' continent with an average life expectancy over 70.

SELECT name AS Country, Continent, LifeExpectancy
FROM country 
WHERE Continent = 'Oceania' 
AND LifeExpectancy > 70

-- 9. Find the total number of languages spoken in the continent 'Africa'.

SELECT count(DISTINCT countrylanguage.`Language`) AS 'total number of languages'
FROM country 
JOIN countrylanguage 
ON country.Code = countrylanguage.CountryCode 
WHERE country.Continent = 'Africa' 

-- 10. Retrieve the names and populations of cities located in countries with a surface area greater than 1 million square kilometers.

SELECT city.name AS city, city.population
FROM city 
JOIN country 
ON country.Code = city.CountryCode
WHERE country.SurfaceArea > 1000000


SELECT city.name AS city, city.population
FROM city 
WHERE city.CountryCode IN (
    SELECT code
    FROM country 
    WHERE SurfaceArea > 1000000
);


-- 11. Retrieve the names and populations of capital cities with populations over 500,000.

SELECT city.name AS city, city.population
FROM city 
JOIN country 
ON country.capital = city.id
WHERE city.Population > 500000

-- 12. List the names and continents of countries where English is an official language.

SELECT country.name AS Country, country.Continent
FROM country
JOIN countrylanguage 
ON country.Code = countrylanguage.CountryCode
WHERE countrylanguage.Language = 'English' AND countrylanguage.IsOfficial = 'T';

-- 13. Find the name of the country with the highest life expectancy.

SELECT name AS Country, LifeExpectancy
FROM country 
ORDER BY LifeExpectancy DESC
LIMIT 1

SELECT Name AS Country, LifeExpectancy
FROM country
WHERE LifeExpectancy = (
    SELECT MAX(LifeExpectancy)
    FROM country
);

-- 14. Get the names of countries in the 'South America' continent with cities having a population over 1 million.

SELECT Name AS Country
FROM country
WHERE continent = 'South America' 
AND (country.code) IN (
    SELECT CountryCode
    FROM city
    WHERE Population  > 1000000
);

SELECT DISTINCT country.Name AS Country
FROM country
JOIN city 
ON city.CountryCode = country.Code
WHERE country.Continent = 'South America' 
AND city.Population > 1000000;

-- 15. Find the name and population of the smallest city (by population) in the country 'India'.

SELECT city.name AS city, city.population
FROM city 
JOIN country 
ON city.CountryCode = country.Code
WHERE country.name = 'India'
ORDER BY city.population
LIMIT 1

SELECT city.name AS city, city.population
FROM city 
JOIN country 
ON city.CountryCode = country.Code
WHERE country.name = 'India' 
AND city.population = (
	SELECT MIN(city.Population)
	FROM city 
	WHERE city.CountryCode = country.Code
);

-- 16. Retrieve the country name and its corresponding capital city's name where the capital's population is more than 1 million.

SELECT country.Name AS Country, city.name AS 'Capital City'
FROM country 
JOIN city 
ON country.Capital = city.id
WHERE city.Population > 1000000

SELECT country.Name AS Country, city.name AS 'Capital City'
FROM country 
JOIN city 
ON country.Capital = city.id
WHERE city.id IN (
	SELECT id
	FROM city 
	WHERE Population > 1000000
);

-- 17. List the names of countries that have no cities in the database.

SELECT country.Name AS Country
FROM country 
WHERE country.Code NOT IN (
    SELECT CountryCode
    FROM city
);

SELECT country.Name AS Country
FROM country
LEFT JOIN city 
ON country.Code = city.CountryCode
WHERE city.name IS NULL;

-- 18. Get the name and population of the largest city in the continent 'South America'.

SELECT city.name AS City, city.Population
FROM city
JOIN country 
ON country.Code = city.CountryCode
WHERE country.Continent = 'South America'
ORDER BY city.Population DESC 
LIMIT 1

SELECT city.name AS City, city.Population
FROM city
WHERE city.Population = (
    SELECT MAX(city.Population)
    FROM city
    JOIN country 
	ON country.Code = city.CountryCode
    WHERE country.Continent = 'South America'
);

-- 19. List the names and populations of all cities in countries where the official language is 'Spanish'.

SELECT city.name AS City, city.Population
FROM city
WHERE city.CountryCode IN (
    SELECT CountryCode 
    FROM countrylanguage 
    WHERE countrylanguage.`Language` = 'Spanish'
    AND countrylanguage.IsOfficial = 'T'
);

SELECT city.name AS City, city.Population
FROM city
JOIN countrylanguage 
ON city.CountryCode = countrylanguage.CountryCode 
WHERE countrylanguage.`Language` = 'Spanish'
AND countrylanguage.IsOfficial  = 'T'

-- 20. Get the name of the country and the population of the city with the highest population in that country.

SELECT country.name AS Country, city.Population AS MaxCityPopulation
FROM country
JOIN city 
ON country.Code = city.CountryCode
WHERE city.Population = (
    SELECT MAX(city.Population)
    FROM city
    WHERE city.CountryCode = country.Code
);