Last active
June 22, 2024 13:26
-
-
Save stefan-000/812a58d1c950db694a262ee81c8da7d7 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 = '' 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 FROM country where Continent = 'Africa' AND Name = LocalName | |
-- 10. What countries have Spanish as official language? Hint: see countrylanguage table | |
SELECT country.name, countrylanguage.language | |
FROM | |
country | |
JOIN | |
countrylanguage | |
ON country.Code = countrylanguage.countrycode | |
WHERE countrylanguage.language = 'Spanish' AND countrylanguage.IsOfficial = 'T' | |
-- 11. What countries have official languages spoken between 1% and 10% of the population? | |
SELECT country.name, countrylanguage.language, countrylanguage.Percentage | |
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 | |
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 country.Name, country.indepyear, country.GovernmentForm | |
FROM | |
country | |
WHERE country.GovernmentForm LIKE '%Republic%' | |
ORDER BY country.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/100) AS 'Number of Speakers' | |
FROM | |
country | |
JOIN | |
countrylanguage | |
ON country.Code = countrylanguage.countrycode | |
-- BONUS | |
-- 1. What is the total population of the world? | |
SELECT SUM(Population) as "World's Total Population" FROM country | |
-- 2. What is the average population of countries in Europe? | |
SELECT AVG(Population) as 'Average Population in Europe' 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' |
Hi Sebastien, thank you for the suggestions, I have corrected the queries.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Question 3: The query selects from the
country
table instead of thecity
table.Question 6: The query should only select the
Population
column.Question 8: The query orders by descending population, which returns the most populated cities instead of the least populated ones.
Question 10: The query does not check if the language is official.
Question 12: The query does not ensure distinct language names and joins with the country table unnecessarily.
Question 13: The query orders by country population instead of the percentage of speakers of 'Creole English'.
Question 14: The query joins with the countrylanguage table unnecessarily.
Question 15: The query should order the columns to return the country name first, then the language, and then the number of speakers.
Question 1 (Bonus): The query calculates the average population instead of the total population.