databases-week1-exercises.sql

-- 1. What are the names of countries with population greater than 8 million?
select name, Population from city 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 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 FROM country WHERE continent = 'Antarctica';

-- 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 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, HeadOfState FROM country WHERE HeadOfState = '' or 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 name, LocalName FROM country WHERE continent = 'Africa' and Name = LocalName;

-- 10. What countries have Spanish as official language? Hint: see countrylanguage table
SELECT CountryCode,  language FROM countrylanguage WHERE language = 'spanish' and IsOfficial = 'T';

-- 11. What countries have official languages spoken between 1% and 10% of the population?
SELECT CountryCode, language FROM countrylanguage WHERE  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 CountryCode, language, Percentage 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 Name, IndepYear, GovernmentForm FROM country where GovernmentForm ='Republic' ORDER by IndepYear desc 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 name, language, Population from country join countrylanguage on country.Code = countrylanguage.CountryCode ;

-- BONUS

-- 1. What is the total population of the world?
select SUM(Population) as 'Total Population' from country;

-- 2. What is the average population of countries in Europe?
select Avg(Population) as 'Average 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 countrylanguage.IsOfficial='T' ;

-- 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, Percentage FROM countrylanguage WHERE  Percentage > 90 ;

by selecting also Percentage the languages returned are not unique, this is more evident if you order by language your query

--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 GovernmentForm ='Republic' ORDER by IndepYear desc limit 5;

by using GovernmentForm ='Republic' you are missing other kind of "republic", and by using desc order you get the countries which gain independence more recently

-- 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 name, language, Population from country join countrylanguage on country.Code = countrylanguage.CountryCode ;

the result will indicate that the entire population of any country speak all the languages spoken in that country.
Read with more attention the requirement and think about where the percentage of population of a country is stored and how to combine it with a specific language.