Class 23 - Week 1 Exercises #7

databases-week1-exercises.sql

-- 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.