Hajas Csilla Adatbazisok-I.gyakorlat

ADATBÁZISOK-1 GYAKORLAT [prog.inf: IP-08AB1G]
ELTE, 2016/2017-es tanév I.félévében [2016.szept.]

Gyakorlatok: [1.] Péntek 8:30-10:00 [2.] Péntek 10:15-11:45 MI labor
Előadás: Kedd 8:30-10:00  Déli ép. 0-822 Mogyoródi [link: AB1EA.html]
Az előadás és gyakorlat kötelező, katalógus van, 4 hiányzás lehetséges.
>> Ellenőrzésre jelenléti ív (név-> 4kar-kód) hallgato/[jelszóval] [itt:.pdf]

   Követelmények: Három dolgozatot írunk. A gyakorlatok anyaga:
> SQL SELECT >> 1.GY >> 2.GY >> 3.GY >> 4.GY >> 5.GY: zh1 (okt.14.)
> SQL SELECT+DML,DDL >> 6.GY >> 7.GY >> 8.GY >> 9.GY: zh2 (nov.18.)
> PL/SQL >> 10.GY >> 11.GY >> 12.GY >> PL/SQL beadandó: dec.13. délig
> Gyak.jegy zárás >> 13.GY: zh3 (dec.16.) >> JavZH (dec.21) >> UV (jan.4)
> Fogadóórák a szorgalmi és a vizsgaidőszakban: Hétfő 10:00-12:00 2.507

Gyakorlati jegy alapja: Összpont = MAX(zh1+zh2; 2*zh2) + zh3, vagyis
akinek nem sikerül a zh1, azoknál a zh2-vel számolunk. A zh3 órai munka is.
   Az zh2 vagy zh3 egyik dolgozat pótolható/javítható: 2016.12.21. szerda dél.
   EA Jav/PótZH időpontja: 2016.12.20. kedd 12:15-13:45-ig 0-804 Lóczy terem
   GYAK Jav/PótZH időpontja: 2016.12.21. szerda 12:15-13:45-ig 2.520 MI labor

A gyakorlat célja: az előadáson szereplő alapfogalmak, algoritmusok, és
az adatbázisok-szemlélet gyakorlatban való elsajátítása. Az a cél, hogy
a hallgatók minél hatékonyabban tudjanak SQL és PL/SQL feladatokat
megoldani úgy, hogy értsék is hogyan történik a lekérdezések kiértékelése.
   >> Gyakorló feladatok: OraclePéldatár.pdf, [SQL Tutorial], [PL/SQL Tutorial]
>> Tananyag [AB1EA.html], +az előadás diasorozata [az AB1ea mappában]
>> Segédletek: hallgato/[jelszóval] [ebben a mappában], [eduPLSQL mappa]

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
1.GY: 2016.09.16. --- SQL előkészítés: create table, insert utasítás
Gépes gyak: Az első előadáshoz kapcsolódóan az Oracle gyakorlatban
> Ismerkedés, Oracle adatbázisok elérése, SQL Developer használata
> SQL DDL: relációséma, adattípusok, megszorítások: create table
> SQL DML: relációelőfordulás, táblák feltöltése adatsorokkal: insert
> Egy táblára vonatkozó lekérdezések az SQL-ben: SELECT utasítás
SELECT, FROM, WHERE és ORDER BY záradékai

   -- TÁBLÁK LÉTREHOZÁSÁRA scriptek:
>> szeret
>> createDolg(hu) vagy createEmpDept(en)

-- FELADATSOR-1:
- Milyen azonosítójú osztályon dolgoznak a dolgozók? (kiterjesztett vetítés)
- Adjuk meg a dolgozók között előforduló foglalkozások neveit (vetítés,
   az eredmény halmaz legyen, vagyis minden foglalkozást csak egyszer írjuk ki!)
- Kik azok a dolgozók, akiknek a fizetése > 2800? (kiválasztás, elemi feltétel)
- Kik azok a dolgozók, akiknek a fizetése 2000 és 4500 között van?
(1.mo: kiválasztás, összetett feltétel; 2.mo: where-ben: intervallum)
- Kik azok a dolgozók, akik a 10-es vagy a 20-as osztályon dolgoznak?
(1.mo: kiválasztás, összetett feltétel; 2.mo: where-ben: in értékek)
- Adjuk meg azon dolgozókat, akik nevének második betűje 'A' (where: like)
- Kik azok a dolgozók, akiknek a jutaléka ismert? (vagyis nem NULL)

-- Oracle Leckék a fenti feladatokhoz (ehhez: createHRsyn)
> SELECT utasítás SELECT, FROM, WHERE és ORDER BY záradékai
   > Oracle Példák
>> 1.lecke - vetítés kiterjesztése, kifejezések
>> 2.lecke - kiválasztás, kiválasztott sorok rendezése

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
2.GY: 2016.09.23. --- SQL egy táblára vonatkozó lekérdezések
Gépes gyak: folyt. az első előadáshoz kapcsolódó Oracle gyakorlat,
az SQL SELECT utasítás SELECT és WHERE záradékaiban kifejezések
és a különböző típusokra vonatkozó sorfüggvények használata.
> Oracle Példák: -- lekérdezésekhez elég szinonimákat venni: createHRsyn
   >> 3.lecke - sorfüggvények használata a SELECT és WHERE záradékban

-- FELADATSOR-2:
- Adjuk meg azon dolgozókat, akik nevében van legalább két 'L' betű.
(Többféle megoldást is keressünk a lekérdezésre, LIKE, INSTR függvény)
- Adjuk meg a dolgozók fizetéseinek négyzetgyökét és 3-mal vett maradékát.
- Adjuk meg, hogy hány hete dolgozik a cégnél ADAMS és milyen hónapban
és milyen nap (hétfő, kedd, stb) lépett be.
- Számoljuk ki, hogy a mai nap hány naposak vagyunk, illetve azt is
hány hónaposak vagyunk (DUAL tábla)
- Adjuk meg a dolgozók nevét, az éves fizetését és hány hete állt munkába!
- Adjuk meg a dolgozók éves jövedelmét, ahol a havi jövedelem a fizetés
és jutalék összege, ahol nincs jutalék megadva,ott a 0 helyettesítő
értékkel számoljunk (NVL függvény alkalmazása).

> Segédlet: Examples/Példák: SQL Language Reference >> 5 Functions:
Sorfüggvények: pl. Karakteres függvények: SUBSTR, RPAD, ..., INSTR, ...
pl. Dátum függvények: MONTHS_BETWEEN, ..., pl. Konverziós: TO_DATE, ...
   pl. Null érték kezelése, helyettesítő érték megadása: NVL, COALESCE, ...
   Figyelem! COALESCE példában: FROM product_information
mivel ez a tábla nem az HR, hanem OE sémában szerepel, ezért
itt ki kell írni a táblatulajdonost: FROM OE.product_information
(ugyanis csak a HR owner tábláihoz hoztuk létre a szinonimákat).

   > Gyakorlás: Oracle Példatár Feladatok.pdf 1.fejezet 1.1.-1.18. feladatok
Megj.: 1.14.feladatban foglalkozás szerint rendezve (nem csoportosítva)
> Házi feladat: befejezni az Oracle Példatár 1.1.-1.18. feladatokat!

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
3.GY: 2016.09.30. --- SQL egy táblás lekérdezések: teljes select (folyt)
Gépes gyak: Oracle gyakorlat, az SQL SELECT utasítás GROUP BY, HAVING
és ORDER BY záradékai, csoportosítás és összesítő függvények használata
> Oracle Példák: -- lekérdezésekhez elég szinonimákat venni: createHRsyn
>> 1/4.lecke - csoportosítás, group by és having záradékok

-- FELADATSOR-3:
- Mennyi a minimális, illetve a maximális fizetés a dolgozók között?
- Mennyi a dolgozók összfizetése?
- Adjuk meg, hogy hány különböző foglalkozás fordul elo a dolgozók között!
- Mennyi a 20-as osztályon az átlagfizetés?
- Adjuk meg osztályonként az átlagfizetést! (OAZON, ATL_FIZ)
- Adjuk meg azokra az osztályokra az átlagfizetést, ahol ez nagyobb mint 2000.
- Adjuk meg azokra az osztályokra az átlagfizetést, ahol legalább hárman dolgoznak!
- Melyek azok az osztályok, ahol legalább hárman dolgoznak? (OAZON, LÉTSZÁM)
- 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.
- Adjuk meg osztályonként az ott dolgozó hivatalnokok (FOGLALKOZAS='CLERK')
átlagfizetését, de csak azokon az osztályokon, ahol legalább két hivatalnok dolgozik!
- Adjuk meg a legmagasabb osztályonkénti átlagfizetést!

   > Gyakorlás: Oracle Példatár Feladatok.pdf 2.fejezet 2.1-2.24 feladatok
> Házi feladat: befejezni az Oracle Példatár 2.1.-2.24. feladatokat!

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
4.GY: 2016.10.07. --- Lekérdezések relációs algebrában és SQL-ben
> Több táblára vonatkozó lekérdezések, összekapcsolások, alkérdések
> Tankönyv feladatai: Termékek-feladatok.pdf, Csatahajók-feladatok.pdf
   > Tankönyv feladataihoz a scriptek: create Termékek, create Csatahajók
> Tk.2.4.14.Feladatok (Tk.54-57.o. 2.4.1.feladat) Termékek feladatai
Lekérdezések kifejezése először (papíron) természetes módon táblákkal,
a táblákon értelmezett műveletekkel gondolkodva, és relációs algebrában
kifejezőfákkal is felrajzolva, majd átírva SQL SELECT utasítással (gépnél)
többféle átírást, megoldási lehetőséget vizsgáljunk meg, vessünk össze!
>>> Megoldás (ellenőrzésre) Termékek-feladatok-megoldása.pdf

-- FELADATSOR-4:
- Kik azok a dolgozók, akik a 10-es vagy a 20-as osztályon dolgoznak?
- Kik azok a dolgozók, akiknek a főnöke KING?
- Kik azok a dolgozók, akik főnökének a főnöke KING?
- Adjuk meg azoknak a főnököknek a nevét, akiknek a foglalkozása nem 'MANAGER'.
- Adjuk meg azokat a dolgozókat, akik többet keresnek a főnöküknél.
- Kik azok a dolgozók, akik osztályának telephelye DALLAS vagy CHICAGO?
- Kik azok a dolgozók, akik osztályának telephelye nem DALLAS és nem CHICAGO?

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
5.GY: 2016.10.14. --- Lekérdezések relációs algebrában és SQL-ben
I.ZH két részből áll:
- gépes feladatok: Lekérdezési feladatok megoldása az SQL SELECT-tel
- papíros feladatok: Lekérdezések relációs algebrában és SQL SELECT-tel

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
6.GY: 2016.10.21. --- Lekérdezések kiterjeszett rel.alg-ban és SQL-ben
> Rel.algebra kiterjesztése, csoportosítás, rendezés, összekapcsolások
-- A 3.GY FELADATSOR-3 feladatait írjuk fel kiterjesztett relációs algebrában!

Gépes gyak: Oracle gyakorlat, az SQL SELECT utasítás GROUP BY, HAVING,
Több táblára vonatkozó lekérdések, többféle megoldás joinnal, alkérdéssel
Összekapcsolások és alkérdések használatával többféle módon oldjuk meg,
de az alkérdéseket most még csak a WHERE és HAVING záradékokban
használjunk és kerüljük a FROM utáni alkérdéseket (ún. inline nézeteket),
a köv.héten nézzük meg a nézettáblák és az inline nézetek használatát!
   > Oracle Példák: -- lekérdezésekhez elég szinonimákat venni: createHRsyn
  >> 1/5.lecke (összekapcsolások, a külső join is)
    >> 1/6.lecke és 2/6.lecke (alkérdések)

   -- FELADATSOR-5:
- Adjuk meg osztályonként a telephelyet és az átlagfizetést (OAZON, TELEPHELY, ATL_FIZ).
- Kik azok a dolgozók, és milyen munkakörben dolgoznak, akiknek a legnagyobb
   a fizetésük?
- Adjuk meg, hogy mely dolgozók fizetése jobb, mint a saját osztályán (azon
az osztályon, ahol dolgozik) dolgozók átlagfizetése!
- Adjuk meg, hogy mely dolgozók átlagjövedelme jobb, mint a saját osztályán
dolgozók átlagjövedelme, ahol a jövedelem a fizetés és jutalék összege, ahol
nincs jutalék megadva, ott a 0 helyettesítő értékkel számoljunk (NVL függvény).
- Adjuk meg, hogy az egyes osztályokon hány ember dolgozik (azt is, ahol 0=senki).
- Adjuk meg osztályonként a dolgozók összfizetését az osztály nevét megjelenítve
ONEV, SUM(FIZETES) formában, és azok az osztályok is jelenjenek meg ahol
nem dolgozik senki, ott az összfizetés 0 legyen. Valamint ha van olyan dolgozó,
akinek nincs megadva, hogy mely osztályon dolgozik, azokat a dolgozókat
egy 'FIKTIV' nevű osztályon gyűjtsük össze. Minden osztályt a nevével plusz
ezt a 'FIKTIV' osztályt is jelenítsük meg az itt dolgozók összfizetésével együtt.

> Gyakorlás: Oracle Példatár Feladatok.pdf 3.fejezet feladatai
> Házi feladat: befejezni az Oracle Példatár 3.1.-3.20. feladatokat!
   Figyelem! Alkérdéseket most még csak a WHERE és HAVING záradékokban
használjunk és kerüljük a FROM utáni alkérdéseket (ún. inline nézeteket),
a köv.héten nézzük meg a nézettáblák és az inline nézetek használatát!
A hierarchikus lekérdezéseket is hagyjuk ki, azt az őszi szünet után vesszük.

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
7.GY: 2016.10.28. --- SELECT Kérdés/Válasz +SQL DML utasítások
-- -- -- --
   (a) Konzultáció: Példatár 3.fejezet feladatai - Oracle Példatár Feladatok.pdf
> További segédletek: hallgato/[jelszóval] [ebben a mappában]
-- -- -- --
   (b) SQL DML (insert, delete, update)
> Oracle Példák: -- DML-utasításokhoz saját táblák létrehozása: createHRtables
>> 1/8.lecke SQL DML-utasítások (insert, update, delete) és a tranzakciók
> Feladatok: 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 a visszagörgetés: ROLLBACK (illetve érvényesítés: COMMIT).

-- FELADATSOR-6:
Előkészítés: Ezekhez a DML feladatokhoz ámenetileg most megszorítások,
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)
   >> Órai gyakorló feladatok .txt-ben is itt: DML_feladatok.txt

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 (lásd FizFokozat 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)
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.

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.

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.

   -- -- -- --
(c) SQL DDL (create table) Táblák és megszorítások létrehozása
> Oracle Példák (Megj.: indexek később: AB2GY-on lesznek)
>> 1/9.lecke SQL DDL (táblák és megszorítások létrehozása)
>> 2/1.lecke SQL DDL (innen: csak a megszorítások létrehozása kell)
> További segédletek: create table, típusok, megszorítások (.txt)
> Feladatok: Táblák és megszorítások (constraints) létrehozása.

-- FELADATSOR-7:
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).
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?
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).

Még egy példa nézettáblákra:
-- 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.

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

   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)

-- Ugyanez WITH-záradékkal
>> 2/6.lecke (WITH záradék -- alkérdések után 16-18.o.)

   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)

   -- -- -- --
   > Gyakorlás: Oracle Példatár Feladatok.pdf 5-6.fejezet feladatai
> Házi feladat: befejezni az Oracle Példatár 5-6.fejezet feladatait

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
8.GY: 2016.11.11. --- Lekérdezések Datalogban, Rekurzió, WITH záradék
-- -- -- --
> II.ZH előtti összefoglalás, konzultáció, kérdés/válasz
> 1-7.előadások anyaga: Tankönyv 1-2., 5-7. és 10.2 fejezetei konzultáció
> Oracle Példatár 1-3. és 5.fejezet feladataiból konzultáció: kérdés/válasz
-- -- -- --
   (a) Tankönyv feladatai: Termékek-feladatok.pdf, Csatahajók-feladatok.pdf
   A korábbi relációs algebrai kifejezőfákat írjuk át Datalog szabályokra is
  >> Szeret tábla és feladatok, ehhez a tábla létrehozása: create_szeret
-- -- -- --
(b) Rekurzió. Az Eljut feladat --- Ehhez: create_jaratok_tabla.txt
> Tk.10.2. Az Eljut-feladat. Rekurzió az SQL-ben: WITH RECURSIVE
> Oracle Példák (gépes kipróbálásra, papíros zárthelyin nem szerepel)
>> 2/6.lecke (WITH záradék -- alkérdések után 16-18.o.)
>> 2/7.lecke Hierarchikus lekérdezések -- hiera_lekerd.txt
> További példák az Oracle SQL Lang.Ref.11.2.pdf dokumentációban:
-- Hierarchikus lekérdezések: Chapter 9 - SQL Queries and Subqueries
innen Hierarchical Queries 9-3 és Hierarchical Query Examples 9-5
-- Rekurzió with-utasítással: itt 19-36 Subquery Factoring: Example

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
9.GY: 2016.11.18. --- Több táblára vonatkozó lekérdezések, és rekurzió
> II.ZH témaköre:
-- -- --
> 1-7.előadások anyaga:
Tankönyv 1.fej. Alapok, 2.4. Rel.algebra, 5.1-5.2. Kiterj.rel.algebra,
   6.1-6.4. SQL SELECT, 6.5. SQL DML, 7.1-7.5. és 8.1-8.2. SQL DDL
[5.3-]5.4. Datalog, innen csak ez: Rel.alg.kifejezést átírni Datalogba
10.2. Rekurzió a Datalogban és az SQL-1999-es szabványban
[Megj.: 7.EA anyagát a PL/SQL-t a 9-12. gyakorlatokon vesszük]
-- -- --
> 1-8.gyakorlatok anyaga:
Több táblára vonatkozó lekérdezések
>> Oracle Példatár 1-3. fejezeteinek feladatai
SQL DML és DDL utasításai
    >> Oracle Példatár 5-6. fejezeteinek feladatai

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
10.GY: 2016.11.25. --- PL/SQL: blokk, deklaráció, utasítások, select into
   > Oracle PL/SQL: Oracle Junior EA >>> Oracle PL/SQL.pdf -->> 1-16.o.
   > SQL/PSM --- Tk. 9.3-9.4.fejezetei alapján >>> 8.előadás.pdf
      -- Tk.9.3. SQL/PSM: Osztott változók, lekérdezések és kurzorok
   -- Tk.9.4. Tárolt eljárások és függvények, kivételkezelés
   > Oracle PL/SQL segédanyagok: ebben az eduPLSQL mappában
   -- Tankönyvtár: PL/SQL programozás Oracle 10g-ben (Gábor A.-Juhász I.)
   > Oracle PL/SQL angol nyelvű tutorial: PL/SQL Tutorial
   > Oracle PL/SQL doksi: Oracle 11gR2 doc PL/SQL Language Reference
> Oracle Példák: -- lekérdezésekhez elég szinonimákat venni: createHRsyn

-- -- -- --
1/7.rész: PL/SQL alapjai, PL/SQL blokk szerkezete >> 2.fej. Alapok
[1_deklarációs_rész] 2_végrehajtható_rész [3_kivételkezelő_rész]
SET SERVEROUTPUT ON;
>> példa-2-24 deklarációk-és-értékadás
>> példa-2-25 select-into (itt a 2-25 példában blokk: begin ... end; /
át kell tenni az end;-et a végére!) SELECT INTO-t akkor használjuk,
  ha a lekérdezés pontosan egy sort ad, ha a lekérdezés több sorral
tér vissza, akkor kurzort kell használni, lásd köv.gyakorlat anyagát).
>> példa6-1 DML utasítások a programban, implicit kurzor
>> példa-6-4 implicit kurzor attribútumok

> ÖNÁLLÓAN MEGOLDANDÓ BEADANDÓ PL/SQL--1 feladatok:
-- A feladatokat most is a saját Dolgozo-Osztaly táblákra kell megírni, ehhez
a táblákat létrehozó script, mint a DML-hez volt: createDolg (no constraint)
-- A PL/SQL blokk előtt minden alkalommal állítsuk be: SET SERVEROUTPUT ON
1.) Az első feladat: Írjuk ki PL/SQL blokkból: 'Szia Világ!', majd egészítsük ki,
   kérjen be egy nevet, számot, dátumot és ezeket is írassuk ki a programból!
   2.) Írjuk ki KING fizetését (olvasás táblából változóba), abban az esetben,
ha ismert, hogy pontosan egy KING nevű dolgozó szerepel a táblában,
lásd példa-2-25 select-into (csak ha a lekérdezés pontosan egy sort ad).
3.) Adjuk meg egy főnök azonosítóját, és töröljük a közvetlen beosztottjait, és
írassuk ki hány beosztottja volt, lásd példa-6-4 implicit kurzor attribútumok.

-- -- -- --
2/7.rész: Vezérlési szerkezetek a PL/SQL-ben 4.fej. Control Statements
PL/SQL alapjaihoz kiegészítés: Alprogramok (tárolt eljárások/függvények)
>> példa2-19 Alprogramok (már itt is vannak: 2.fej. Alapok)
>> példa4-1 Alprogramok IF-THEN utasítás példában (4.fej. Utasítások)
Vezérlési szerkezetek: Feltételes utasítások, ciklusok
>> példa-4-5 if-then-elsif utasítás,
>> példa-4-6 egyszerű case utasítás, és itt írjuk át grade := 'B' -> '&B'
helyettesítési változóra, aminek a felhasználó adja meg az értékét!
>> példa-4-10 alap LOOP ciklus utasítás EXIT WHEN kilépés a ciklusból
>> példa 4-14 FOR ciklus utasítás
>> példa 4-27 WHILE ciklus utasítás
-- -- -- --
>> Oracle PL/SQL.pdf (és összefoglalók) plsql_01_bevezetes.pdf,
   02_vezerlo_utasitasok.txt, 02_tipusok.pdf, 03_dml.pdf, 05_valtozok.pdf
--- Ez volt az előkészítés az Eljut feladat megoldásához, lásd köv.3/6.részt.

> Gyakorló feladatok az Oracle Példatárból
   > Feladatok: Oracle Példatár Feladatok.pdf 8.fejezet 8.1-8.9.feladatok
> HF: Oracle Példatár 8.fejezet feladatai: Változóhasználat, utasítások PL/SQL-ben

-- -- -- --
    3/7.rész: Az "Eljut feladat" megvalósítása PL/SQL-ben
   >> Motiváció: Tk.10.2. Az Eljut-feladat SQL/PSM-ben (PL/SQL-ben)
>> Az ehhez szükséges előkészületeket lásd PL/SQL 1/7 és 2/7 részben
(ismétlés) >> példa6-1 DML utasítások a programban, implicit kurzor

    -- Az Eljut-feladat a Tankönyv (Ullman-Widom kék könyv) 10.2 szakaszára épül
    -- 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/több átszállással).
    -- Ezzel a scripttel jaratok_tabla.txt készítsünk saját táblát, ami alapján dolgozunk.

> Rek1.feladat: Mely (x, y) várospárokra lehetséges egy vagy több átszállással
eljutni x városból y városba? -- Ehhez készítsünk egy Eljut(honnan, hova) táblát,
a sorait a járatok tábla alapján PL/SQL programmal töltsük fel (ciklust szervezni,
  az insert 2.alakja: több sor felvitele alkérdéssel/járatok és eljut táblák alapján).
>>> (csak ha kell, egy kis segítség, további ötletek és a megoldás vázlata: itt)

> Rek2.feladat: Mely (x,y) város párokra hány átszállással és milyen költségekkel
lehetséges egy vagy több átszállással eljutni x városból y városba? -- Ehhez is
készítsünk Eljut2(honnan, hova, atszallas, koltseg) táblát, a sorait programmal.
>> Papíron megoldandó feladat: Fejezzük ki az SQL-1999-es szabvány SELECT
WITH RECURSIVE utasítással, hogy mely mely városokba (hova) tudunk eljutni
'DAL' (Dallas)-ból legfeljebb 3 átszállással és legfeljebb 5000 költségből.
>>> (csak ha kell, egy kis segítség WITH RECURSIVE papíros részéhez itt)

> Rek3.feladat: Tegyük fel, hogy nemcsak az érdekel, hogy el tudunk-e jutni az
egyik városból a másikba, hanem az is, hogy utazásunk során az átszállások is
ésszerűek legyenek, ez azt jelenti, hogy ha több járattal utazunk, akkor nézni
kell átszálláskor az érkező járatnak legalább egy órával a rákövetkező indulás
előtt meg kell érkeznie. (Tegyük fel, hogy nincs egy napnál hosszabb utazás!)

> Rek4.feladat: A fenti feladatokat oldjuk meg PL/SQL-ben úgy is, hogy ne csak
a várospárokat, hanem a teljes útvonalat is listázzuk ki.

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
11.GY: 2016.12.02. --- PL/SQL: lekérdezések, kurzorok használata
   > Oracle PL/SQL: Oracle Junior EA >>> Oracle PL/SQL.pdf -->> 17-21.o.
   > SQL/PSM --- Tk. 9.3-9.4.fejezetei alapján >>> 8.előadás.pdf
      -- Tk.9.3. SQL/PSM: Osztott változók, lekérdezések és kurzorok
   -- Tk.9.4. Tárolt eljárások és függvények, kivételkezelés
   > Oracle PL/SQL segédanyagok: ebben az eduPLSQL mappában
   -- Tankönyvtár: PL/SQL programozás Oracle 10g-ben (Gábor A.-Juhász I.)
   > Oracle PL/SQL angol nyelvű tutorial: PL/SQL Tutorial
   > Oracle PL/SQL doksi: Oracle 11gR2 doc PL/SQL Language Reference
> Oracle PL/SQL, ehhez Oracle 11gR2 doc PL/SQL Language Reference
-- Az Oracle doksikban szereplő DML példák kipróbálásához: createHRtables

-- -- -- --
    4/7.rész:  Kurzorok deklarálása és használata >> 6.fej. Static SQL/Cursors
>> példa-6-7 explicit kurzorok deklarálása és használata, lásd 5.feladat,
>> példa-6-11-től 6-14 explicit kurzor attribútumok, lásd 6.feladat,
    >> példa-6-17 paraméteres kurzorok, lásd 7-8.feladat,
   >> példa-6-43 for update kurzor módosításhoz, lásd 9-10.feladat.
>> (összefoglaló) (volt:03_sql_dml.pdf), 13_kurzorok.pdf, pl03_cursor.txt

> ÖNÁLLÓAN MEGOLDANDÓ BEADANDÓ PL/SQL--2 feladatok:
-- A feladatokat most is a saját Dolgozo-Osztaly táblákra kell megírni, ehhez
a táblákat létrehozó script, mint a DML-hez volt: createDolg (no constraint)
-- A PL/SQL blokk előtt minden alkalommal állítsuk be: SET SERVEROUTPUT ON
     1-3.) Folytatás, lásd az előző heti gyakorlaton az első PL/SQL feladatokat!
4.) Lásd az Eljut-feladat megvalósítását PL/SQL programmal: Rek1, Rek2.
5.) Írjuk ki a dolgozók nevét és fizetését!
-- több sort visszaadó lekérdezés, kurzor használata, lásd  példa-6-7
6.) Írjuk ki a 3. 5. és 8. legnagyobb fizetésű dolgozó nevét, fizetését!
-- kurzor attribútumok, lásd példa-6-14
7.) Írjuk ki azon dolgozók nevét és fizetését, akik fizetése nagyobb mint
egy olyan szám, amelyet a felhasználó fog majd futás közben megadni!
-- felhasználói/helyettesítő változók: &numerikus_valt, '&karakteres_valt'
8.) Írjuk ki azon dolgozók nevét, fizetését és osztálykódját, akik a felhasználó
által megadott osztályon dolgoznak! A felhasználó által megadott betű
legyen A, R, S (Accounting ...) a 10, 20, 30-as osztály esetén.
    -- paraméteres kurzor, lásd példa-6-17
9.) Növeljük meg a hivatalnokok (CLERK) fizetését a saját fizetésük 20%-ával!
-- for update kurzor módosításhoz, lásd ugyanez a feladat: példa-6-43
   10.) Módosítsuk a dolgozók nevét írjuk át, hogy csak a kezdőbetű legyen nagy,
a többi betű kicsi, továbbá a dolgozók fizetését is növeljük meg 2 %-kal!
-- for update kurzor módosításhoz, lásd példa-6-43

> Gyakorló feladatok az Oracle Példatárból
   > Feladatok: Oracle Példatár Feladatok.pdf 9.fejezet 9.1-9.14.feladatok
> HF: Oracle Példatár 9.fejezet feladatai: Lekérdezések, kurzorok a PL/SQL-ben

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
12.GY: 2016.12.09. --- PL/SQL: Kivételkezelés, tárolt eljárások/függvények
    5/7.rész:  PL/SQL kivétel- és hibakezelés >> 11.fej. PL/SQL Error Handling
   >> példa-11-5 és tábla-11-2 előre definiált kivételek
>> példa-11-8 felhasználó által definiált kivételek
>> (összefoglaló) plsql_07_kivetelek.pdf, Kivételekre_példa
    6/7.rész:  PL/SQL eljárások és függvények >> 8.fej. PL/SQL Subprograms
>> példa2-19 Alprogramok (már itt is volt függvény: 2.fej. Alapok)
>> példa4-1 IF-THEN utasítás (már itt is volt eljárás: 4.fej. Utasítások)
>> (összefoglaló) plsql_06_alprogramok.pdf, Alprogramokra_példa
>> (elemi programozási feladatok alprogramokra) feladatok.txt

> ÖNÁLLÓAN MEGOLDANDÓ BEADANDÓ PL/SQL--3 feladatok:
> PL/SQL--3.feladatok: --- ehhez, mint a DML-hez volt: createDolg (no constraint)
-- A feladatokat most is a saját Dolgozo-Osztaly táblákra kell megírni, ehhez
a táblákat létrehozó script, mint a DML-hez volt: createDolg (no constraint)
-- A PL/SQL blokk előtt minden alkalommal állítsuk be: SET SERVEROUTPUT ON
     1-10.) Lásd az előző heti gyakorlaton az első PL/SQL feladatokat: ezeket most
    írjuk át eljárásokra/függvényekre, plusz legyen benne hiba- és kivételkezelés!
2.) Írjuk ki KING fizetését (olvasás táblából változóba és a képernyőre való kiíratás)
  abban az esetben,  ha pontosan egy KING nevű dolgozó szerepel a táblában,
viszont ha nincs ilyen nevű dolgozó vagy több ilyen is van, akkor azt írjuk ki!
-- Előre definiált kivételek: NO_DATA_FOUND, TOO_MANY_ROWS, lásd példa-11-5
(...) -- Előre definiált és felhasználói kivételek. Alprogramok (függvények, eljárások)
   11.) a.) Írjunk meg egy függvényt, ami az azonosító alapján visszaadja a nevet!
      b.) Írjunk meg egy eljárást, ami az azonosító alapján egy OUT változóban
      visszaadja a nevet! (alprogramok: függvények és eljárások/procedúrák)
   12.) Írjunk meg egy függvényt, ami visszaadja a paraméterében levő magánhangzók
     számát. Majd módosítsuk a fizetéseket ennek a függvénynek a segítségével.
   (itt most a módosítást az UPDATE utasítással végezzük, és nem plsql-ben)

> Gyakorló feladatok az Oracle Példatárból
   > Feladatok: Oracle Példatár Feladatok.pdf 10.fejezet feladatai
> HF: befejezni az Oracle Példatár 10.fejezet feladatait! Köv.héten PL/SQL ZH!

> ÖNÁLLÓAN MEGOLDANDÓ BEADANDÓ PL/SQL feladatok
Beküldése e-mailben, egyszerűen a levél szövegében küldjék el (ne fájlban)
Az 1-12. PL/SQL feladat beküldési határideje: 2016. december 13. (kedd) délig

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
13.GY: 2016.12.16. --- III.ZH PL/SQL
> III.ZH témaköre: Oracle PL/SQL
PL/SQL gépes dolgozat, amibe a 10-12.gyakorlatok órai munkája és
a kötelező beadandó feladatai is beszámítanak a zh3 pontjaiba.
Az 1-12. PL/SQL feladat beküldési határideje: 2016. december 13. (kedd) délig

« | 1G | 2G | 3G | 4G | 5G | 6G | 7G | 8G | 9G | 10G | 11G | 12G | 13G | #
JavZH: 2016.12.21. szerda 12:15-13:45; +GyakUV: 2017.01.04. (Neptun)
> Akinek nem sikerül a zh1, azoknál a zh2-vel számolunk duplán zh1 helyett.
> A zh2 vagy zh3 egyik dolgozat (és csak az egyik) pótolható/javítható:
Jav/PótZH: 2016.12.21. szerda 12:15-13:45-ig 2.520 Mest.Int.labor

> Akinek sem a zh2 sem a zh3 nem sikerült, azok elégtelen gyakorlati jegyet
kapnak (decemberben) és a januári gyakUV-val tudnak gyakorlati jegyet
szerezni, a gyakorlati utóvizsgára a Neptunban tudnak feljelentkezni:
GyakJegyUV: 2017.01.04. szerda 12:15-13:45-ig 2.520 Mest.Int.labor