User:A3nm/SD202 2021 ex1

From SQLZOO
Jump to navigation Jump to search

Welcome to the first lab of the SD202 class! The goal of this lab is to learn how to query data using an SQL database.

In this first exercise, we will query a single world table. The table contains information about the world's countries. The table has the following fields:

  • name varchar(50): the English name of the country (primary key)
  • continent varchar(60): the English name of the continent
  • area decimal(10,0): the area in square kilometers
  • population decimal(11,0): the population
  • gdp decimal(14,0): the GDP (we will not use it in the exercise)
  • capital varchar(60): the English name of the capital of the country
  • tld varchar(5): the Top Level Domain associated to the country (we will not use it)
  • flag varchar(255): the URL to the flag (we will not use it)

Here is an excerpt of the first 5 rows of the table:

namecontinentareapopulationgdpcapitaltldflag
AfghanistanAsia6522302550010020364000000Kabul.af//upload.wikimedia.org/wikipedia/commons/9/9a/Flag_of_Afghanistan.svg
AlbaniaEurope28748282197712044000000Tirana.al//upload.wikimedia.org/wikipedia/commons/3/36/Flag_of_Albania.svg
AlgeriaAfrica238174138700000207021000000Algiers.dz//upload.wikimedia.org/wikipedia/commons/7/77/Flag_of_Algeria.svg
AndorraEurope468760983222000000Andorra la Vella.ad//upload.wikimedia.org/wikipedia/commons/1/19/Flag_of_Andorra.svg
AngolaAfrica124670019183590116308000000Luanda.ao//upload.wikimedia.org/wikipedia/commons/9/9d/Flag_of_Angola.svg

Warning: contrary to what the SQL standard prescribes, table names are case-sensitive in the SQL engine used in this website. So the table must be called world, not World, otherwise the evaluation will fail (with the error: Table 'gisq.World' doesn't exist).

Using the world table, your task will be to write queries satisfying some requirements. Sometimes, an example query is provided for you to complete. When submitting a query, you will get its result, and you can compare it to the expected result if the two do not match. Warning: the order of rows (and that of columns) is taken into account when checking an answer.

Basics: Countries and continents

Modify the query below to select the name of all countries in Europe. Hint: use the WHERE clause: 

SELECT name FROM world

SELECT name FROM world WHERE continent='Europe'

Select all DISTINCT continents in the database: 

SELECT continent FROM world

SELECT DISTINCT continent FROM world

Filtering names

Select all country names that start with an 'F'. Hint: use LIKE. Note: in the SQL implementation used in this website, the LIKE operator is case-insensitive. 

SELECT name FROM world

SELECT name FROM world WHERE name LIKE 'F%'

Select all country names containing the letter 'z': 

SELECT name FROM world

SELECT name FROM world WHERE name LIKE '%z%'

Population

Select all country names having at most one million inhabitants: 

SELECT name FROM world

SELECT name FROM world WHERE population <= 1000000

Write a query returning the name and population of all European countries: 

SELECT * FROM world

SELECT name, population FROM world WHERE continent = 'Europe'

Write a query returning the country name and population of France, Germany, and Italy (in that order): 

SELECT * FROM world

SELECT name, population FROM world WHERE name IN ('France', 'Germany', 'Italy')
ORDER BY name

Write a query returning the population of France: 

SELECT * FROM world

SELECT population FROM world WHERE name = 'France'

Boolean conditions

Write a query selecting the name, population, and area of all countries that have at least 100 million inhabitants or have an area of at least 3 million square kilometers (or both). Order the results by name. 

SELECT name, population, area FROM world

SELECT name, population, area FROM world WHERE population >= 100000000 OR area >= 3000000
ORDER BY name

Write a query selecting the name, population, and area of all European countries which have population greater than 50 million or area greater than 500000 square kilometers (or both), ordered by name. Hint: be careful! 

SELECT name, population, area FROM world

SELECT name, population, area FROM world
WHERE continent = 'Europe' AND (population >= 50000000 OR area >= 500000)
ORDER BY name

Write a query selecting the name, population, and area of all countries that are either large in population or large in size, but not both. Specifically, select countries that have at least 100 million inhabitants OR have an area of at least 3 million square kilometers, but not both. Order the results by name 

SELECT name, population, area FROM world

SELECT name, population, area FROM world WHERE (population >= 100000000 AND NOT area >= 3000000) OR (area >= 3000000 AND NOT population >= 100000000)
ORDER BY name

String operations

Write a query selecting the country names that are the same as that country's capital, sorted by name. 

SELECT name FROM world

SELECT name FROM world WHERE capital = name
ORDER BY name

Write a query selecting the country names and capital names where both names have the same length. E.g., "Greece" and "Athens" both have length 6. Exclude the cases from the previous question, i.e., those where the country name is the same as the capital, and sort the results by country name. Hint: search the Internet for a function to compute the length of a string. 

SELECT name, capital FROM world

SELECT name, capital FROM world WHERE LENGTH(capital) = LENGTH(name) AND capital <> name
ORDER BY name

Ordering

Write a query returning the country name with the least population, along with its population. 

SELECT name, population FROM world

SELECT name, population FROM world ORDER BY population LIMIT 1

Write a query returning the name and population of the 5 countries with the greatest population (ordered by descending population, i.e., from greatest to least population). 

SELECT name, population FROM world

SELECT name, population FROM world ORDER BY population DESC LIMIT 5

Write a query returning the name, population, and area of the 42nd country in alphabetical order. 

SELECT name, population, area FROM world ORDER BY name

SELECT name, population, area FROM world ORDER BY name LIMIT 1 OFFSET 41

Write a query returning the name and population of the 10 most populous countries among the 20 countries with the greatest area. Hint: use a subquery, and remember to give an alias to its result! 

SELECT name, population FROM world

SELECT * FROM (SELECT name, population FROM world ORDER BY area DESC LIMIT 20) AS T ORDER BY population DESC LIMIT 10;

Computation and aggregation

Write a query returning the name and population density of the Asian countries. The population density is the population divided by the area. Order the results by name. 

SELECT name FROM world

SELECT name, population/area AS density FROM world WHERE continent = 'Asia'
ORDER BY name

Write a query returning the name of Asian countries with a new column dense containing "yes" for countries with density at least 100 inhabitants per square kilometer, and "no" otherwise. Sort the results to have first the dense countries, then the non-dense countries, and then order the countries in each group by name. Hint: use UNION. 

SELECT name FROM world

SELECT name, 'yes' AS t FROM world WHERE continent = 'Asia' AND population/area >= 100
UNION SELECT name, 'no' AS t FROM world WHERE continent = 'Asia' AND population/area < 100
ORDER BY t DESC, name

Write a query returning the number of countries in the database. Hint: use COUNT(*). 

SELECT * FROM world

SELECT COUNT(*) FROM world;

Write a query returning the total world population. Hint: use SUM. 

SELECT * FROM world

SELECT SUM(population) FROM world;

Write a query returning the continent names and, for each continent, the number of countries in that continent. Hint: use aggregation. In this question and the next ones, unless otherwise stated, order the results by continent. 

SELECT continent FROM world

SELECT continent, COUNT(*) FROM world GROUP BY continent
ORDER BY continent

Write a query returning the continent names and, for each continent, the total population and total area of the countries in that continent. 

SELECT continent FROM world

SELECT continent, SUM(population), SUM(area) FROM world GROUP BY continent
ORDER BY continent

Show the name and total population of continents having total population at least 100 million. 

SELECT continent FROM world

SELECT continent, SUM(population) AS total FROM world GROUP BY continent HAVING total >= 100000000
ORDER BY continent

Show the name of continents and the number of countries in that continent that have population at least 1 million. 

SELECT continent FROM world

SELECT continent, COUNT(*) FROM world WHERE population >= 1000000 GROUP BY continent
ORDER BY continent

Compute the average population of the world's countries. Unfortunately, the AVERAGE command is unavailable, but find a way to do it nevertheless. ;) 

SELECT COUNT(*) FROM world

SELECT SUM(population)/COUNT(*) AS average FROM world

Advanced queries

Compute, for every continent name, its population density (i.e., the continent's population divided by the continent's area), and the average of the population densities of its countries. Do you understand why the two values are different? 

SELECT continent FROM world

SELECT continent, SUM(population)/SUM(area) AS total,
SUM(population/area)/COUNT(*) AS average FROM world 
GROUP BY continent

Return a table with a column alpha containing the first letter of country names (ordered alphabetically) and a column total with the total population of countries whose name starts with that letter. Hint: use SUBSTR as in the example. 

SELECT SUBSTR(name, 1, 1) AS alpha FROM world

SELECT SUBSTR(name, 1, 1) AS alpha, SUM(population) FROM world GROUP BY alpha
ORDER BY alpha

Compute, for every continent name, its country having the largest area, and its country having the greatest population. Order the result by continent. Hint: this is a complicated task, use subqueries and/or join the world table with itself. Note: It is normal that Kazakhstan appears as a country in Europe. 

SELECT continent FROM world

SELECT W1.continent, W1.name AS largest, W2.name AS most_populous FROM world AS W1, world AS W2
WHERE W1.continent = W2.continent
AND NOT EXISTS
  (SELECT 1 FROM world AS W WHERE W.continent = W1.continent AND W.area > W1.area)
AND NOT EXISTS
  (SELECT 1 FROM world AS W WHERE W.continent = W2.continent AND W.population > W2.population)
ORDER BY W1.continent


Compute, for every continent name, a count of how many countries in the continent are strictly more populous that the continent's largest country (by area). Order the results by continent, and do not omit results where the count is zero. 

SELECT continent FROM world

SELECT continent, SUM(total) FROM
(SELECT continent, COUNT(*) AS total FROM world AS W1
WHERE population > (SELECT population FROM world AS W2 WHERE W1.continent =
W2.continent ORDER BY area DESC LIMIT 1) GROUP BY continent
UNION
SELECT DISTINCT continent, 0 AS TOTAL FROM world) AS T GROUP BY continent
ORDER BY continent


Acknowledgements

This exercise is inspired by the exercises SELECT_from_WORLD_Tutorial, SELECT_within_SELECT_Tutorial, and SUM_and_COUNT. If you reach the end of this exercise and still have time, you can complete the questions in these three tutorials that are not the same as the ones covered here.

Thanks to Andrew Cumming for his great work in maintaining SQLZoo!

Retrieved from "https://sqlzoo.net/w/index.php?title=User:A3nm/SD202_2021_ex1&oldid=39854"