User:A3nm/test

From SQLZOO
< User:A3nm
Revision as of 15:07, 22 September 2021 by A3nm (talk | contribs) (fix)
Jump to navigation Jump to search

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

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

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.

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:

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

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

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

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.

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)

String operations

Write a query selecting the country names that are the same as that country's capital.

SELECT name FROM world
SELECT name FROM world WHERE capital = 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. 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;

Ordering

Write a query returning the country name with the least population and 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, sorted by increasing population, of the 20 countries with the greatest area. Hint: use a subquery.

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

Computation and aggregation

Write a query returning the name and population density (computed as a column density) of the European countries. The population density is the population divided by the area.

SELECT name FROM world
SELECT name, population/area AS density WHERE continent = 'Europe';

Write a query returning the name of European countries with a new column dense containing "yes" for countries with density at least 100 inhabitants per square kilometer, and "no" otherwise. Hint: use UNION.

SELECT name FROM world
SELECT name, 'yes' FROM world WHERE population/area >= 100 UNION SELECT name, 'no' FROM world WHERE population/area < 100;

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.

SELECT continent FROM world
SELECT continent, COUNT(*) FROM world GROUP 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;

Show the name and total population (in a column total) 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;

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

Return a table with a column alpha containing the first letter of country names 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

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

SELECT continent FROM world
SELECT continent, SUM(population)/SUM(area) AS total, AVERAGE(population/area) AS average FROM world

Compute, for every continent name, its country having the largest area (in a column largest) and its country having the greatest population (in a column most_populous). Hint: this is a complicated task, use subqueries and/or join the world table with itself.

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


Compute, for every continent name, a column total indicating how many countries in the continent are strictly more populous that the continent's largest country by area.

SELECT continent FROM world
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)