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;