-
-
Save nibishakajean/038a5026b41bd94887d228e1ab3b0323 to your computer and use it in GitHub Desktop.
-- 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 c 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; | |
-- 8. What's the top 10 least populated cities? Return the name and population | |
SELECT name from city order by Population ASC 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 CountryCode from countrylanguage where LANGUAGE ="spanish" and IsOfficial ="T"; | |
-- 11. What countries have official languages spoken between 1% and 10% of the population? | |
select countrycode 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 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 from country where GovernmentForm ="republic" ORDER by IndepYear ASC 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,country.Population ,countrylanguage.Language,country.Population*countrylanguage.Percentage/100 as people_speak_each_language | |
from country | |
inner 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 CountryCode,COUNT(Language) AS "Number of Languages" from countrylanguage WHERE CountryCode ="BEL" AND IsOfficial ="T"; | |
-- MORE | |
-- 1. Retrieve the names of all the cities along with their respective country names, make sure the column names are easy to understand. | |
SELECT city.name as city,country.Name as country | |
from city | |
inner join country on city.CountryCode =country.Code ; | |
-- 2. Find the average life expectancy of countries in the continent 'Europe'. | |
SELECT AVG(LifeExpectancy) | |
FROM country | |
WHERE Continent = "Europe"; | |
-- 3. Get the names and populations of cities in the district 'California'. | |
SELECT name,Population FROM city where District ="california" ; | |
-- 4. Retrieve the capital city of each country along with the country name. | |
SELECT city.Name ,country.Name from city inner join country WHERE city.CountryCode =country.Code; | |
-- 5. Find the largest city by population in each country. | |
SELECT Name as "largest city",CountryCode | |
FROM city | |
WHERE (CountryCode , population) IN ( | |
SELECT CountryCode , MAX(population) | |
FROM city | |
GROUP BY CountryCode | |
); | |
-- 6. List the names of all cities with a population of over 1 million in the continent of 'Asia'. | |
SELECT city.Name from city inner join country WHERE city.CountryCode =country.Code AND country.Continent ='Asia' and city.Population >1000000; | |
-- 7. Get the names and continents of countries that do not have an official language recorded in the database. | |
SELECT country.Name ,country.Continent,country.code | |
FROM country | |
LEFT JOIN countrylanguage | |
ON country.code = countrylanguage.countrycode AND countrylanguage.isofficial = 'T' | |
WHERE countrylanguage.countrycode IS NULL; | |
-- 8. List the countries in the 'Oceania' continent with an average life expectancy over 70. | |
SELECT Name from country where Continent ="oceania" and LifeExpectancy >70 | |
-- 9. Find the total number of languages spoken in the continent 'Africa'. | |
SELECT COUNT(countrylanguage.Language) AS "total number of Languages in africa" from country LEFT JOIN countrylanguage | |
ON country.code = countrylanguage.countrycode | |
WHERE country.Continent ='Africa' ; | |
-- 10. Retrieve the names and populations of cities located in countries with a surface area greater than 1 million square kilometers. | |
SELECT city.Name ,city.Population from city LEFT JOIN country | |
ON country.code = city.countrycode | |
WHERE country.SurfaceArea >1000000 ; | |
-- 11. Retrieve the names and populations of capital cities with populations over 500,000. | |
SELECT city.Name ,city.Population from city LEFT JOIN country | |
ON country.code = city.countrycode | |
WHERE city.Population >500000; | |
-- 12. List the names and continents of countries where English is an official language. | |
SELECT country.Name ,country.Continent | |
FROM country | |
LEFT JOIN countrylanguage | |
ON country.code = countrylanguage.countrycode AND countrylanguage.isofficial = 'T' | |
WHERE countrylanguage.`Language` ="English"; | |
-- 13. Find the name of the country with the highest life expectancy. | |
SELECT name from country order by LifeExpectancy DESC limit 1; | |
-- 14. Get the names of countries in the 'South America' continent with cities having a population over 1 million. | |
SELECT country.Name | |
FROM country | |
LEFT JOIN city | |
ON country.code = city.CountryCode AND city.Population >1000000 | |
WHERE country.Continent="South America" AND city.Population is NOT NULL; | |
-- 15. Find the name and population of the smallest city (by population) in the country 'India'. | |
SELECT city.Name ,city.Population | |
FROM country | |
LEFT JOIN city | |
ON country.code = city.CountryCode | |
WHERE country.Name ="India" ORDER BY city.Population ASC LIMIT 1 ; | |
-- 16. Retrieve the country name and its corresponding capital city's name where the capital's population is more than 1 million. | |
SELECT country.Name ,city.Name | |
FROM country | |
LEFT JOIN city | |
ON country.code = city.CountryCode | |
WHERE city.Population >1000000 ; | |
-- 17. List the names of countries that have no cities in the database. | |
SELECT country.Name | |
FROM country | |
LEFT JOIN city | |
ON country.code =city.countrycode | |
WHERE city.Name IS NULL; | |
-- 18. Get the name and population of the largest city in the continent 'South America'. | |
SELECT city.Name,city.Population | |
FROM city | |
LEFT JOIN country | |
ON city.countrycode= country.code | |
WHERE country.Continent ="South America" ORDER by city.Population DESC limit 1 ; | |
-- 19. List the names and populations of all cities in countries where the official language is 'Spanish'. | |
SELECT city.Name,city.Population | |
FROM city | |
LEFT JOIN country | |
ON city.countrycode= country.code | |
LEFT JOIN countrylanguage | |
ON city.countrycode= countrylanguage.CountryCode | |
WHERE countrylanguage.`Language` ="Spanish" AND countrylanguage.IsOfficial ="T"; | |
-- 20. Get the name of the country and the population of the city with the highest population in that country. | |
SELECT country.Name,city.Population from country | |
LEFT JOIN city | |
ON country.code= city.countrycode | |
ORDER by city.Population DESC limit 1 ; |
Question 4: The continent name should be capitalized correctly, in some cases names can be case sensitive.
Question 7: The query only checks for NULL and should also check for empty strings.
Question 8: The query does not include the population column in the SELECT clause.
Question 10: The query selects CountryCode instead of the country name and uses lowercase "spanish" instead of "Spanish".
Question 11: The query does not check if the language is official and selects CountryCode instead of the country name.
Question 13: The query selects CountryCode instead of the country name and uses incorrect case for "Creole English".
Question 14: The query selects countris that have a gouvernment form that is exactly "republic" where we ask for some form of republic.
Question 15: The population was not asked.
Tried each 100 %