databases-week1-exercises.sql

-- 1. What are the names of countries with population greater than 8 million?
SELECT Name 
FROM country
WHERE Population > 8000000;

-- 2. What are the names of countries that have “land” in their names?
SELECT name from city where name like "%land";

-- 3. What are the names of the cities with population in between 500,000 and 1 million?
SELECT name from city where Population > 500000 and Population < 1000000;

-- 4. What's the name of all the countries on the continent ‘Europe’?
SELECT name from country where Continent = "Europe";

-- 5. What are the names of all the cities in the Netherlands?
SELECT name from city where CountryCode = 'nld';

-- 6. What is the population of Rotterdam?
SELECT Population from city where name = 'rotterdam';

-- 7. Which countries don't have a head of state? Hint: looks for NULL and '' values
SELECT * from country where HeadOfState is null;

-- 8. What's the top 10 least populated cities? Return the name and population
SELECT name, population from city limit 10;

-- 9. What countries in Africa have the local name the same as their common name?
SELECT * from country where name = LocalName and Continent = 'Africa';

-- 10. What countries have Spanish as official language? Hint: see countrylanguage table
SELECT country.name from country join countrylanguage  on country.code = countrylanguage.CountryCode where countrylanguage.`Language` = 'Spanish';

-- 11. What countries have official languages spoken between 1% and 10% of the population?
SELECT * from countrylanguage where IsOfficial = 't' and Percentage > 1 and Percentage < 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 language from countrylanguage where Percentage > 90 ;

-- 13. In which countries is 'Creole English' used? Order by descending percentage of speakers
SELECT country.name from country join countrylanguage on country.code = countrylanguage.CountryCode 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, IndepYear, GovernmentForm from country where IndepYear is not null and GovernmentForm like   '%republic%' order by IndepYear asc limit 5;

-- 15. For each country, how many people speak each language? Important: we want absolute values, not a percentage. Return the name of the country, the name of the language, and number of speakers of that language - Hint: you need both the country and countrylanguage tables - Hint: you can do calculations between columns, for example (SELECT a - b from table;)
SELECT country.name, countrylanguage.`Language`, round(country.Population*(countrylanguage.Percentage/100)) from country join countrylanguage on country.Code = countrylanguage.CountryCode ;


-- BONUS
-- 1. What is the total population of the world?
SELECT SUM(population) as TotalWorldPopulation FROM country;

-- 2. What is the average population of countries in Europe?
SELECT AVG(population) AS AverageEuropePopulation FROM country WHERE 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(*) AS "Numbers 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 CityName, country.Name AS CountryName FROM city JOIN country ON city.CountryCode = country.Code;


-- 2. Find the average life expectancy of countries in the continent 'Europe'.
SELECT AVG(LifeExpectancy) AS AverageLifeExpectancy FROM country where continent = 'Europe';


-- 3. Get the names and populations of cities in the district 'California'.
SELECT Name AS CityName, Population FROM city WHERE District = 'California';


-- 4. Retrieve the capital city of each country along with the country name.
SELECT country.Name AS CountryName, city.Name AS CapitalCity FROM country JOIN city ON country.Capital = city.ID;