-
-
Save juryp/35d88fe136a55f62590b4fc8d0185b25 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 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 = '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 | |
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 country.name | |
FROM country | |
JOIN countrylanguage ON country.code = countrylanguage.countrycode | |
WHERE countrylanguage.isofficial = 'T' AND countrylanguage.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 country.name, countrylanguage.percentage | |
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 | |
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 name, language, (Percentage / 100) * Population AS 'number' | |
FROM country c | |
JOIN countrylanguage c2 ON c.Code = c2.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 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' | |
GROUP BY countrycode; |
-- 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;
you are selecting the most populated
-- 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';
you are selecting any country that has Spanish
as language even if not official
-- 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'
GROUP BY countrycode;
GROUP BY
is not needed
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
My answers are here: https://gist.github.com/juryp/35d88fe136a55f62590b4fc8d0185b25