7.gyak. SQL DDL, DML áttekintése, nézetek, inline nézetek
   
-- papíros feladatok/táblás gyakorlat: E/K diagram leképezése relációkra
-- gépes gyakorlat: Oracle: SQL teljes áttekintés 
> 2.1. Relációsémák és megszorítások definiálása SQL-ben
> 2.2. DML: insert, delete, update  
> 2.3. Feladatok nézetekre, inline nézetre és WITH utasításra
> 2.4. Felső-N elemzés ROWNUM segítségével
        
E/K diagram és leképezése relációs modellre
Ullman-Widom: Adatbázisrendszerek. Alapvetés (Második, átdolg.kiad), 2009.
-- E/K modell és leképezése relációs modellre, [UW1] 4.1-4.6.fej., 133-181.o.
-- II.ZH-án várható feladat: adott E/K diagram átírása relációs modellé, vagyis
    1.) egyedhalmazok és 2.) kapcsolatok átírása relációkká, 3.) összevonások,
    4.) gyenge egyedhalmazok kezelése, 5.) osztályhierarchia ("isa") átalakítása
    relációkká (a három megközelítés: E/K típusú, obj.orientált, nullértékekkel).
   
Példák: E/K diagram leképezése relációs modellre
    a Tankönyv 4.5-4.6 fejezetei és feladatai alapján:
-- lásd E/K_pl1.pdf és HF: E/K_pl2.pdf    

ORACLE SQL ÁTTEKINTÉSE:
SQL DDL (CREATE TABLE, CREATE VIEW)
-- Táblák és megszorítások (constraints) létrehozása.
-- Egyszerű és összetett nézettáblák létrehozása. 
SQL tranzakciókezelés
-- tranzakciókezelés alapjai, érvényesítés: COMMIT,
    és visszagörgetés: ROLLBACK
    és mentési pontok: SAVEPOINT
SQL DML (INSERT, DELETE, UPDATE) 
-- 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?
-- Eközben mentési pontokat adjunk meg,  ahova folyton
    visszagörgetjük (visszaállítjuk a táblák eredeti állapotát).
SQL lekérdezések: Oracle specialitások
-- Felső-N elemzés ROWNUM segítségével  
-- Hierarchikus lekérdezések CONNECT BY PRIOR
Ennyi! -- Jön: Oracle PL/SQL (8-11. gyakorlatokon)
     
2.1. Relációsémák és megszorítások definiálása SQL-ben
   
CREATE TABLE - Relációsémák és megszorítások megadása
   
Példák és feladatok:
>> Dolgozo és Osztaly táblák (megszorításokkal való) létrehozása
>> SQL_21_Tablak.pdf  példái -- Ehhez create_synonym_HR
>> Táblák létrehozása, Oracle típusok
>> Megszorítások példák 
   
További gyakorló feladatok: lásd Példatár "5.fejezet. Adattáblák létrehozása,
módosítása, tranzakciók, megszorítások" DML- és DDL- utasításokra feladatok
   
2.2. Adattáblák tartalmának módosítása (DML)
    
Feladatok: DML_feladatok.txt 
Előkészítés: ezekhez a DML feladatokhoz átmenetileg hivatkozási épség
megadása nélkül hozzuk létre a táblákat:
>> Módosított Dolgozo és Osztaly táblák létrehozása (no constraints)
 
DELETE
-- Töröljük azokat a dolgozókat, akiknek jutaléka NULL.
-- Töröljük azokat a dolgozókat, akiknek a belépési dátuma 1982 előtti.
-- Töröljük azokat a dolgozókat, akik osztályának telephelye DALLAS.
-- Töröljük azokat a dolgozókat, akiknek a fizetése kisebb, mint az átlagfizetés.
-- Töröljük a jelenleg legjobban kereső dolgozót.
-- Töröljük ki azokat az osztályokat, akiknek van olyan dolgozója,  aki
    a 2-es fizetési kategóriába esik (lásd még SALGRADE táblát).
    Adjuk meg azon osztályok nevét, amelyeknek van olyan dolgozója,  aki
    a 2-es fizetési kategóriába esik)
-- Töröljük ki azon osztályokat, amelyeknek 2 olyan dolgozója van, aki
    a 2-es fizetési kategóriába esik.

INSERT
- Vigyünk fel egy 'Kovacs' nevű új dolgozót a 10-es osztályra a következő
   értékekkel: dkod=1, dnev='Kovacs', oazon=10, belépés=aktuális dátum,
   fizetés=a 10-es osztály átlagfizetése. A többi oszop legyen NULL.

UPDATE
-- Növeljük meg a 20-as osztályon a dolgozók fizetését 20%-kal.
-- Növeljük meg azok fizetését 500-zal, akik jutaléka NULL vagy a fizetésük
    kisebb az átlagnál.
-- Növeljük meg mindenkinek a jutalékát a jelenlegi maximális jutalékkal.
    (NULL tekintsük 0-nak)
-- Módosítsuk 'Loser'-re a legrosszabbul kereső dolgozó nevét.
-- Növeljük meg azoknak a dolgozóknak a jutalékát 3000-rel, akiknek
    legalább 2 közvetlen beosztottjuk van.
    Az ismeretlen (NULL) jutalékot vegyük úgy, mintha 0 lenne.
-- Növeljük meg azoknak a dolgozóknak a fizetését, akiknek van beosztottja
    a minimális fizetéssel
-- Növeljük meg a nem főnökök fizetését a saját osztályuk átlagfizetésével
   
További gyakorló feladatok: lásd Példatár "5.fejezet. Adattáblák létrehozása,
módosítása, tranzakciók, megszorítások" DML-utasításokra feladatok
   
2.3. Nézetek, inline nézet, WITH utasítás az Oracle-ben (ez nem a szabvány!)
  
CREATE VIEW - Egyszerű és összetett nézettáblák létrehozása és használata.
Kipróbálni. Mikor lehetséges az alaptáblák karbantartása a nézeteken keresztül?
További részletek, lásd  SQL Language Reference -> Nézettáblák létrehozása 
 
Feladatok:
1.feladat: (Nikovits Tibor) A lekérdezésekhez NIKOVITS felhasználó
tulajdonában levő táblákat használjuk! Elérése: tulajdonos.táblanév
   
NIKOVITS.CIKK (ckod, cnev, szin, suly)
NIKOVITS.PROJEKT (pkod, pnev, helyszin)
NIKOVITS.SZALLITO (szkod, sznev, statusz, telephely)
NIKOVITS.SZALLIT (szkod, ckod, pkod, mennyiseg, datum)
   
-- Melyik cikket szállítják a legtöbb projekthez?
   
A lekérdezést adjuk meg nézettáblák létrehozásával valamint úgy is, hogy
ne kelljen nézetet létrehozni, a "nézet" csak a lekérdezés idejére jön létre:
adjuk meg a lekérdezést INLINE nézettel, majd ugyanez WITH-del is.
 
Megoldása: Nezet_feladatok.txt (WITH munkatáblák használatára is példa)
Figyelem! Ez az Oracle WITH utasítás nem ugyanaz, mint az előadáson
és a kék-Tk 10.2 fejezetében szereplő WITH RECURSIVE utasítás!
Rekurzió az SQL-ben feladatokra a 11.gyakorlaton nézzünk példákat!
   
2.feladat:
DOLGOZO (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
OSZTALY (oazon, onev, telephely)
   
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.
 
További gyakorló feladatok: lásd Példatár "6.fejezet. Nézettáblák" feladatai
valamint "3.fejezet. Allekérdezések a FROM utasításrészben" feladatai illetve
összetett feladatai és a "7.fejezet. Összetett SQL-feladatsorok" feladatai 
     
2.4. Felső-N elemzés ROWNUM segítségével ("toplista" készítése)
  
Felső-N elemzés (például a ZH-n is hasznos, amikor túl nagy az eredménytábla
és csak az első 5 sorát kell beküldeni). a felső 5 sor kiíratása az alábbi módon:
   
  SELECT  *  FROM 
       (SELECT... FROM ...WHERE... ORDER BY <oszlop> DESC)
       WHERE  ROWNUM  <= 5
   
További részletek, lásd Oracle doksiban SQL Language Reference
2 Pseudocolumns fejezetben (44/1508) ROWNUM Pseudocolumn
   
ROWNUM - pszeudooszlop a kiválasztásra kerülő sorokhoz hozzárendeli
a kiválasztásuk sorszámát, de a WHERE feltételben nem állhat fent egyenlőség
(ez nem sorszám, hanem a lekérdezés eredményének a sorrendjét adja meg).
WHERE feltételben ROWNUM < N illetve ROWNUM <= N használható!
Hibás! WHERE feltételben ROWNUM > N illetve ROWNUM = N (hibás!) 
           (nem ad vissza sort) 
   
Megj: Példatár 6.fejezet elméleti összefoglalóban szerepel: "felső-N analízis"
    
Fel a lap tetejére                          Vissza az AB1gyak oldalára (főmenü)