10.GYAKORLAT (ADATBÁZISOK)  
   
   Témakör: táblák, nézettáblák, munkatáblák (SQL gépes gyakorlat)  
   I.  SQL DDL (táblák, nézettáblák létrehozása és használata) 
       ceate table+constraints: táblák, megszorítások létrehozása
       create view: módosítható és nem módosítható nézettáblák
       majd a meta-adatok lekérdezése az adatszótár nézetekből
   II. SQL SELECT WITH munkatáblák
       hierarhikus illetve gráf struktúrájú adatok lekérdezése
     
   
Feladatok:  
-- -- -- -- --
>> SQL DDL, relációs séma megadása = sortípus + megszorítások: 6EA.pdf; 
>> Oracle: SQL Language Reference >> innen Data Types; illetve Contraint; 
 
1.) Dolgozo, Osztaly, Fiz_Kategoria táblák, példa: createDolg+Constraints  
 
        Dolgozo (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)      
        Osztaly (oazon, onev, telephely)
        Fiz_Kategoria (kategoria, also, felso)
 
     Készítsünk drop table mytabla; create table mytabla as select * from tabla;  
     másolatokat a dolgozo és osztaly táblákból mydolgozo és myosztaly néven,
     véglegesítse az adattáblákat (commit), majd a létrehozott táblákat lássa el
     az alábbi megszorításokkal, alter table példák  (mo_ell: megszorítások.txt)
    - Legyen a dkod elsődleges kulcs a mydolgozo táblában, és
      legyen az oazon elsődleges kulcs a myosztaly táblában és idegen kulcs a
      mydolgozo táblában, amely a hivatkozási épséget itt úgy állítsa be, hogy
      egy osztály törlése esetén törlődjenek ennek az osztálynak a dolgozói is.
   - Egy új dolgozó csak az adott 700 és 7000 USD értéktartománybeli fizetést
     kaphasson.

    - Ellenőrizze ezeket a megszorításokat sikeres (megfelelő rekord felvitele)
      és sikertelen (hibás rekord) adatbeviteli kísérletekkel, majd állítsa vissza
      az eredeti táblatartalmakat (rollback).
 
-- -- -- -- --
2.)  Készítsünk két táblát az egyikben legyenek sportcsapatok csapat_id, név.
      A másikban a játékosok, id, név, mezszám, csapat_id. A csapat azonosító 
      legyen idegen kulcs. (lásd még: create table-példák.txt).  
 
-- -- -- -- --
>> SQL DDL create view: módosítható/nem-módosítható nézettáblák: 7EA.pdf;
 
3.) 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?
 
>> 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)
   
-- -- -- -- --
4.) Adatszótár nézetek - Táblák és oszlopai, megszorítások
     A DBA_TABLES az adatbázisban levő összes tábláról, DBA_TAB_COLUMNS
     a táblák oszlopairól tartalmaz információt (oszlopok neve, típusa, sorrendje).
  -- A fent létrehozott táblák adatait keressük meg az adatszótár nézetekben,
     milyen információkat találhatunk a táblákról és a táblák oszlopairól? 
     Adatszótár nézetek: DBA_OBJECTS, DBA_TABLES és DBA_TAB_COLUMNS
 -- Nézzük meg hogyan jelennek meg a megszorítások a DBA_CONSTRAINTS-ban
 -- Nézzük meg a fentebb létrehozott nézeteket a DBA_VIEWS katalógustáblában.
 
HF. köv.gyak-ra: Feladatok a DBA_TAB_COLUMNS  adatszótár nézet lekérdezésére
a.) Hány oszlopa van a sila.dolgozo táblának?
b.) Milyen típusú a sila.dolgozo tábla 6. oszlopa?
c.) Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek
     van 'Z' betűvel kezdődő oszlopa.
d.) Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek
     legalább 8 darab dátum tipusú oszlopa van.
e.) Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek
    1. es 4. oszlopa is VARCHAR2 tipusú.
    
   
II.rész: WITH munkatáblák használata az SQL SELECT lekérdezésekben
5.) WITH utasítás: lásd a fenti példát nézettáblákra, ugyanez WITH-záradékkal,
>> Oracle: SQL Language Reference >> innen: Subquery Factoring: Examples 

   Példák WITH munkatá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.
    
  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)
 
-- -- -- -- --
6.) Rekurzió: SQL WITH RECURSION utasítás
>> Eljut feladat, gráfok lekérdezése: 7EA.pdf (végén, lásd Eljut feladat)
>> Oracle: SQL Language Reference >> innen: Recursive Subquery Factoring
        
     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). 
     
    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 szabvány SQL megoldás kis változtatással fut le gépes környezetben!
       Például Oracle 11gR2 verztiótól kezdve működik az alábbi 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;
   
-- -- -- -- --
7.) Hierarchikus lekérdezések
>> Rekurzióra egy másik Oracle megoldás: SQL12_hiera_lekerd.pdf   
>> Oracle: SQL Language Reference >> innen: 
     SELECT ... FROM... WHERE ... START WITH ... CONNECT BY PRIOR  
     Oracle Példatár 3.fejezetében: Hierarchikus lekérdezések (62-65.o.)