Adatbázis_I

7.gyak. SQL DDL, DML áttekintése, nézetek, inline nézetek

-- gépes gyakorlat: Oracle: SQL teljes áttekintés
> 2.1. Relációsémák és megszorítások definiálása SQL-ben
> 2.2. DML: insert, delete, update
> 2.3. Feladatok nézetekre, inline nézetre és WITH utasításra
> 2.4. Felső-N elemzés ROWNUM segítségével

Feladatok: SQL DDL create table, create view valamint SQL DML utasításai

ORACLE SQL ÁTTEKINTÉSE:
SQL DDL (CREATE TABLE, CREATE VIEW)
-- Táblák és megszorítások (constraints) létrehozása.
-- Egyszerű és összetett nézettáblák létrehozása.
SQL tranzakciókezelés
-- tranzakciókezelés alapjai, érvényesítés: COMMIT,
és visszagörgetés: ROLLBACK
és mentési pontok: SAVEPOINT
SQL DML (INSERT, DELETE, UPDATE)
-- 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 (visszaállítjuk a táblák eredeti állapotát).
SQL lekérdezések: Oracle specialitások
-- Felső-N elemzés ROWNUM segítségével
-- Hierarchikus lekérdezések CONNECT BY PRIOR
Ennyi! -- Jön: Oracle PL/SQL (8-11. gyakorlatokon)

2.1. Relációsémák és megszorítások definiálása SQL-ben

CREATE TABLE - Relációsémák és megszorítások megadása

Példák és feladatok:
>> Dolgozo és Osztaly táblák (megszorításokkal való) létrehozása
>> SQL_21_Tablak.pdf példái
>> Táblák létrehozása, Oracle típusok
>> Megszorítások példák

További gyakorló feladatok: lásd Példatár "5.fejezet. Adattáblák létrehozása,
módosítása, tranzakciók, megszorítások" DML- és DDL- utasításokra feladatok

2.2. Adattáblák tartalmának módosítása (DML)

Feladatok: DML_feladatok.txt (Nikovits Tibor)
Előkészítés: lásd 2.1. Dolgozo és Osztaly táblák létrehozása

DELETE
-- Töröljük azokat a dolgozókat, akiknek jutaléka NULL.
-- Töröljük azokat a dolgozókat, akiknek a belépési dátuma 1982 előtti.
-- Töröljük azokat a dolgozókat, akik osztályának telephelye DALLAS.
-- Töröljük azokat a dolgozókat, akiknek a fizetése kisebb, mint az átlagfizetés.
-- Töröljük a jelenleg legjobban kereső dolgozót.
-- 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 még SALGRADE 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)
-- 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
- 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.

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

További gyakorló feladatok: lásd Példatár "5.fejezet. Adattáblák létrehozása,
módosítása, tranzakciók, megszorítások" DML-utasításokra feladatok

2.3. Nézetek, inline nézet, WITH utasítás az Oracle-ben (ez nem a szabvány!)

CREATE VIEW - Egyszerű és összetett nézettáblák létrehozása és használata.
Kipróbálni. Mikor lehetséges az alaptáblák karbantartása a nézeteken keresztül?
További részletek, lásd SQL Language Reference -> Nézettáblák létrehozása

Feladatok: Nezet_feladatok.txt (Nikovits Tibor)

1.feladat:
A lekérdezésekhez NIKOVITS felhasználó tulajdonában levő
táblákat használjuk! Elérése: tulajdonos.táblanév

NIKOVITS.CIKK (ckod, cnev, szin, suly)
NIKOVITS.PROJEKT (pkod, pnev, helyszin)
NIKOVITS.SZALLITO (szkod, sznev, statusz, telephely)
NIKOVITS.SZALLIT (szkod, ckod, pkod, mennyiseg, datum)

-- Melyik cikket szállítják a legtöbb projekthez?

A lekérdezést adjuk meg nézettáblák létrehozásával valamint úgy is, hogy
ne kelljen nézetet létrehozni, a "nézet" csak a lekérdezés idejére jön létre:
adjuk meg a lekérdezést INLINE nézettel, majd ugyanez WITH-del is.

Figyelem! Ez az Oracle WITH utasítás nem ugyanaz, mint az előadáson
és a kék-Tk 10.2 fejezetében szereplő WITH RECURSIVE utasítás!
Rekurzió az SQL-ben feladatokra a 10.gyakorlaton nézzünk példákat!

2.feladat:
DOLGOZO (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
OSZTALY (oazon, onev, telephely)

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.

További gyakorló feladatok: lásd Példatár "6.fejezet. Nézettáblák" feladatai
valamint "3.fejezet. Allekérdezések a FROM utasításrészben" feladatai illetve
összetett feladatai és a "7.fejezet. Összetett SQL-feladatsorok" feladatai

2.4. Felső-N elemzés ROWNUM segítségével ("toplista" készítése)

Felső-N elemzés (például a ZH-n is hasznos, amikor túl nagy az eredménytábla
és csak az első 5 sorát kell beküldeni). a felső 5 sor kiíratása az alábbi módon:

  SELECT * FROM
(SELECT... FROM ...WHERE... ORDER BY <oszlop> DESC)
   WHERE ROWNUM <= 5

További részletek, lásd Oracle doksiban SQL Language Reference
2 Pseudocolumns fejezetben (44/1508) ROWNUM Pseudocolumn

ROWNUM - pszeudooszlop a kiválasztásra kerülő sorokhoz hozzárendeli
a kiválasztásuk sorszámát, de a WHERE feltételben nem állhat fent egyenlőség
(ez nem sorszám, hanem a lekérdezés eredményének a sorrendjét adja meg).
WHERE feltételben ROWNUM < N illetve ROWNUM <= N használható!
Hibás! WHERE feltételben ROWNUM > N illetve ROWNUM = N (hibás!)
(nem ad vissza sort)

Megj: Példatár 6.fejezet elméleti összefoglalóban szerepel: "felső-N analízis"

Fel a lap tetejére

Vissza az AB1gyak oldalára (főmenü)