Témakörök: TÁBLÁK, NÉZETTÁBLÁK, MUNKATÁBLÁK
(a) SQL DDL: táblák+megszorítások: create table+constraints
(b) SQL DDL: nézettáblák használata: create or replace view
(c) SELECT: with munkatáblák használata összetett lekérdezéshez
(a) 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
(b) Nézettáblák készítése/használata: [CREATE OR REPLACE | DROP ] VIEW
4.) Egyszerű/összetett nézettáblák létrehozása, melyik módosítható/melyik nem?
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?
- Oracle SQL CREATE VIEW: SQL Lang.Ref, CREATE VIEW
- [Kieg. SQL11_view.pdf]
-- -- -- -- --
5.) Például: Bonyolultabb lekérdezés megoldása nézettáblák segítségével
- lásd SQL08_alkerdes2.pdf (16-18.o. példa, uez a with-re példa: SQL Lang.Ref.)
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);
-- -- -- -- --
>> Önálló gyakorlásra: Oracle Példatár Feladatok.pdf 6.fejezet feladatai
(c) WITH munkatáblák használata az SQL SELECT lekérdezésekben
WITH utasítás: lásd a fenti példát nézettáblákra, ugyanez WITH-záradékkal,
>> Oracle: SQL Language Reference >> innen: Subquery Factoring: Examples
6.) Példák WITH munkatá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.
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);
-- -- -- -- --
Visszatérve: Korábbi összetettebb lekérdezéseket nézzük meg with-tel is
>> Gyakorlat példái: createSzeret.txt séma: Szeret (nev, gyumolcs)
7.) Kik szeretnek minden gyümölcsöt?
8.) Kik azok, akik legalább azokat a gyümölcsöket szeretik, mint Micimackó?
9.) Kik azok, akik legfeljebb azokat a gyümölcsöket szeretik, mint Micimackó?