10.GYAKORLAT (ADATBÁZISOK)
Témakör: táblák,
nézettáblák,
munkatáblák (SQL gépes gyakorlat)
I. SQL DDL
(táblák,
nézettáblák
létrehozása és
használata)
ceate table+constraints:
táblák,
megszorítások létrehozása
create view:
módosítható és nem
módosítható
nézettáblák
majd a meta-adatok lekérdezése az
adatszótár nézetekből
II. SQL SELECT WITH
munkatáblák
hierarhikus illetve
gráf
struktúrájú adatok
lekérdezése
Feladatok:
-- -- -- -- --
>> SQL DDL, relációs
séma megadása =
sortípus + megszorítások:
6EA.pdf;
>>
Oracle:
SQL Language Reference
>> innen Data
Types; illetve Contraint;
1.) Dolgozo,
Osztaly, Fiz_Kategoria táblák, példa: createDolg+Constraints
Dolgozo (dkod,
dnev, foglalkozas,
fonoke, belepes, fizetes, jutalek, oazon)
Osztaly (oazon, onev,
telephely)
Fiz_Kategoria (kategoria,
also, felso)
Készítsünk drop table mytabla;
create table mytabla as select * from tabla;
másolatokat a
dolgozo
és osztaly táblákból
mydolgozo és myosztaly
néven,
véglegesítse az
adattáblákat (commit), majd a
létrehozott
táblákat lássa el
az alábbi
megszorításokkal, alter table
példák (mo_ell: megszorítások.txt)
- Legyen a dkod elsődleges kulcs a
mydolgozo
táblában, és
legyen az oazon
elsődleges kulcs a
myosztaly
táblában és idegen kulcs a
mydolgozo
táblában, amely a
hivatkozási épséget itt
úgy állítsa be, 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.
- 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 két
táblát az egyikben legyenek sportcsapatok
csapat_id,
név.
A másikban a
játékosok, id, név,
mezszám, csapat_id. A csapat
azonosító
legyen idegen kulcs. (lásd
még: create
table-példák.txt).
-- -- -- -- --
>> SQL DDL create view:
módosítható/nem-módosítható
nézettáblák: 7EA.pdf;
3.) 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?
>> Például: Bonyolultabb
lekérdezés
megoldása
nézettáblák
segítségével
-- 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)
-- -- -- -- --
4.) Adatszótár
nézetek - Táblák
és
oszlopai, megszorítások
A DBA_TABLES az
adatbázisban levő összes
tábláról, DBA_TAB_COLUMNS
a
táblák
oszlopairól tartalmaz információt (oszlopok neve, típusa, sorrendje).
-- A fent létrehozott
táblák adatait keressük
meg az
adatszótár
nézetekben,
milyen
információkat találhatunk a
táblákról és a
táblák
oszlopairól?
Adatszótár
nézetek: DBA_OBJECTS,
DBA_TABLES
és
DBA_TAB_COLUMNS
--
Nézzük
meg hogyan
jelennek meg a
megszorítások a
DBA_CONSTRAINTS-ban
-- Nézzük
meg a fentebb létrehozott nézeteket a DBA_VIEWS
katalógustáblában.
HF. köv.gyak-ra: Feladatok a
DBA_TAB_COLUMNS
adatszótár
nézet lekérdezésére
a.) Hány oszlopa van a sila.dolgozo
táblának?
b.) Milyen típusú a sila.dolgozo tábla
6.
oszlopa?
c.) Adjuk meg azoknak a tábláknak a
tulajdonosát
és nevét, amelyeknek
van 'Z' betűvel kezdődő
oszlopa.
d.) Adjuk meg azoknak a tábláknak a tulajdonosát és nevét,
amelyeknek
legalább 8 darab dátum
tipusú oszlopa
van.
e.) Adjuk meg azoknak a tábláknak a tulajdonosát és nevét,
amelyeknek
1. es 4. oszlopa
is VARCHAR2 tipusú.
II.rész: WITH munkatáblák használata az SQL SELECT lekérdezésekben
5.) 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
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)
-- -- -- -- --
6.) Rekurzió: SQL
WITH RECURSION utasítás
>> Eljut feladat, gráfok lekérdezése: 7EA.pdf
(végén, lásd Eljut feladat)
>> Oracle: SQL Language Reference
>> innen: Recursive Subquery
Factoring
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 illetve több, akárhány
átszállással).
Az Eljut feladat gépes
megvalósítása Oracle-ben:
- Ezzel a scripttel jaratok_tabla.txt
készítsünk saját
táblát.
- A szabvány SQL
megoldás kis
változtatással fut le
gépes
környezetben!
Például Oracle
11gR2 verztiótól kezdve működik az
alábbi megoldás:
with
eljut (honnan, hova) as
(select honnan, hova from
jaratok
union all
select jaratok.honnan,
eljut.hova
from jaratok, eljut
where
jaratok.hova=eljut.honnan
)
SEARCH DEPTH FIRST BY honnan SET SORTING
CYCLE honnan SET is_cycle TO 1 DEFAULT 0
select distinct honnan, hova from eljut order by honnan;
-- -- -- -- --
7.) Hierarchikus
lekérdezések
>> Rekurzióra egy másik Oracle
megoldás: SQL12_hiera_lekerd.pdf
>>
Oracle:
SQL Language Reference
>> innen: Hierarchical Queries
SELECT ... FROM... WHERE
... START WITH ... CONNECT BY PRIOR
Oracle
Példatár 3.fejezetében: Hierarchikus
lekérdezések (62-65.o.)