~~NOTOC~~
====== MySQL: Przykłady zapytań na bazie World ======

{{:notatki:pasted:20250513-154333.png}}\\
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

===== Wyświetlenie 10 największych krajów według populacji =====

<code sql>
SELECT Name, Population
FROM Country
ORDER BY Population DESC
LIMIT 10;
</code>

===== Średnia populacja miast w Niemczech =====

<code sql>
SELECT AVG(Population) AS AvgCityPopulation
FROM City
WHERE CountryCode = 'DEU';
</code>
===== Lista języków urzędowych (IsOfficial = 'T') w krajach azjatyckich =====

<code sql>
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';
</code>
===== Suma populacji krajów z podziałem na kontynenty =====

<code sql>
SELECT Continent, SUM(Population) AS TotalPopulation
FROM Country
GROUP BY Continent
ORDER BY TotalPopulation DESC;
</code>
===== Miasta o populacji większej niż 1 milion w Ameryce Południowej =====
<code sql>
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;
</code>
===== Kraj z największą liczbą miast =====

<code sql>
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;
</code>
===== Kraje, gdzie język hiszpański nie jest językiem urzędowym, ale jest używany =====

<code sql>
SELECT c.Name, cl.Language
FROM CountryLanguage cl
JOIN Country c ON cl.CountryCode = c.Code
WHERE cl.Language = 'Spanish' AND cl.IsOfficial = 'F';
</code>
===== Kraje z populacją większą niż średnia populacja wszystkich krajów =====

<code sql>
SELECT Name, Population
FROM Country
WHERE Population > (SELECT AVG(Population) FROM Country);
</code>
===== Użycie CASE: klasyfikacja krajów według wielkości populacji =====

<code sql>
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;
</code>
===== CTE (Common Table Expression) – kraje z językiem angielskim, posortowane wg populacji =====

<code sql>
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;
</code>

===== Kraj z największym średnim zaludnieniem miast =====
(czyli gdzie średnia liczba mieszkańców na miasto jest największa)
<code sql>
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;
</code>
===== Miasta w krajach, których populacja jest powyżej średniej, i w których oficjalnym językiem jest angielski =====

<code sql>
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'
);
</code>
===== Lista krajów, które mają mniej miast niż średnia liczba miast na kraj, ale powyżej średniej populacji =====

<code sql>
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
);
</code>
===== Wszystkie oficjalne języki krajów, których stolica ma populację powyżej 1 miliona =====

<code sql>
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
);
</code>
===== Kraje z największą różnicą między populacją kraju a jego największym miastem =====

<code sql>
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;
</code>
===== Dla każdego kontynentu: kraj o największej średniej populacji miast =====

<code sql>
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;
</code>
===== Kraje, w których oficjalnie mówi się więcej niż jednym językiem =====
<code sql>
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;
</code>
===== Kraje, w których mówi się językiem, którego nie zna żaden sąsiad z tego samego kontynentu =====

To wymaga analizy języków w kontekście kontynentu i unikalności – trochę "sztuczne", ale ciekawe:
<code sql>
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';
</code>
===== Średnia populacja miast dla każdego języka urzędowego w krajach, gdzie ten język jest używany =====
<code sql>
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;
</code>
===== Lista krajów, które nie mają żadnych miast w bazie danych =====
<code sql>
SELECT co.Name
FROM Country co
LEFT JOIN City ci ON co.Code = ci.CountryCode
WHERE ci.ID IS NULL;
</code>