Adatbázis_I

8.gyak. SQL áttekintése, nézetek, inline nézet, with utasítás.
Hierarchikus lekérdezések (CONNECT BY PRIOR)

> Az előző órai I.ZH papíros és gépes feladatainak a megbeszélése.
> Ma gépes gyakorlatot tartunk: Oracle: Teljes SQL áttekintése
>> 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
>> 2.5. Hierarchikus lekérdezések (CONNECT BY PRIOR)
> Következő héten táblás gyakorlat lesz, két fő témakört nézünk meg:
a.) az E/K modell és az E/K diagramok átírása relációs modellre
(ezeket a táblákat és megszorításokat is majd létrehozzuk: 2.1.),
b.) lekérdezések Datalogban, relációs algebra és Datalog átírások.
(HF. a köv.hétre átnézni az E/K modell és Datalog előadás anyagot).

ORACLE: TELJES 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 DML (INSERT)
-- A megszorításoknak eleget tevő új adatsorok felvitele,
-- illetve új sorok felvitele meglévő tábla adataiból.
SQL DCL (jogok)
-- táblákhoz és a nézetekhez jogok kiadása/átadása: GRANT
és visszavonása: REVOKE
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).

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 (create table)
Integritási megszorítások a rel.modellben, relációs adatbázis sémák.
Kulcs, egyed- és hivatkozási integritási megszorítások.
[1UW] 2.3. Relációsémák definiálása SQL-ben (30-39.o.)
[1UW] 2.5. Relációkra vonatkozó megszorítások (61-68.o.)
[1UW] 7.fejezet: Megszorítások és triggerek (329-360.o.)

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: lásd 2.1. Dolgozo és Osztaly táblák létrehozása

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

FELADATOK: Nezet_feladatok.txt

1.feladat:
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.

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 9.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

Megj: Példatár 6.fejezet elméleti összefoglalóban szerepel: "felső-N analízis"

2.5. Hierarchikus lekérdezések az Oracle-ben (CONNECT BY PRIOR)
Családfák. SFW START WITH ... CONNECT BY PRIOR ...

Segédanyagok:
>> hiera_lekerd.txt (Nikovits Tibor) valamint AB1_Hierarch.pdf (Kiss Attila)
>> Hierarchikus.pdf (jelszavas anyag) példáit is próbáljuk ki az Oracle-ben!
-- ehhez a HR séma tábláihoz készítsük el a szinonimákat create_synonym

Feladatok a hierarchikus lekérdezésekhez
- Táblák és feladatok: dolgozo_tabla.txt Ehhez a táblák létrehozása: create_dolgozo.txt
- Listázzuk ki az dolgozo tábla alapján a főnökökhöz tartozó beosztottak nevét és osztályukat.
   a.) A dolgozo tábla önamagára való hivatkozásával (többtáblás lekérdezés sorváltozókkal).
   b.) A CONNECT BY utasításrész használatával, a hierarchikus szerkezetet 'KING'-től
   felülről lefelé bejárva.
   c.) Alulról felfelé járjuk be a hierarchikus szerkezet egy ágát 'SMITH'-től kezdve.

Megj: Példatár 3.fejezet elméleti összefoglalóban "Hierarchikus adatszerkezet megjelenítése"

Nem kötelező beadandó gyakorló feladatok:
Kérem, hogy a mai órai feladatok (lásd DML_feladatok.txt) és az Oracle példatár
(lásd Feladatok.pdf 5-6.fejezet) további gyakorló feladatainak a saját megoldását,
az önálló gyakorlást egy "<ehakod>_8gy.txt" nevű szöveges fájlban mentse el és
nem-kötelező beadandóként legkésőbb a köv. kedd 10h-ig külde el emailben.
A beadandó célja az önálló gyakorlás, lásd Gyakorlati követelmények #Beadandó.

Vissza az AB1 gyakorlat oldalára

Vissza a Kezdőlapra