3. CVIČENÍ

Hierarchické dotazy :

Nyní navážeme na poslední dotaz z předchozího cvičení: 


SELECT  a.prijmeni, a.jmeno, b.prijmeni, b.jmeno
  FROM  osoby a, osoby b
 WHERE  a.id_nad = b.id_osoby;
Výše uvedený dotaz vlastně zobrazí vždy dvě úrovně z celé hierarchie osob. Tímto způsobem lze zobrazit i např. první tři horní úrovně hierarchie "trojnásobným spojením", kořen stromu (nejvýše postavená osoba) má ID_NAD rovno NULL. Tatkto lze ale provádět dotazy vždy jen s pevným počtem úrovní v hierarchii osob (kolikrát je proveden self-join). V závislosti na datech se však počet úrovní může dynamicky měnit. RDBMS Oracle umožňuje procházet stromovou hierarchii do hloubky pomocí SQL klauzule CONNECT BY PRIOR. Pseudo-sloupec LEVEL udává v jaké úrovni se právě nacházíme. Následující dotaz projde pro každou osobu i všechny její podřízené. Vazba rodič - potomek je definována podmínkou v klauzuli CONNECT BY PRIOR, kde PRIOR označuje stranu rodiče. Pozor, tuto konstrukci podporuje POUZE Oracle. 
 SELECT  LEVEL, jmeno, prijmeni
   FROM  osoby
CONNECT  BY PRIOR id_osoby = id_nad;
Způsob procházení hierarchie ukazuje spodní obrázek:

Seznam osob s jejich nařízenými: 

 SELECT  LEVEL, jmeno, prijmeni
   FROM  osoby
CONNECT  BY id_osoby = PRIOR id_nad;
Výše uvedené dotazy prochází strukturu rekurzivně pro každý záznam. Můžeme však specifikovat, odkud se má začít hierarchická struktura prohledávat: 
 SELECT  LEVEL, jmeno, prijmeni
   FROM  osoby
CONNECT  BY PRIOR id_osoby = id_nad
  START  WITH id_nad IS NULL;
Takto přehledně zobrazíme celou hierarchii: 
 SELECT  LPAD(' ', (LEVEL-1)*8)||jmeno||' '||prijmeni AS osoba
   FROM  osoby
CONNECT  BY PRIOR id_osoby = id_nad
  START  WITH id_nad IS NULL;
Zobrazení hierarchie od určité osoby: 
 SELECT  LPAD(' ', (LEVEL-1)*8)||jmeno||' '||prijmeni AS osoba
   FROM  osoby
CONNECT  BY PRIOR id_osoby = id_nad
  START  WITH jmeno = 'Karel' AND prijmeni = 'Vavricka';
Zobrazení cesty od libovolného zaměstance až ke generálnímu řediteli: 
 SELECT  LPAD(' ', (LEVEL-1)*8)||jmeno||' '||prijmeni AS osoba
   FROM  osoby
CONNECT  BY id_osoby = PRIOR id_nad
  START  WITH id_nad IS NOT NULL;
Zobrazení cesty od konkrétního zaměstance až ke generálnímu řediteli: 
 SELECT  LPAD(' ', (LEVEL-1)*8)||jmeno||' '||prijmeni AS osoba
   FROM  osoby
CONNECT  BY id_osoby = PRIOR id_nad
  START  WITH jmeno = 'Maxmilian' AND prijmeni = 'Rohlik';
Zobrazení úrovně, na které se v hierarchii nachází daný zaměstnanec: 
 SELECT  MAX(LEVEL)
   FROM  osoby
CONNECT  BY PRIOR id_osoby = id_nad
  START  WITH prijmeni = 'Vavricka';
Počet podřízených (i nepřímých) daného zaměstnance: 
 SELECT  COUNT(*) - 1 AS pocet
   FROM  osoby
CONNECT  BY  PRIOR id_osoby = id_nad
  START  WITH prijmeni = 'Dudacek';
Počet podřízených (i nepřímých) všech zaměstnanců: 
  SELECT  jmeno, prijmeni, COUNT(*) - 1 AS pocet
    FROM  osoby
 CONNECT  BY id_osoby = PRIOR id_nad
   GROUP  BY jmeno, prijmeni
   ORDER  BY 3 DESC;

Sjednocení a průnik dotazů : 

SELECT  p1.zkratka, r1.semestr, r1.den, r1.h_od, r1.h_do, m1.zkratka
  FROM  predmety p1, rozvrh r1, mistnosti m1
 WHERE  p1.id = r1.id_predm
   AND  r1.id_mistn = m1.id
UNION
SELECT  p2.zkratka, '*', '*', -1, -1, '*'
  FROM  predmety p2
 WHERE  NOT EXISTS ( SELECT  *
                       FROM  rozvrh r2
                      WHERE  p2.id = r2.id_predm );
Pozn.: dotazy musí vracet stejný počet sloupců a typy jednotlivých sloupců si musí odpovídat.

Sjednocení odstraňuje duplicitní záznamy : 

SELECT  p1.zkratka, r1.semestr
  FROM  predmety p1, rozvrh r1, mistnosti m1
 WHERE  p1.id = r1.id_predm
   AND  r1.id_mistn = m1.id
UNION
SELECT  p2.zkratka, '*'
  FROM  predmety p2
 WHERE  NOT EXISTS ( SELECT  *
                       FROM  rozvrh r2
                      WHERE  p2.id = r2.id_predm );
Nepřejeme-li si duplicitní záznamy odstranit, musíme použít pro sjednocení klauzuli UNION ALL : 
SELECT  p1.zkratka, r1.semestr
  FROM  predmety p1, rozvrh r1, mistnosti m1
 WHERE  p1.id = r1.id_predm
   AND  r1.id_mistn = m1.id
UNION ALL
SELECT  p2.zkratka, '*'
  FROM  predmety p2
 WHERE  NOT EXISTS ( SELECT  *
                       FROM  rozvrh r2
                      WHERE  p2.id = r2.id_predm );

Průnik dotazů realizujeme pomocí klíčového slova INTERSECT : 

SELECT  p1.zkratka, r1.semestr, r1.den
  FROM  predmety p1, rozvrh r1, mistnosti m1
 WHERE  p1.id = r1.id_predm
   AND  r1.id_mistn = m1.id
   AND  r1.semestr = 'LS'
INTERSECT
SELECT  p1.zkratka, r1.semestr, r1.den
  FROM  predmety p1, rozvrh r1, mistnosti m1
 WHERE  p1.id = r1.id_predm
   AND  r1.id_mistn = m1.id
   AND  r1.den = 'PO';

Příklady

Následující obrázek představuje E-R model databáze pro řízení přístupů do objektů na základě identifikačních karet. Každá osoba může mít svoji identifikační kartu. V této databázi je možné dané osobě povolit přístup (individuální) do objektu v určité časové zóně. Osoby lze združovat do skupin a dané skupině lze povolit přístup (skupinový) do objektu ve zvolené časové zóně. Jednotlivé entity jsou v databázi reprezontovány tabulkami s odpovídajícími jmény, u vazeb je uvedeno jméno atributu (sloupce), kterým je vazba realizována.

Zjistěte strukturu jednotlivých tabulek v databázi a na základě této informace a znalosti E-R modelu napište dotazy v SQL, které zobrazí :

  1. Počet osob
  2. Počet osob bez karty
  3. Počet nepřidělených karet
  4. Identifikační číslo osoby a PIN její karty
  5. Identifikační číslo osoby a PIN její karty včetně osob bez karty
    (u nich nastavte PIN na nulu)
  6. Jména členů skupiny Udrzbari
  7. Jména skupin v abecedním pořadí s počtem členů
  8. Příjmení a jméno osoby se jménem skupiny jejíž je členem
  9. Jména skupin s počtem členů seřazená podle počtu členů
  10. Jména objektů kam má přístup skupina Vsichni
  11. Kam a v jaké časové zóně mají osoby individuální přístup
  12. Kam a v jaké časové zóně mají osoby skupinový přístup
  13. Kam a v jaké časové zóně má přístup Ondrej Pesicka
  14. Kdo a kdy má přístup do objektu Sekretariat
  15. Jméno skupiny, která má nejvíce členů
Řešení