Hajas Csilla Adatbazisok-I.gyakorlat

3.HETI GYAKORLAT (LEVELEZŐ INFO TANÁR - ADATBÁZISOK)

Témakörök:
1.rész: Alkérdések az SQL SELECT utasítás FROM, WHERE, HAVING záradékaiban
2.rész: SQL DML, táblák tartalmának módosítása: insert, delete, update, illetve
SQL tranzakció-kezelés utasítások: commit, rollback,
3.rész: SQL DDL: táblák, megszorítások létrehozása: ceate table, constraints
   módosítható és nem módosítható nézettáblák használata: create view
4.rész: SQL kiegészítések: WITH utasítás (SQL SELECT), indexek: create index

Táblák előkészítése: Az SQL DML feladatokhoz most a megszorítások nélkül
   (mint például 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) (új!!!)

  Dolgozo (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
Osztaly (oazon, onev, telephely)
Fiz_Kategoria (kategoria, also, felso)

1.rész: Többtáblás lekérdezések, alkérdések (FROM, WHERE, HAVING)
> Többtáblás: SQL05_set_operators.pdf; SQL06_osszekapcsolas.pdf
> Alkérdések: SQL07_subquery1.pdf; SQL08_subquery2.pdf

1. Kik azok és melyik telephelyen dolgoznak a legnagyobb fizetésű dolgozók?
2. Adjuk meg osztályonként a minimális fizetést, de csak azokat az osztályokét, ahol
a minimális fizetés nagyobb, mint a 30-as osztályon dolgozók minimális fizetése.
3. Adjuk meg, hogy mely dolgozók fizetése jobb, mint a saját osztályán (vagyis
azon az osztályon, ahol dolgozik az ott) dolgozók átlagfizetése! (korrelált)
4. Adjuk meg osztályonként a legnagyobb fizetésű dolgozó(k) minden adatát.
5. Adjuk meg a legrosszabbul kereső főnök fizetését, és fizetési kategóriáját.
-- SQL DML utasítások WHERE-ben is használunk alkérdéseket, ezekkel folytatjuk!

>> Önálló gyakorlás: Oracle Példatár Feladatok.pdf 3.fejezet feladatai

2.rész: SQL DML utasítsok (INSERT, DELETE, UPDATE)
   > SQL segédanyagok: SQL09_DML.pdf

>> a.) DELETE
1. Töröljük azokat a dolgozókat, akiknek jutaléka NULL.
2. Töröljük azokat a dolgozókat, akiknek a belépési dátuma 1982 előtti.
3. Töröljük azokat a dolgozókat, akik osztályának telephelye DALLAS.
4. Töröljük azokat a dolgozókat, akiknek a fizetése kisebb, mint az átlagfizetés.
5. Töröljük ki azokat az osztályokat, akiknek van olyan dolgozója, aki a 2-es
   fizetési kategóriába esik (vagyis az alkérdésben adjuk meg azon osztályok
   nevét, amelyeknek van olyan dolgozója, aki a 2-es fizetési kategóriába esik)
6. Töröljük ki azon osztályokat, amelyeknek 2 olyan dolgozója van, aki a 2-es
   fizetési kategóriába esik.

>> b.) INSERT
  7. 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.
8. Több sor felvitele: Hozzunk létre egy UjOsztaly nevű táblát, amelynek
attribútumai megegyeznek az Osztály tábla oszlopaival, plusz van még egy
numerikus típusú Letszam nevű attribútuma. Ebbe az UjOsztaly táblába az
insert utasítás 2. alakjával (alkérdéssel ) vigyünk fel új sorokat az osztály és
dolgozó táblák aktuális tartalmának felhasználásával minden osztály adatát
kiegészítve az adott osztályon dolgozók létszámával. Azok az osztályok is
jelenjenek meg ahol nem dolgozik senki, ott a létszám 0 legyen. Továbbá
ha vannak olyan dolgozók, akiknek nincs (nem ismert) az osztályuk, azok
létszámát egy oazon=0, onev= 'FIKTIV' és telephely='ISMERETLEN'
adatokkal rendelkező sorba írjuk be.

>> c.) UPDATE
   9. Növeljük meg a 20-as osztályon a dolgozók fizetését 20%-kal.
10. Növeljük meg azok fizetését 500-zal, akik jutaléka NULL vagy
   a fizetésük kisebb az átlagnál.
11. Növeljük meg mindenkinek a jutalékát a jelenlegi maximális jutalékkal.
12. Módosítsuk 'Loser'-re a legrosszabbul kereső dolgozó nevét.
13. 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.
14. Növeljük meg azoknak a dolgozóknak a fizetését, akiknek van olyan
   beosztottja, aki minimális fizetéssel rendelkezik.
15. Növeljük meg a nem főnökök fizetését a saját osztályuk átlagfizetésével.

-- -- --
> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 5.fejezet feladatai

3.rész: SQL DDL Táblák, megszorítások és nézettáblák
   > SQL segédanyagok:  SQL10_DDL.pdf; SQL11_view.pdf
> További segédletek: create table, típusok, megszorítások (.txt)

>> a.) Táblák és megszorítások (constraints) létrehozása.
1.) Írjon szkript programot, amely új adattáblát hoz létre az emp és dept táblákból
myemp és mydept néven. Ha már léteznek ilyen nevű táblák, akkor előbb
    azokat törölje. Véglegesítse az adattáblákat (commit).
    A létrehozott táblákat lássa el az alábbi megszorításokkal:
- Legyen az empno elsődleges kulcs a myemp táblában, és
- legyen a deptno elsődleges kulcs a mydept táblában és idegen kulcs a myemp
táblában, amely a hivatkozási épséget biztosítja oly módon, 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.
2.) 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). Véglegesítse az adattáblákat (commit).

>> b.) Nézettáblák létrehozása és használata
3.) Egyszerű és összetett nézettáblák létrehozása, melyik módosítható és melyik nem?
4.) 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.
Tranzakciókezelés alapjai, mentési pontok: SAVEPOINT és
visszagörgetés: ROLLBACK (illetve érvényesítés: COMMIT).
5.) 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). Véglegesítse az adattáblákat (commit).

-- -- --
> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 5-6.fejezet feladatai

4.rész: SQL kiegészítések: WITH utasítás (SQL SELECT), indexek

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.

>> a.) Bonyolultabb lekérdezés megoldása nézettáblák segítségével
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)

>> b.) Ugyanez WITH-záradékkal, lásd SQL08_subquery2.pdf (16-18.o. WITH)
WITH-záradék, lásd Oracle SQL Lang.Ref.: 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)

>> c.) Indexekhez előkészítés: ROWID
18 karakteren írodik ki, a következő formában: OOOOOOFFFBBBBBBRRR
OOOOOO - az objektum azonosítója
FFF    - fájl azonosítója (táblatéren belüli relativ sorszám)
BBBBBB - blokk azonosító (a fájlon belüli sorszám)
RRR    - sor azonosító (a blokkon belüli sorszám)

A ROWID megjelenítéskor 64-es alapú kódolásban jelenik meg.
Az egyes számoknak (0-63) a következő karakterek felelnek meg:
A-Z -> (0-25), a-z -> (26-51), 0-9 -> (52-61), '+' -> (62), '/' -> (63)

Pl. 'AAAAAB' -> 000001

select rowid, d.* from sila.dolgozo d;

>> d.) Indextáblák
Hozzunk létre egy vagy több táblához több különböző indexet!
Legyen köztük több oszlopos, csökkenő sorrendű, függvény alapú,
fordított kulcsú (reverse), bitmap index, lásd create index példák.txt
   >> Oracle SQL Language Reference >> innen CREATE INDEX
>> Alapfogalmak Oracle Database Concepts >> 3 Indexes