Hajas Csilla Adatbazisok-I.gyakorlat

8.GYAKORLAT (ADATBÁZISOK)

Nézettáblák, munkatáblák létrehozása és használata; Rekurzió, with záradék
>> I.RÉSZ - NÉZETTÁBLÁK, MUNKATÁBLÁK LÉTREHOZÁSA ÉS HASZNÁLATA
>> II.RÉSZ - HIERARCHIKUS, GRÁFOS ADATSZERKEZETEK, WITH ZÁRADÉK

I.RÉSZ Nézettáblák [CREATE OR REPLACE | DROP ] VIEW; WITH munkatáblák

1.) Egyszerű/összetett nézettáblák létrehozása, melyik módosítható/melyik nem?
    Adatok karbantartása adattáblán illetve nézeten keresztül, és megfigyelni ezek
   egymásra hatását: Hogyan hat a nézetek adatainak módosítása az adattáblára,
   és a másik irányban, az adatok módosítása hogyan jelenik meg a nézetben?
>> Oracle SQL CREATE VIEW: SQL Lang.Ref. CREATE VIEW
>> [ kiegészítés: Oracle segédanyagok: SQL11_view.pdf ]

2.) Például: Bonyolultabb lekérdezés megoldása nézettáblák segítségével...
Képezzük osztályonként az összfizetést, vegyük ezen számok átlagát, és
adjuk meg, hogy mely osztályokon nagyobb ennél az átlagnál az összfizetés.

CREATE OR REPLACE VIEW osztaly_osszfiz
AS
   SELECT onev, SUM(fizetes) ossz_fiz
FROM sila.dolgozo d, sila.osztaly o
WHERE d.oazon = o.oazon
GROUP BY onev;

CREATE OR REPLACE VIEW atlag_koltseg
AS
SELECT SUM(ossz_fiz)/COUNT(*) atlag
FROM osztaly_osszfiz;

SELECT * FROM osztaly_osszfiz
   WHERE ossz_fiz > (SELECT atlag FROM atlag_koltseg);

-- -- -- -- --
3.) Példák WITH munkatáblák használatára:
- lásd SQL08_alkerdes2.pdf (16-18.o. példa, uez with-re példa SQL Lang.Ref.)
- Oracle: SQL Language Reference >> innen: Subquery Factoring: Examples
Képezzük osztályonként az összfizetést, vegyük ezen számok átlagát, és
adjuk meg, hogy mely osztályokon nagyobb ennél az átlagnál az összfizetés.

WITH
osztaly_osszfiz AS (
       SELECT onev, SUM(fizetes) ossz_fiz
     FROM sila.dolgozo d, sila.osztaly o
     WHERE d.oazon = o.oazon
     GROUP BY onev),
atlag_koltseg AS (
   SELECT SUM(ossz_fiz)/COUNT(*) atlag
     FROM osztaly_osszfiz)
SELECT * FROM osztaly_osszfiz
WHERE ossz_fiz > (SELECT atlag FROM atlag_koltseg);

-- -- -- -- --
>> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 6.fejezet feladatai

II.RÉSZ: HIERARCHIKUS ADATSZERKEZETEK ÉS GRÁFOK, REKURZIÓ

1.) Hierarchikus adatszerkezetek lekérdezése Oracle-ben: CONNECT BY
>> Oracle: SQL12_hiera_lekerd.pdf; -- példákhoz: create_hr_synonym.txt
>> Oracle: LiveSQL Tutorial_HieraQueries.html
>> Oracle Példatár 3.fej. Hierarchikus lekérdezések: peldatar_62-65o.pdf
>> Oracle DB SQL Lang. Ref. 12.2: Hierarchical Queries html (Examples)
   SQL Hierarchikus függvények: sys_connect_by_path html (Examples)
   SELECT ... FROM... WHERE ... START WITH ... CONNECT BY PRIOR ...

-- Az Oracle SQL Lang.Ref. 9.fej. SELECT példáit írjuk át, lépésenként...
select dkod, dnev, fonoke from dolgozo;

select dkod, dnev, fonoke, level
from dolgozo
start with dkod = 7839 -- 'KING'
   -- (select dkod from dolgozo where dnev='KING')
connect by prior dkod = fonoke;

-- Ha a fát felülről-lefelé, előrefelé járjuk be:
select dkod, dnev, fonoke, level
from dolgozo
start with dkod = 7566 -- 'JONES'
connect by prior dkod = fonoke;

-- Ha a fát alulról felfelé, visszafelé járjuk be:
select dkod, dnev, fonoke, level
from dolgozo
start with dkod = 7566 -- 'JONES'
connect by prior fonoke = dkod;

-- SQL Lang.Ref. 7.fej. SYS_CONNECT_BY_PATH fv-re való példát is írjuk át:
select dkod, dnev, fonoke, level,
LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(dnev, '/') "Útvonal"
from dolgozo
start with dkod = (select dkod
    from dolgozo where dnev='KING')
connect by prior dkod = fonoke;

-- -- -- -- --
>> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 3.fejezetben is volt.

-- -- -- -- --
2.) Gráfok lekérdezése, rekurzió az SQL szabványban: WITH RECURSIVE
Az Eljut-feladat a Tankönyv (Ullman-Widom kék könyv) 10.2 szakaszára épül
      Adott Jaratok(legitarsasag, honnan, hova, koltseg, indulas, erkezes) táblában
repülőjáratok adatait tároljuk (honnan-hova várospárok). Azt keressük, hogy
Dallasból mely városokba tudunk eljutni (közvetlenül vagy egy/több átszállással).
>> Ullman-Widom tankönyv 10.2 szakasza Eljut-feladat: korábbiEA/p67_p80.pdf
>> Oracle: SQL Language Reference >> innen: Recursive Subquery Factoring

  >> Az Ullman-Widom Tankönyv szerint (SQL szabvány szerint, csak papíron)
     (konkrét adatbázis-kezelő rendszerekben eltérő megvalósítások lehetnek)

WITH RECURSIVE Eljut(honnan, hova) AS
(SELECT honnan, hova FROM Jaratok
UNION
      SELECT Eljut.honnan, Jaratok.hova
      FROM Eljut, Jaratok
      WHERE Eljut.hova = Jaratok.honnan)
SELECT hova FROM Eljut WHERE honnan='DAL';

--- Kérdés: itt miért fontos kiemelni az UNION (halmaz) és
   UNION ALL (multihalmaz) közötti különbséget?

-- -- -- -- --
3.) Az Eljut feladat megvalósítása Oracle-ben WITH utasítással:
   Ezzel a scripttel jaratok_tabla.txt készítsünk saját táblát.
  >> Az SQL-99 szabványhoz képest az eltérések, hogy WITH RECURSIVE helyett
az Oracle-ben csak WITH, és a UNION az Oracle-ben nem megy, hanem csak
   UNION ALL működik, és nem-hierarchikus esetben meg kell adni a CYCLE-t is:

WITH Eljut (honnan, hova) as
(SELECT honnan, hova FROM Jaratok
UNION ALL
SELECT Jaratok.honnan, Eljut.hova
FROM Jaratok, Eljut
WHERE Jaratok.hova=Eljut.honnan
)
SEARCH DEPTH FIRST BY honnan SET SORTING
CYCLE honnan SET is_cycle TO 1 DEFAULT 0
SELECT distinct honnan, hova
FROM Eljut
ORDER BY honnan;

-- -- -- -- --
4.) Az Eljut feladat megvalósítása Oracle-ben CONNECT BY megoldással
   Az előző feladat scripttel jaratok_tabla.txt készítsünk saját táblát.
   Amíg nincs kör, a fenti 1.f. szerinti hierarchikus lekérdezéssel működik
  >> Ha kör van a gráfban, akkor NOCYCLE kiegészítéssel már működik

SELECT LPAD(' ', 4*level) || honnan, hova,
level-1 atszallasok,
sys_connect_by_path(honnan||'->'||hova, '/'),
connect_by_isleaf, connect_by_iscycle
FROM Jaratok
START WITH honnan = 'SF'
CONNECT BY NOCYCLE PRIOR hova = honnan;

-- -- -- -- --
5.) További lehetőségek, érdekességek (nem-tananyag, opcionális)
  >> Reguláris kifejezések: SQL_regularis_kif.txt; SQL_regular_exp.pdf
  >> Analitikus függvények: Oracle Példatár 13.fej.; Tutorial: Analytic Funct.

-- -- -- -- --
  >> KÖV.TÉMA JÖN 9.-11.gyak. PL/SQL - SQL progr.nyelvi környezetben használata