MySQL: Examples of queries on the World database

Database schema.

The world database is a sample database if you choose it will be installed together with the MySQL community.

You can execute queries and observe the results using the application: https://wiki.ostrowski.net.pl/php_mysql/world.php

Display the top 10 countries by population

SELECT Name, Population
FROM Country
ORDER BY Population DESC
LIMIT 10;

Average population of cities in Germany

SELECT AVG(Population) AS AvgCityPopulation
FROM City
WHERE CountryCode = 'DEU';

List of official languages (IsOfficial = 'T') in Asian countries

SELECT cl.CountryCode, c.Name AS CountryName, cl.Language
FROM CountryLanguage cl
JOIN Country c ON cl.CountryCode = c.Code
WHERE cl.IsOfficial = 'T' AND c.Continent = 'Asia';

Sum of country populations by continent

SELECT Continent, SUM(Population) AS TotalPopulation
FROM Country
GROUP BY Continent
ORDER BY TotalPopulation DESC;

Cities with a population greater than 1 million in South America

SELECT ci.Name AS City, ci.Population, co.Name AS Country
FROM City ci
JOIN Country co ON ci.CountryCode = co.Code
WHERE ci.Population > 1000000 AND co.Continent = 'South America'
ORDER BY ci.Population DESC;

Country with the most cities

SELECT co.Name, COUNT(ci.ID) AS CityCount
FROM Country co
JOIN City ci ON co.Code = ci.CountryCode
GROUP BY co.Name
ORDER BY CityCount DESC
LIMIT 1;

Countries where Spanish is not an official language but is spoken

SELECT c.Name, cl.Language
FROM CountryLanguage cl
JOIN Country c ON cl.CountryCode = c.Code
WHERE cl.Language = 'Spanish' AND cl.IsOfficial = 'F';

Countries with a population larger than the average population of all countries

SELECT Name, Population
FROM Country
WHERE Population > (SELECT AVG(Population) FROM Country);

Using CASE: classifying countries by population size

SELECT Name,
       Population,
       CASE 
           WHEN Population > 100000000 THEN 'Very Large'
           WHEN Population > 50000000 THEN 'Large'
           WHEN Population > 10000000 THEN 'Medium'
           ELSE 'Small'
       END AS PopulationCategory
FROM Country;

CTE (Common Table Expression) - countries with English language, sorted by population

WITH EnglishSpeakingCountries AS (
    SELECT CountryCode
    FROM CountryLanguage
    WHERE LANGUAGE = 'English' AND IsOfficial = 'T'
)
SELECT c.Name, c.Population
FROM Country c
JOIN EnglishSpeakingCountries esc ON c.Code = esc.CountryCode
ORDER BY c.Population DESC;

Country with the highest average urban population

(i.e. where the average number of inhabitants per city is the highest)

SELECT co.Name, AVG(ci.Population) AS AvgCityPopulation
FROM Country co
JOIN City ci ON co.Code = ci.CountryCode
GROUP BY co.Code
HAVING COUNT(ci.ID) > 1
ORDER BY AvgCityPopulation DESC
LIMIT 1;

Cities in countries with above average populations and where English is the official language

SELECT ci.Name AS City, co.Name AS Country, ci.Population
FROM City ci
JOIN Country co ON ci.CountryCode = co.Code
WHERE co.Population > (
    SELECT AVG(Population) FROM Country
)
AND co.Code IN (
    SELECT CountryCode FROM CountryLanguage
    WHERE LANGUAGE = 'English' AND IsOfficial = 'T'
);

List of countries that have fewer cities than the average number of cities per country, but above average population

SELECT co.Name, COUNT(ci.ID) AS CityCount, co.Population
FROM Country co
LEFT JOIN City ci ON co.Code = ci.CountryCode
GROUP BY co.Code
HAVING COUNT(ci.ID) < (
    SELECT AVG(CityCount)
    FROM (
        SELECT CountryCode, COUNT(*) AS CityCount
        FROM City
        GROUP BY CountryCode
    ) AS SubCityCounts
)
AND co.Population > (
    SELECT AVG(Population) FROM Country
);

All official languages of countries whose capital has a population of more than 1 million

SELECT DISTINCT cl.Language, co.Name AS Country
FROM Country co
JOIN CountryLanguage cl ON co.Code = cl.CountryCode
WHERE cl.IsOfficial = 'T'
AND co.Capital IN (
    SELECT ID FROM City WHERE Population > 1000000
);

Countries with the largest difference between the population of a country and its largest city

SELECT co.Name, co.Population - MAX(ci.Population) AS PopulationDiff
FROM Country co
JOIN City ci ON co.Code = ci.CountryCode
GROUP BY co.Code
ORDER BY PopulationDiff DESC
LIMIT 10;

For each continent: the country with the largest average urban population

SELECT Continent, Name, AvgCityPop
FROM (
    SELECT co.Continent, co.Name, AVG(ci.Population) AS AvgCityPop,
           RANK() OVER (PARTITION BY co.Continent ORDER BY AVG(ci.Population) DESC) AS rnk
    FROM Country co
    JOIN City ci ON co.Code = ci.CountryCode
    GROUP BY co.Code
) AS ranked
WHERE rnk = 1;

Countries where more than one language is officially spoken

SELECT co.Name, COUNT(*) AS OfficialLanguages
FROM Country co
JOIN CountryLanguage cl ON co.Code = cl.CountryCode
WHERE cl.IsOfficial = 'T'
GROUP BY co.Code
HAVING COUNT(*) > 1;

Countries where a language is spoken that is not spoken by any of their neighbours on the same continent

This requires an analysis of languages in the context of continent and uniqueness - a bit 'artificial', but interesting:

SELECT DISTINCT c1.Name, cl1.Language
FROM Country c1
JOIN CountryLanguage cl1 ON c1.Code = cl1.CountryCode
WHERE cl1.Language NOT IN (
    SELECT cl2.Language
    FROM Country c2
    JOIN CountryLanguage cl2 ON c2.Code = cl2.CountryCode
    WHERE c2.Continent = c1.Continent
      AND c2.Code != c1.Code
)
AND cl1.IsOfficial = 'T';

Average population of cities for each official language in countries where that language is spoken

SELECT cl.Language, AVG(ci.Population) AS AvgCityPopulation
FROM CountryLanguage cl
JOIN Country co ON cl.CountryCode = co.Code
JOIN City ci ON co.Code = ci.CountryCode
WHERE cl.IsOfficial = 'T'
GROUP BY cl.Language
ORDER BY AvgCityPopulation DESC;

List of countries that do not have any cities in the database

SELECT co.Name
FROM Country co
LEFT JOIN City ci ON co.Code = ci.CountryCode
WHERE ci.ID IS NULL;