8.GYAKORLAT: SQL with recursive [dr. Hajas Csilla]  
 
 
           
-- -- --
  I.rész:  WITH utasítás, de előtte még egy példa nézettáblák használatára:
   -- 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)
   
-- Ugyanez WITH-záradékkal, lásd SQL08_subquery2.pdf (16-18.o. WITH záradék)
   WITH-záradék, lásd Subquery Factoring: Examples 
   --- gépes lekérdezési feladatok munkatáblák segítségével, és
   --- nézzünk példákat nézettáblák <-> munkatáblák átírásokra  
 
  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)
 
           
-- -- --
   II.rész:  Hierarchikus és gráfos adatok lekérdezése, WITH RECURSIVE utasítás
   Elméleti összefoglaló:  8.ea.pdf (Eljut feladat)
   Rekurzió: Tankönyv 10.2. (Ullman-Widom kék könyv) Eljut feladat
 
        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 illetve több, akárhány átszállással). 
     
   >> 3.1. SQL szabvány szerint (csak papíron)
        (konkrét adatbázis-kezelő rendszerekben eltérő megvalósítások)  
   
            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.2. Az Eljut feladat gépes megvalósítása Oracle-ben:
     -- Ezzel a scripttel jaratok_tabla.txt készítsünk saját táblát.
     --  A fenti szabvány SQL megoldás csak egy kis változtatással
         fut le gépes környezetben! Például Oracle 11gR2 megoldás:
   
            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;
     
     -- Példák az Oracle SQL Language Reference 11.2.pdf dokumentációban:
         >> Rekurzió with-utasítással: Recursive Subquery Factoring: Examples
   
  >> 3.3. Hierarchikus lekérdezésekre Oracle megoldás (8.ea.pdf legvégén)
   > SQL Lang.Ref: Hierarchical Queries, Hierarchical Query Examples
      SELECT ... FROM... WHERE ... START WITH ... CONNECT BY PRIOR ...