Adatbázis_I

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

> Gépes feladatok: Oracle 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
-- Beküldendő nem-kötelező h.f.: DML_feladatok.txt
>> 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)

> Papíros feladatok:
>> 2.6. Lekérdezések kifejezése Datalogban

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)

-- Beküldendő nem-kötelező házi feladat, bemutatás esetén órai+pont,
amely csak féljegy esetén számít bele a gyak.jegybe (felfelé kerekítés),
hasonlóan, mint az I.ZH előtti belépő dolgozatok (célja: a gyakorlás)
-- Beküldése e-mailben sila@inf.elte.hu címre HATÁRIDŐ: május 5. 12h

FELADATOK: DML_feladatok.txt (Nikovits Tibor)
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 (Nikovits Tibor)

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"

(papíros feladatok)
2.6. Lekérdezések kifejezése Datalogban, vagyis egy logikai lekérdező nyelven

Áttekintés:
- Az 1-2. gyakorlat lekérdezéseit először természetes nyelven fogalmaztuk meg,
- majd "táblákban gondolkodva" azt néztük meg, hogyan tudtuk mely táblából
milyen műveletekkel tudtuk megkapni az eredménytáblát,
- majd megadtuk formálisan relációs algebrában (halmazokon),
- Ezután a 3-4. gyakorlaton SQL SELECT utasítással (multihalmazokon) fejeztük ki
ugyanezeket a lekérdezéseket. Az 5-6. gyakorlaton néztünk további lehetőségeket
az SQL SELECT-ben, majd kiterjesztettük a relációs algebrát erre a lehetőségekre.
- Visszatérve az 1-2. gyakorlatok alap relációs algebrai lekérdezéseire (halmazokon)
ugyanezeket a lekérdezéseket fejezzük ki (nem rekurzív) Datalog programmal.

Segédanyag: lásd az előadás anyagát Datalog (jelszóval) vagy
[Ullman-Widom] 5.3-5.4. Datalog szabályok és lekérdezések

Feladatok:
Egyszerű táblák és lekérdezések a kezdetekhez:
Szeret tábla és feladatok
Sörivók adatbázis és feladatok
Tankönyv Termék sémán alapuló feladatai:
Termék-PC-Laptop-Nyomtató adatbázis
Hajóosztályok-Csaták-Kimenetelek adatbázis
A fenti linkeken elérhető feladatokat fejezzük ki nem-rekurzív Datalog programmal.

Megjegyzés. A rekurziót a következő gyakorlaton tárgyaljuk, például a tranzitív lezárt
nem fejezhető ki relációs algebrában, viszont rekurzív Datalog programmal megadható.

Vissza az AB1 gyakorlat oldalára

Vissza a Kezdőlapra