Zdrojů je samozřejmě celá řada. Vybral jsem cvičnou databázi VŠE Krokodata. Je jednoduchá a obsahuje celou řadu zadání k dotazům ideálních k výuce.
Alternativně zde:
stackoverflow a další zdroj obsahuje i ukázková data http://www.pgsql.cz/skoleni/skoleni_sql.zip
V angličtině je zdrojů mnohem více. w3resource.com nabízí interaktivní box ke zkoušení dotazů. Fungují tam jen základní dotazy.
Zde je jednoduchá databáze obsahující data k objednávce. Dump do DB je v Oraclu.
Automaticky vygenerovaná dokumentace k databázi je k nahlédnutí na cvicna databáze.
Dump do databáze pro Postgre je zde. Jen je třeba pro novější verze nahradit sloupcový typ CLOB jiným, například BYTEA.
Základní příklady jsou zde.
http://krokodata.vse.cz/SQL/Ulohy
Výhodou je, že se jedna o cvičnou DB pro studenty. Takže testových dotazů vzniklo velmi mnoho. Jsou však pro Oracle…..většina by měla fungovat i v PostgreSQL.
Zadání příkazů pro příklad SELECT (4it218)
https://uloz.to/hledej?orderby=latest&q=4it218
Poznámky k základním úlohám krokodata pro postgresql (pg)
Výsledky jsou stejné, ale někdy liší se pořadí např. př. 10.
10př Pro každou kategorii vypište názvy všech zboží v této kategorii, spolu s kódem a názvem této kategorie. Ať se vypíší opravdu všechny kategorie!
select kat,nazk,kod,naz
from kat left join zboz using (kat)
order by 1,3;
14př Pro každou kategorii spočítejte počet kategorií jí podřízených.
Je myšleno jen přímo podřízených. Žádné vnořování.
15př Vypište kódy a názvy kategorií, které nemají žádnou podkategorii.
Se dá využít dotaz ze 14. Jen jeden sloupec smáznout, ale je pak komplikovanější. Někdy lepší rychle klikat, než to dělat nejlíp 😊
select kat, nazk
from kat
where kat not in (
select nad.KAT as POCET_PODRIZ
from KAT NAD join KAT POD ON (NAD.KAT=POD.NADR)
group by NAD.KAT
Rekurzivní dotazy
Teorie je v dokumentaci. Tutorial je na postgresqltutorial. Ukážu na tabulce kategorií (kat).
Kategorie nápoje (nap) má pod sebou 2 kategorie napAl a napNa. Ty mají pod sebou ještě další. Jak bude vypadat výpis všech prvků podřízených kategorii nápojů?
WITH RECURSIVE pod AS (
SELECT kat, nadr, nazk
FROM kat
WHERE kat = ‘nap’
UNION SELECT
k.kat, k.nadr, k.nazk
FROM kat k
INNER JOIN pod ON pod.kat = k.nadr
) SELECT *
FROM pod;
Pokud dáme do WHERE ‘pec’. Tak dotaz vrátí jeden výsledek.
Pokud chceme zjisti rekurzivním dotazem úroveň jednotlivých kategorií, použijeme inner join (INNER JOIN pod ON k.nadr = pod.kat) v rekurzivní části dotazu na tabulku vznikajicí rekurzí (WITH RECURSIVE pod…).
WITH RECURSIVE pod (kat, nazk, nadr, urov) AS (
SELECT kat, nazk, nadr, 0 urov
FROM kat
UNION ALL
SELECT k.kat, k.nazk, k.nadr, urov + 1
FROM kat k
INNER JOIN pod ON k.nadr = pod.kat
) SELECT *
FROM pod;
SELECT do SELECTu nám zajistí jedinočnost a maximální dosaženou úroveň.
SELECT unique_pod.kat, unique_pod.nazk, unique_pod.nadr, max(unique_pod.urov) as max_urov FROM
(WITH RECURSIVE pod (kat, nazk, nadr, urov) AS (
SELECT kat, nazk, nadr, 0 urov
FROM kat
UNION ALL
SELECT k.kat, k.nazk, k.nadr, urov + 1
FROM kat k
INNER JOIN pod ON k.nadr = pod.kat
) SELECT *
FROM pod)
AS unique_pod
GROUP BY unique_pod.kat, unique_pod.nazk, unique_pod.nadr
ORDER BY max_urov;
