week1 exercise done

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 CountryCode
FROM city
WHERE CountryName = 'Netherlands';

-- 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 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 country.Name
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
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 CountryName, IndependenceDate
FROM Countries
WHERE GovernmentType LIKE '%republic%'
ORDER BY IndependenceDate 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--
SELECT country.Name AS CountryName, countrylanguage.Language, 
       ROUND((country.Population * (countrylanguage.Percentage / 100))) AS NumberOfSpeakers
FROM country
JOIN countrylanguage ON country.Code = countrylanguage.CountryCode;

### Bonus

-- 1. What is the total population of the world?--
SELECT SUM(Population) AS TotalWorldPopulation
FROM country;
--2. What is the average population of countries in Europe?--
SELECT AVG(Population) AS AveragePopulationEurope
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 AS 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 CityName, country.Name AS CountryName
FROM city
JOIN country ON city.CountryCode = country.Code;

-- 2. Find the average life expectancy of countries in the continent 'Europe'.
SELECT AVG(LifeExpectancy) AS AverageLifeExpectancy
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 country.Name AS CountryName, city.Name AS CapitalCity
FROM country
JOIN city ON country.Capital = city.ID;

-- 5. Find the largest city by population in each country.
SELECT city.CountryCode, city.Name AS CityName, city.Population
FROM city
JOIN (
    SELECT CountryCode, MAX(Population) AS MaxPopulation
    FROM city
    GROUP BY CountryCode
) AS max_cities
ON city.CountryCode = max_cities.CountryCode 
AND city.Population = max_cities.MaxPopulation;

-- 6. List the names of all cities with a population of over 1 million in the continent of 'Asia'.
SELECT city.Name
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 CountryName, 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
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 TotalLanguages
FROM country
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 AS CityName, city.Population
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 CapitalCity, city.Population
FROM city
JOIN country ON city.ID = country.Capital
WHERE city.Population > 500000;

-- 12. List the names and continents of countries where English is an official language.
SELECT country.Name AS CountryName, 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;

-- 14. Get the names of countries in the 'South America' continent with cities having a population over 1 million.
SELECT DISTINCT country.Name AS CountryName
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 Name AS CityName, Population
FROM city
WHERE CountryCode = (SELECT Code FROM country WHERE Name = 'India')
ORDER BY 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 AS CountryName, city.Name AS CapitalCity
FROM country
JOIN city ON country.Capital = city.ID
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.ID IS NULL;

-- 18. Get the name and population of the largest city in the continent 'South America'.
SELECT city.Name AS CityName, city.Population
FROM city
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 AS CityName, city.Population
FROM city
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.Name AS CityName, city.Population
FROM country 
LEFT JOIN city ON country.Code = city.CountryCode 
ORDER BY city.Population DESC 
LIMIT 1;

Question 5: There is no CountryName column in the city table; it should use CountryCode.
Question 14: The query returns countries that have a gouvernment form that is exactly "republic" where we asked for some form of republic.
Question 5 (Bonus): The query does not specify the max_cities subquery or table. We can use a subquery instead.
Question 7 (Bonus): The query should check countrylanguage.CountryCode IS NULL to identify countries without any official language records.
Question 15 (Bonus): Your answer is the same as for question 14 (Bonus)?

Thanks for review. I made corrections.