Schemat bazy danych.

Baza world jest to przykładowa baza jeżeli wybierzemy to będzie instalowana razem ze środowiskiem MySQL community.

Zapytania można wykonać i zaobserwować wyniki za pomocą aplikacji: https://wiki.ostrowski.net.pl/php_mysql/world.php

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

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

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

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

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;

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;

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

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

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;

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;

(czyli gdzie średnia liczba mieszkańców na miasto jest największa)

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;

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'
);

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

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

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;

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;

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;

To wymaga analizy języków w kontekście kontynentu i unikalności – trochę „sztuczne”, ale ciekawe:

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

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;

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