Transcript C08-SQL-dalsi dotazy

SQL
Structured Query Language
Další dotazy
Vnořené dotazy
Některé SQL příkazy mohou mít v sobě obsaženy další kompletní
příkazy SELECT.
Využijeme je tam, kde potřebujeme nejprve zjistit nějakou informaci
a v závislosti na ní pak informace další.
Mohou se vyskytovat v klauzulích WHERE a HAVING vnějšího
příkazu SELECT - také v příkazech INSERT,UPDATE,DELETE.
Platí:
•Vnořený dotaz je vždy uzavřen mezi závorkami.
•Vnořený dotaz je ve výrazu vždy na pravé straně.
•Vnořený dotaz musí vracet vždy pouze jeden sloupec (výraz).
•V případě, že používáme srovnávací operátory, musí vnořený dotaz
vracet právě jeden řádek. Výjimkou jsou množinové operátory.
Vnořené dotazy
Příklad:
Vypište seznam všech studentů, kteří mají nejlepší studijní průměr.
Pomocí jednoduchých SQL dotazů, tento výsledek obdržíme ve dvou
krocích.
Nejprve si zjistíme, jaký je nejlepší studijní průměr (použijeme
agregační funkci MIN):
SELECT MIN(st_prumer)
FROM student;
Tento dotaz nám vrátí např. číslo 1.05. Toto číslo si zapamatujeme a
použijeme jej v konstrukci následujícího dotazu:
SELECT jmeno, prijmeni,st_prumer
FROM student
WHERE st_prumer = 1.05;
Jednoduché vnořené dotazy
Za jednoduchý vnořený dotaz budeme považovat vnořený příkaz
SELECT vracející nám jednu hodnotu.
SELECT jmeno, prijmeni,st_prumer
FROM student
WHERE st_prumer= (SELECT MIN(st_prumer) FROM
student);
Vnořeným SELECT-em se nám vyhodnotí Vypočtená_hodnota na
jednu hodnotu a máme tedy podmínku zapsanou v pořádku.
Vnořený SELECT se vyhodnotí pouze jednou, hodnotu si SQL
server "zapamatuje" a s ní pak porovnává všechny ostatní hodnoty v
tabulce.
V jednoduchých vnořených dotazech – poddotazech, budou
obdobným způsobem fungovat i další agregační funkce.
Poddotazy vracející více hodnot
Operátory - IN, ANY (SOME), ALL
Vnořování dotazů nám rozšiřuje klasickou množinu relačních
operátorů o operátory další, které se aplikují z levé strany na sloupec
a z pravé strany na vnořený dotaz vracející více hodnot.
IN - slouží pro jednoduché porovnání, zdali se hodnota sloupce
vlevo vyskytuje mezi hodnotami vrácenými vnořeným dotazem.
Kdybychom např. chtěli jména a příjmení studentů, kteří nastoupili
do školy v letech 2000 až 2003, mohli bychom takový dotaz pomocí
operátoru IN zapsat takto:
SELECT jmeno,prijmeni,datum_zapisu
FROM student
WHERE YEAR(datum_zapisu) IN (2000,2001,2002,2003);
Poddotazy vracející více hodnot
ANY (nebo SOME) - určují, že relace se vztahuje na alespoň jednu
z hodnot, kterou vrátí vnořený dotaz.
Chceme-li seznam předmětů, které vyučuje libovolný učitel
z Ústavu informatiky:
SELECT nazev_pr,id_uc
FROM predmet
WHERE id_uc = ANY (SELECT id_uc FROM ucitel
WHERE ustav ="UI")
Je to možné zapsat taky:
SELECT nazev_pr,id_uc
FROM predmet
WHERE id_uc IN(SELECT id_uc FROM ucitel
WHERE ustav ="UI")
To znamená, že je totožné použití IN a = ANY.
Poddotazy vracející více hodnot
ALL - aplikuje relaci na všechny hodnoty vrácené poddotazem.
Chceme-li seznam předmětů, které vyučuje libovolný učitel, který
není z Ústavu informatiky:
SELECT nazev,id_uc
FROM predmet
WHERE id_uc <>ALL (SELECT id_uc FROM ucitel
WHERE ustav ="UI")
nebo
SELECT nazev,id_uc
FROM predmet
WHERE id_uc NOT IN (SELECT id_uc FROM ucitel
WHERE ustav ="UI")
V tomto případě platí, že je totožné NOT IN a <> ALL.
Operátor EXISTS
Tento operátor vrací pravdivostní hodnotu TRUE nebo FALSE na
základě toho, zda vnořený SQL dotaz vrací nějakou hodnotu.
EXISTS ( vnořený SQL dotaz );
Jestli vnořený SQL dotaz vrátí alespoň jeden řádek, operátor
EXISTS nám vrátí TRUE.
Poznámka:
Vnořený SQL dotaz v argumentu operátoru EXISTS se vyhodnocuje zvlášť pro
každý řádek.
Chceme zkontrolovat, zdali u každého studenta jsou zadané předměty, na
které se zapsal. Chceme tedy seznam studentů, u kterých nemáme v
systému zadaný žádný předmět:
SELECT jmeno,prijmeni
FROM student
WHERE NOT EXISTS (SELECT id_st FROM zapis
WHERE student.id_st = zapis.id_st);
Skládání dotazů - Sjednocení dotazů
Pro sjednocení výsledků dotazů slouží klíčové slovo UNION, za
kterým následuje další příkaz SELECT.
Chceme seznam všech předmětů, které navštěvuje Anna Antalová,
nebo Bruno Babel.
a) bez použití skládání dotazů:
SELECT distinct p.nazev
FROM student s, zapis z, predmet p
WHERE s.id_st = z.id_st
AND z.id_pr = p.id_pr
AND (
(jmeno = 'Anna' AND prijmeni = 'Antalova')
OR
(jmeno = 'Bruno' AND prijmeni = 'Babel'));
Skládání dotazů - Sjednocení dotazů
b) s pomocí sjednocení :
SELECT p.nazev
FROM student s, zapis z, predmet p
WHERE s.id_st = z.id_st
AND z.id_pr = p.id_pr
AND jmeno = 'Anna' AND prijmeni = 'Antalova'
UNION
SELECT p.nazev
FROM student s, zapis z, predmet p
WHERE s.id_st = z.id_st
AND z.id_pr = p.id_pr
AND jmeno = 'Bruno' AND prijmeni = 'Babel';
Skládání dotazů - Průnik dotazů
Pro průnik se používá klíčové slovo INTERSECT.
Chceme názvy všech předmětů, které navštěvuje Anna Antalová a
zároveň Bruno Babel (a možná někdo další, ale to už nás nezajímá).
a) bez použití průniku dotazů:
SELECT distinct p.nazev
FROM student s, zapis z, predmet p
WHERE s.id_st = z.id_st
AND z.id_pr = p.id_pr
AND (
(jmeno = 'Anna' AND prijmeni = 'Antalova')
AND
(jmeno = 'Bruno' AND prijmeni = 'Babel'));
Takový dotaz je samozřejmě chybný. V žádném řádku pseudotabulky, která vznikne
spojením uvedených tří tabulek, nemůže nastat situace, že by jméno bylo Anna a
zároveň Bruno.
Skládání dotazů - Průnik dotazů
b) s použitím průniku dotazů :
SELECT p.nazev
FROM student s, zapis z, predmet p
WHERE s.id_st = z.id_st
AND z.id_pr = p.id_pr
AND jmeno = 'Anna' AND prijmeni = 'Antalova'
INTERSECT
SELECT p.nazev
FROM student s, zapis z, predmet p
WHERE s.id_st = z.id_st
AND z.id_pr = p.id_pr
AND jmeno = 'Bruno' AND prijmeni = 'Babel';
Pohledy
Z bázových tabulek odvozené virtuální tabulky.
Vytvoření pohledu
CREATE VIEW jm_pohledu [(jm_sloupce, …)] AS
tab_výraz [WITH CHECK OPTION]
- uloží definici pohledu do systémového katalogu
Sloupce musí mít jednoznačná jména.
Př) Pohled pro studenty přihlášené na Matematiku.
CREATE VIEW Matematici AS
SELECT S.* FROM Student S, Zapis Z, Predmet P
WHERE S.id_st=Z.id_st AND Z.id_pr=P.id_pr AND
P.nazev_pr=´Matematika´
WITH CHECK OPTION
WITH CHECK OPTION - kontrola, že při změně nedochází k
porušení def. pohledu.
Odstranění pohledu
DROP VIEW jm_pohledu [RESTRICT|CASCADE];
- Zruší informaci o pohledu ze systémového katalogu
Manipulace na pohledech
Př) Vypište studenty přihlášené na Matematiku,
kteří nejsou z prvého ročníku
SELECT * FROM Matematici WHERE rocnik>1;
b
SELECT S.* FROM Student S, Zapis Z, Predmet P
WHERE S.id_st=Z.id_st AND Z.id_pr=P.id_pr AND
P.nazev_pr=´Matematika´AND S.rocnik>1;
SŘBD musí být schopen jednoznačně transformovat operace
řádku pohledu na operace nad zdrojovými bázovými tabulkami
pohledu.
Pohledy s klauzulemi DISTINCT, GROUP BY, HAVING, s
agregačními funkcemi a spojující několik tabulek umožňují
jen čtení.
CREATE VIEW pocet_studentu (predmet,pocet) AS
SELECT id_pr,COUNT(*) FROM Zapis GROUP BY id_pr;
Selektivní pohled
CREATE VIEW Informatici AS
SELECT * FROM Student WHERE obor=´Informatika´;
Agregační pohled
CREATE VIEW pocet_predmetu (student,pocet) AS
SELECT id_st,COUNT(*) FROM Zapis GROUP BY id_st;
Zadání cv.8:
Z databáze STUDIJNI_AGENDA
1. Vypište abecedně seřazené studenty, dle příjmení a jména a k nim přiřazeny
názvy předmětů, které navštěvují.
2. Vypište seznam všech studentů, kteří nejsou zapsaní na žádný předmět.
3. Vypište jména a příjmení všech studentů, kteří mají nejhorší studijní průměr.
4. Vypište počet učeben v jednotlivých blocích (A,B…)
5. Vypište počet předmětů, které se vyučují na libovolné učebně v bloku A.
6. Vypište seznam všech studentů, kteří jsou zapsáni v maximálním počtu
předmětů.
7. Vypište seznam všech předmětů, do kterých se přihlásilo alespoň 5 studentů a
setřiďte je sestupně podle počtu přihlášených studentů.
8. Vypište seznam všech učitelů, pro které je počet předmětů, na které se někdo
zapsal, menší než 3.
9. Vypište přehled počtu učitelů podle jejich titulů na jednotlivých ústavech.