Databases Week 1 Exercises

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 100000;

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 city.Name 
FROM city JOIN country ON city.CountryCode = CountryCode 
WHERE country.Name = '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 LocalName = Name ;

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 
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 Name, IndependenceYear 
FROM country 
WHERE GovernmentForm LIKE '%Repbulic%'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 country.Name, countrylanguage.Language, (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 Totalpopulation 
FROM country;

2. What is the average population of countries in Europe?
SELECT AVG(population) AS Averagepopulation 
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'GROUP BY CountryCode ;

More Exercises

-- 1. Retrieve the names of all the cities along with their respective country names, make sure the column names are easy to understand.
SELECT cities_name AS 'City Name', countries_Name AS 'Country Name'
FROM city 
JOIN counries ON cities.country_id = countries.id;

-- 2. Find the average life expectancy of countries in the continent 'Europe'.
SELECT AVG(life_expectancy) AS Average Life Expectancy
FROM country 
WHERE Continent = 'Europe';

-- 3. Get the names and populations of cities in the district 'California'.
SELECT name AS city_name, population AS city_population
FROM cities
WHERE district = 'California';

-- 4. Retrieve the capital city of each country along with the country name.
SELECT city_name AS 'Capital City', country_name AS 'Country Name'
FROM city 
WHERE 
-- 5. Find the largest city by population in each country.
-- Tip: It is possible to use nested queries, for example, if you wanted to get all the countries which population
-- is greater than Spain's you would do:
-- SELECT Name FROM country WHERE Population > (SELECT Population FROM country WHERE Name = 'Spain')
SELECT country_name, MAX(city by Population) AS 'Largest City POpulation'
FROM countries


-- 6. List the names of all cities with a population of over 1 million in the continent of 'Asia'.
SELECT  name, id
FROM city AND country
WHERE population > 1000000 AND continet = 'Asia'


-- 7. Get the names and continents of countries that do not have an official language recorded in the database.
SELECT name, continent
FROM countries
WHERE official language IS NULL OR official language = '';


-- 8. List the countries in the 'Oceania' continent with an average life expectancy over 70.
SELECT  name, AVG(life expectancy ) 
FROM countries
WHERE continent = 'Oceania'

-- 9. Find the total number of languages spoken in the continent 'Africa'.
SELECT COUNT(DISTINCT language) 
FROM language 
WHERE continent = 'Africa';

-- 10. Retrieve the names and populations of cities located in countries with a surface area greater than 1 million square kilometers.
SELECT ...

-- 11. Retrieve the names and populations of capital cities with populations over 500,000.
SELECT ...

-- 12. List the names and continents of countries where English is an official language.
SELECT ...

-- 13. Find the name of the country with the highest life expectancy.
SELECT ...

-- 14. Get the names of countries in the 'South America' continent with cities having a population over 1 million.
SELECT ...

-- 15. Find the name and population of the smallest city (by population) in the country 'India'.
SELECT ...

-- 16. Retrieve the country name and its corresponding capital city's name where the capital's population is more than 1 million.
SELECT ...

-- 17. List the names of countries that have no cities in the database.
SELECT ...

-- 18. Get the name and population of the largest city in the continent 'South America'.
SELECT ...

-- 19. List the names and populations of all cities in countries where the official language is 'Spanish'.
SELECT ...

-- 20. Get the name of the country and the population of the city with the highest population in that country.
SELECT ...