Táblák tartalmának módosítása; Táblák létrehozása, integritási megszorítások
>> I.RÉSZ - SQL DML: TÁBLÁK TARTALMÁNAK MÓDOSÍTÁSA, TRANZAKCIÓK
>> II.RÉSZ - SQL DDL: TÁBLÁK LÉTREHOZÁSA, INTEGRITÁSI MEGSZORÍTÁSOK
I.RÉSZ SQL DML utasítások [DELETE, UPDATE, INSERT]; Tranzakciók
Témakör: SQL DML, táblák tartalmának módosítása: delete, update, insert
+ehhez szükséges a tranzakció-kezelés: commit, rollback, savepoint
>> Lásd EA+ Ullman-Widom Tankönyv 6.5.-6.6. szakasz [ 6.ea.pdf /1rész ]
>> [ kiegészítés: Oracle segédanyagok: SQL09_DML.pdf ]
FELADATSOR: dolgozo, osztaly (SQL 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.
-- Emlékeztető: Dolgozo, Osztaly, Fiz_Kategoria: createDolg+Constraints
A módosítást egy másodpéldányon (oszt2, dolg2, fizkat2) végezzük, hogy
a tábla eredeti tartalma megmaradjon és a megszorítások se zavarjanak.
DROP TABLE fizkat2;
DROP TABLE dolg2;
DROP TABLE oszt2;
CREATE TABLE oszt2 AS SELECT * FROM Osztaly;
CREATE TABLE dolg2 AS SELECT * FROM Dolgozo;
CREATE TABLE fizkat2 AS SELECT * FROM Fiz_Kategoria;
-- Az SQL DML feladatok után kérdezzük le a módosított tábla tartalmát,
-- majd állítsuk vissza a tábla eredeti tartalmát a ROLLBACK utasítással!
ROLLBACK; -- visszaállítjuk a tábla eredeti tartalmát (a további feladatokhoz)
COMMIT; -- ezzel véglegesítjük a táblák tartalmát (befejezzük a tranzakciót)
-- -- -- -- --
DELETE - sorok törlése
DELETE [FROM ] tábla
[WHERE feltétel ]
1. Töröljük azokat a dolgozókat (a dolg2 táblából), 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 azokat a dolgozókat, akiknek a fizetése kisebb, mint a saját
osztályuk átlagfizetése.
6. Töröljük a legjobban kereső dolgozót.
7. Töröljük ki azokat az osztályokat (az oszt2 táblából), ahol dolgozik valaki,
akinek a fizetése a Fiz_Kategoria tábla szerint a 2-es kategóriába esik.
8. Töröljük ki azon osztályokat, amelyeknek 2 olyan dolgozója van, akinek
a fizetése a 2-es fizetési kategóriába esik a Fiz_Kategoria tábla alapján.
-- -- -- -- --
UPDATE - tábla tartalmának módosítása
UPDATE tábla
SET oszlop = érték [, oszlop = érték, ... ]
[WHERE feltétel ]
1. Növeljük meg a 20-as osztályon a dolgozók fizetését 20%-kal.
2. Növeljük meg azok fizetését 500-zal, akik jutaléka NULL vagy
a fizetésük kisebb az átlagnál.
3. Növeljük meg mindenkinek a jutalékát a jelenlegi maximális jutalékkal,
ha valakinek ismeretlen a jutaléka (NULL) tekintsük 0-nak.
4. Módosítsuk 'Loser'-re a legrosszabbul kereső dolgozó nevét.
5. 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.
6. Növeljük meg azoknak a dolgozóknak a fizetését a jelenlegi minimális fizetés
5%-kával, akiknek van olyan beosztottja, aki minimális fizetéssel rendelkezik.
7. Növeljük meg a nem főnökök fizetését a saját osztályuk átlagfizetésével.
8. Összetett, több lépéses feladat a Kende-Nagy feladatgyűjteményből (ott 5.5/c)
Bővítsük a dolg2 táblát egy lakhely oszloppal, az új oszlop létrehozása:
ALTER TABLE dolg2 ADD lakhely VARCHAR2(30);
majd módosítsuk a táblát töltsük fel a lakcím oszlopát a következőképpen:
A BOSTON-ban dolgozók CHICAGO-ban, a CHICAGO-ban dolgozók pedig
BOSTON-ban laknak, kivéve azokat a CHICAGO-i dolgozókat, akiknek
BLAKE a főnöke, mert ők INDIANAPOLIS-ban laknak, feltéve, hogy
nem CLERK foglalkozásúak, mert akkor sehol sem laknak (NULL).
-- -- -- -- --
INSERT -- 1.alakja - egy új sor felvitele értékek megadásával
INSERT INTO tábla [(oszlop [, oszlop... ])]
VALUES (érték [, érték... ]);
1. 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.
2. Példa a helyettesítő/felhasználói változókra: &Numerikus/'&Karakteres'
az insert utasításban, itt a felhasználónak kell begépelnie az értékeket:
INSERT INTO dolg2 (dkod, dnev, oazon)
VALUES (&DOLGKOD, upper('&DOLGNEV'), &OSZTAZON);
-- -- -- -- --
INSERT -- 2.alakja - több sor felvitele alkérdéssel
INSERT INTO tábla [(oszlop [, oszlop... ])]
(SFW alkérdés);
3. 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ű új oszlopa.
DROP TABLE UjOsztaly;
CREATE TABLE UjOsztaly
AS SELECT o.*, 0 letszam
FROM Osztaly o
WHERE 1=2;
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 nem ismert az osztálya,
azok létszámát egy olyan sorba írjuk be, amelyben az adatok
oazon=0, onev= 'FIKTIV' és telephely='ISMERETLEN' legyenek.
4. Több sor felvitele: Hozzunk létre egy UjDolg nevű táblát, amelynek
attribútumai megegyeznek az Dolgozo tábla oszlopaival, plusz van
még egy numerikus típusú Nyeremény nevű attribútuma.
Ebbe az UjDolg táblába az insert utasítás 2. alakjával (alkérdéssel )
vigyünk fel új sorokat a dolgozo táblák sorait egészítsük ki véletlenszám
generátor függvénnyel adjuk meg a nyereményt, amely 1000 és 3000
közötti 100-ra kerekített érték legyen! Segítség:
- Ujdolg tábla létrehozása hasonlóan, mint az előző UjOsztaly tábla volt.
INSERT INTO UjDolg (dkod, dnev, oazon, fonoke, fizetes, nyeremeny)
(SELECT dkod, dnev, oazon, fonoke, fizetes,
round(dbms_random.value(1000, 3000),-2) nyeremeny
FROM Dolgozo d);
-- -- -- -- --
>> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 5.fejezet DML feladatai
II.RÉSZ Táblák és integritási megszorítások: [CREATE | ALTER | DROP ] TABLE
>> SQL DDL, relációs séma megadása create table-példák.txt
- create table táblanév (oszlopnév típus, stb) milyen adattípusokat ismerünk?
- Az Oracle fő adattípusai: SQL Lang.Ref. Data Types (varchar2, number, date)
>> Integritási megszorítások alter_table-megszorítás-példák.txt
- Oracle kulcsok, hivatkozási épség megadása: SQL Lang.Ref, Constraint
>> Lásd EA+ Ullman-Widom Tankönyv 2.3. és 7.1.-7.3.szakasz [ 6.ea.pdf/2rész ]
>> [ kiegészítés: Oracle segédanyagok: SQL10_DDL.pdf ]
-- -- -- -- --
1.) Dolgozo, Osztaly, Fiz_Kategoria táblák, példa: createDolg+Constraints
E/K diagramja: Egyedhz: Dolgozó, Osztály; Kapcsolatok: sok-egy kapcsolatok
Dolgozo (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
Osztaly (oazon, onev, telephely)
Készítsünk a fenti táblákhoz (megszorítások nélküli) másolatokat:
DROP TABLE dolg1;
DROP TABLE oszt1;
CREATE TABLE oszt1 AS SELECT * FROM Osztaly;
CREATE TABLE dolg1 AS SELECT * FROM Dolgozo;
majd az új táblákat lássa el az alábbi megszorításokkal:
- Legyen a dkod elsődleges kulcs a dolg1 táblában, és legyen az oazon
elsődleges kulcs a oszt1 táblában és idegen kulcs a dolg1 táblában és
a hivatkozási épséget itt úgy állítsa be, hogy egy osztály törlése esetén
ennek az osztálynak a dolgozóit is törölje a dolg1 táblából!
- Egy új dolgozó fizetése nem maradhat hiányzó érték, és csak az adott
700 és 7000 USD értéktartománybeli fizetést kaphasson!
- 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).
-- -- -- -- --
2.) Készítsünk el SQL-ben két táblát: sportcsapatok és játékosok.
- Az egyik a sportcsapatok: csapatnev, varos, tagdij oszlopokkal,
- ebben a táblában legyen összetett kulcs (csapatnev, varos),
- és ha a varos nem 'Budapest', akkor a tagdíj <= 5000 legyen.
- A másik a játékosok táblának az attribútumai legyenek:
nev, mezszam, szuldatum, szulvaros, csapatnev, varos
és itt is legyen összetett kulcs (mezszam, csapatnev, varos),
- a játékosok táblában a (csapatnev, varos) legyen idegen kulcs,
vagyis itt hivatkozunk a sportcsapatok tábla elsődleges kulcsára,
- a nev oszlopot kötelező kitölteni és egyedinek kell lennie,
- és a szulvaros mezőben csak olyan várost lehet megadni,
ami szerepel a sportcsapatok tábla varos oszlopában.
-- -- -- -- --
3.) Modellezési feladatok [ E/K emlékeztető: INFR_EK_feladatok.pdf ]
Rajzoljunk fel a táblán/papíron E/K diagramokat, majd alakítsuk át
rel.sémákra és a szükséges megszorításokra, készítsük el a táblákat!
>> Példák E/K diagramra és relációs sémákra való átalakításukra:
- egy áruház dolgozói, vevői és beszállítói: EK_pelda_A.pdf
- egy orvosi rendelő orvosai és páciensei: EK_pelda_B.pdf
-- -- -- -- --
>> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 5.fejezet DDL feladatai