7.GYAKORLAT (ADATBÁZISOK)
Témakör: Oracle
gépes gyakorlat.
I. SQL DML, táblák
tartalmának
módosítása: insert, delete,
update, illetve
tranzakció-kezelés
utasítások: commit, rollback,
II. 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
III. SQL SELECT: WITH munkatáblák használatáról
> Oracle
Példák: DML-hez saját
táblaként hozzuk
létre: createHRtables
(új!)
> SQL segédanyagok: SQL09_DML.pdf; SQL10_DDL.pdf; SQL11_view.pdf
I.rész: SQL DML
(insert, delete, update)
FELADATSOR-A/6.rész: dolgozo,
osztaly (DML: DELETE, INSERT és UPDATE)
Előkészítés:
Ezekhez a DML feladatokhoz ámenetileg most
megszorítások
(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!)
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.
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.
II.rész: SQL DDL.
Táblák és
megszorítások,
nézettáblák
FELADATSOR-A/7.rész: dolgozo,
osztaly (DDL: táblák,
nézettáblák)
> További segédletek:
create
table, típusok, megszorítások
(.txt)
>
Feladatok: 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).
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).
III.rész: 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 használatára példák,
lásd Subquery
Factoring: Examples
[Forrás: Oracle Database SQL Language Reference -- html illetve pdf]
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)
>> Köv.gyak. II.ZH
>>
HF: Önálló gyakorlás - Oracle
Példatár Feladatok.pdf
2-3.fejezet (SQL SELECT) és
5-6.fejezet (SQL DDL, DML)