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 * from country WHERE name like '%land';

-- 3. What are the names of the cities with population in between 500,000 and 1 million?
SELECT * FROM city WHERE population between 500000 and 1000000;

-- 4. What's the name of all the countries on the continent ‘Europe’?
SELECT * FROM country Where  continent = 'Europe';

-- 5. What are the names of all the cities in the Netherlands?
Select * from city WHERE countryCode = 'NLD';

-- 6. What is the population of Rotterdam?
Select name , population  from city where name ='Rotterdam';


-- 7. Which countries don't have a head of state? Hint: looks for NULL and '' values
SELECT name 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 ORDER BY population limit 10 ;

-- 9. What countries in Africa have the local name the same as their common name?
Select * From country Where continent = 'Africa' AND name = LocalName;

-- 10. What countries have Spanish as official language? Hint: see countrylanguage table
Select * From countrylanguage Where IsOfficial = 'T' AND Language = 'Spanish';

-- 11. What countries have official languages spoken between 1% and 10% of the population?
Select * From countrylanguage Where IsOfficial = 'T' AND 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 language From countrylanguage Where Percentage > 90;

-- 13. In which countries is 'Creole English' used? Order by descending percentage of speakers
 Select*From countrylanguage Where language = 'Creole English' Order by 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 * FROM country WHERE IndepYear IS NOT NULL AND GovernmentForm like '%Republic%' Order by indepYear 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, country.population, countrylanguage.language , countrylanguage.percentage, FLOOR(countrylanguage.percentage / 100 * country.population ) AS absoluteValueOfSpeakers FROM country Join countrylanguage ON country.code = countrylanguage.countrycode ;

-- 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 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';