databases-week1-exercises.sql

-- What are the names of countries with population greater than 8 million?
select name from country where Population > 8000000;

-- What are the names of countries that have “land” in their names?
select name from city c where Name like '%land%';

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

-- What's the name of all the countries on the continent ‘Europe’?
select name from country where Continent = 'europe';

-- What are the names of all the cities in the Netherlands?
select name from city c where c.CountryCode = (select code from country c2 where name = 'Netherlands');

-- What is the population of Rotterdam?
select population from city c where Name = 'Rotterdam';

-- Which countries don't have a head of state? Hint: looks for NULL and '' values
select * from country c where HeadOfState is null or HeadOfState ='';

-- What's the top 10 least populated cities? Return the name and population
select * from city order by Population limit 10;

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

-- What countries have Spanish as official language? Hint: see countrylanguage table
select * from country c where c.code in (select countrycode from countrylanguage c2 where c2.language = 'spanish'); 

-- What countries have official languages spoken between 1% and 10% of the population?
select * from countrylanguage c ;

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

-- In which countries is 'Creole English' used? Order by descending percentage of speakers
select name from country c where c.code in (select countrycode from countrylanguage c where language = 'Creole English' order by Percentage desc);

-- 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 c where IndepYear is not null order by IndepYear limit 5;

-- 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, (Percentage / 100)*Population as 'number' from country c join countrylanguage c2 ON c.Code = c2.CountryCode;

-- What is the total population of the world?
select sum(population) from country;

-- 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 c join country c2 on c.CountryCode = c2.Code where c2.Name = 'Belgium' GROUP BY c.CountryCode;

My answer are here: https://gist.github.com/EnnyFRANS/b7e766b5ea483e1050a36b68e917efbd
completed.

Question 2: The query selects from the city table instead of the country table.
Question 4: The continent name should be capitalized correctly.
Question 5: It works but it could be alot more simple, can you see how?
Question 7: We only want names of countries
Question 8: We only want name and population of cities.
Question 9: We only want the name of countries.
Question 10: We only want name of countries where Spanish is the official language.
Question 11: Incomplete.
Question 13: The subquery should not contain an ORDER BY clause.
Question 14: The query should filter by GovernmentForm containing 'republic'.
Question 3 (Bonus): could be simpler and does not check on official language.