databases-week1-exercises.sql

gistfile1.txt

-- 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 country
WHERE Name
LIKE '%land%';

-- 3. What are the names of the cities with population in between 500,000 and 1 million?
SELECT Name
FROM country
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 Name
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 DESC 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 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 DISTINCT country.name
FROM country
JOIN countrylanguage
ON country.code = countrylanguage.countryCode
WHERE countrylanguage.IsOfficial = 'T'
AND countrylanguage.Percentage > 1
AND countrylanguage.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 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
FROM country
WHERE 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, countrylanguage.language, country.population * countrylanguage.percentage
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 country.code, COUNT (language) AS 'Number of Languages' FROM country JOIN countrylanguage ON country.code = countrylanguage.countryCode;