Last active
June 15, 2024 22:05
-
-
Save Ashrafpoless/b0f47b3f48ea5005fc5fbfa1cd3dd756 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 , population | |
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, population | |
from city | |
where Population > 500000 and Population <1000000 ; | |
-- 4. What's the name of all the countries on the continent ‘Europe’? | |
SELECT name, continent | |
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 name, population | |
from city | |
where Name = 'Rotterdam'; | |
-- 7. Which countries don't have a head of state? Hint: looks for NULL and '' values | |
select name, headofstate | |
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 | |
limit 10 ; | |
-- 9. What countries in Africa have the local name the same as their common name? | |
SELECT name,LocalName, continent | |
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 , countrylanguage.IsOfficial | |
from country join countrylanguage on country.code = countrylanguage.CountryCode | |
where language = 'spanish' and isOfficial = 't'; | |
-- 11. What countries have official languages spoken between 1% and 10% of the population? | |
select country.Name , countrylanguage.language , countrylanguage.IsOfficial, countrylanguage.Percentage | |
from country join countrylanguage on country.code = countrylanguage.CountryCode | |
where countrylanguage.Percentage > 1 and countrylanguage.Percentage < 10 and isOfficial = 't'; | |
or ----> | |
select Name , language , IsOfficial, Percentage | |
from country join countrylanguage on code = CountryCode | |
where isOfficial = 't' and 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 Name , language , Percentage | |
from country join countrylanguage on code = CountryCode | |
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 IndepYear IS NOT null | |
and 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, floor(Population *percentage / 100 ) as 'language speakers' | |
from country join countrylanguage on Code = 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 ; | |
-- 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 join country | |
where 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 as 'capital' , country.Name as 'country' | |
from city join country on Country.Capital = city.ID ; | |
-- 5. Find the largest city by population in each country. | |
SELECT Country.Name, MAX(city.Population) as MaxPopulation | |
FROM city join country on city.CountryCode = Country.Code | |
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 , city.population, Country.Continent | |
from city join country on city.CountryCode = Country.Code | |
where city.Population > 1000000 and Country.Continent = 'asia'; | |
-- 7. Get the names and continents of countries that do not have an official language recorded in the database. | |
SELECT country.name AS Country, country.Continent | |
FROM country | |
WHERE country.Code NOT IN ( | |
SELECT CountryCode | |
FROM countrylanguage | |
WHERE IsOfficial = 'T' | |
); | |
-- or ----> | |
SELECT country.Name, country.Continent | |
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, continent, LifeExpectancy | |
from country | |
where continent = 'Oceania' and LifeExpectancy > 70; | |
-- 9. Find the total number of languages spoken in the continent 'Africa'. | |
select count(distinct countrylanguage.language) as 'total Languages in africa' , Country.Continent | |
from countrylanguage join Country | |
on countrylanguage.CountryCode= country.Code | |
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 as city, city.Population, Country.Name as country | |
from city join country on city.CountryCode = Country.Code | |
where Country.SurfaceArea > 1000000 ; | |
-- 11. Retrieve the names and populations of capital cities with populations over 500,000. | |
SELECT city.Name as city, city.Population | |
from city join country on Country.Capital = city.ID | |
where city.Population > 500000; | |
-- 12. List the names and continents of countries where English is an official language. | |
SELECT country.name as country , country.continent | |
from country join countrylanguage | |
on country.Code = countrylanguage.CountryCode | |
where countrylanguage.language = 'english' and countrylanguage.IsOfficial = 't'; | |
-- 13. Find the name of the country with the highest life expectancy. | |
SELECT Name | |
FROM country | |
ORDER BY LifeExpectancy DESC | |
LIMIT 1; | |
-- or ----> | |
SELECT Name, LifeExpectancy | |
FROM country | |
WHERE LifeExpectancy = (SELECT MAX(LifeExpectancy) FROM country); | |
-- 14. Get the names of countries in the 'South America' continent with cities having a population over 1 million. | |
select distinct Country.Name as country | |
from country join city on Country.Code = city.CountryCode | |
where Country.Continent= 'South America' and city.Population > 1000000 ; | |
-- 15. Find the name and population of the smallest city (by population) in the country 'India'. | |
SELECT city.Name as city, city.Population , country.Name as country | |
from city join country | |
on city.countrycode = country.Code | |
where country.Name = 'india' order by population | |
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 as country, city.Name as capital , city.population | |
from country join city | |
on Country.Code = city.CountryCode | |
where country.Capital = city.ID | |
and city.Population >1000000 ; | |
-- 17. List the names of countries that have no cities in the database. | |
SELECT country.Name as country | |
from country | |
where Country.Code not in ( | |
select city.CountryCode | |
from city | |
where country.Code = city.CountryCode | |
) ; | |
-- 18. Get the name and population of the largest city in the continent 'South America'. | |
select city.Name as city, city.Population, country.Continent | |
from city | |
join country on city.CountryCode = Country.Code | |
where Country.Continent = 'South America' | |
order by city.Population desc | |
limit 1; | |
select city.Name as city, city.Population, country.Continent | |
from city | |
join country on city.CountryCode = Country.Code | |
where city.Population in( | |
select max(city.Population) | |
from city | |
join country on city.CountryCode = Country.Code | |
where Country.Continent = 'South America' | |
); | |
-- 19. List the names and populations of all cities in countries where the official language is 'Spanish'. | |
SELECT city.Name as city, city.Population, countrylanguage.Language , countrylanguage.IsOfficial | |
from city | |
join countrylanguage | |
on city.CountryCode = countrylanguage.CountryCode | |
where countrylanguage.Language = 'spanish' | |
and countrylanguage.IsOfficial = 't'; | |
select name as city , population | |
from city | |
where CountryCode in( | |
select CountryCode | |
from countrylanguage | |
where language = 'spanish' | |
and 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 as country, city.Name as city, city.Population | |
from country | |
join city | |
on Country.Code = city.CountryCode | |
where city.Population in ( | |
select max(Population) | |
from city | |
where city.CountryCode = Country.Code | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment