Témakör: Alap relációs algebrai lekérdezések átírása SQL lekérdezésekre
>> 1.RÉSZ - TECHNIKAI KÉRDÉSEK Oracle adatbázisok elérése, sqldeveloper
>> 2.RÉSZ - SQL egytáblás lekérdezések , where feltétel, háromértékű logika
>> 3.RÉSZ - SQL többtáblás lekérdezések (Oracle demo Dolgozo-Osztaly táblák)
1.RÉSZ: TECHNIKAI KÉRDÉSEK Oracle adatbázisok elérése, sqldeveloper
- Az óra elején előkészítjük az SQL lekérdezések gyakorláshoz a környezetet,
megbeszéljük hogyan csatlakozzunk az ELTE szervereken az adatbázisokhoz.
- ELTE-s ORACLE ADATBÁZIS szerverek elérése -->> adatbazis_eleres.html
A lekérdezéseket relációs algebrában is és Oracle SQL-ben is nézzük meg!
- Az ABKR-felépítése, SQL főbb utasításai: SQL01_bevezetes.pdf
- Oracle demo példa HR séma: Schema Diagrams -> ehhez hasonló az órai példa.
- Lekérdezésekkel kezdünk, de ahhoz, hogy az SQL lekérdezéseket kipróbáljuk
létre kell hoznunk a táblákat, a scriptben szereplő utasításokat később tanuljuk:
create table táblanév (oszlopnév típus, stb, megszorítások) részletesen 7.gyak.
lesz az Oracle alapvető adattípusai: Oracle_tipusok.txt (varchar2, number, date)
- Itt a Dolgozo és Osztaly közötti dolg(dkod, oazon) kapcsolat sok-egy kapcsolat,
azaz egy dolgozo nem dolgozhat több osztályon, legfeljebb csak egy osztályon,
(ha tudjuk, hogy a dolgozó melyik osztályon dolgozik, akkor az egyértelmű).
Hasonlóan két Dolgozo közötti fonok(dkod, fonoke) kapcsolat is ilyen sok-egy
kapcsolat. A sok-egy kapcsolatokat leíró táblák beolvadtak a Dolgozo táblába.
Az E/K modellt átalakítjuk relációs modellre:
-- 1a.) lépés: Entitások átírása relációsémákra
Osztaly (oazon, onev, telephely)
Dolgozo (dkod, dnev, foglalkozas, belepes, fizetes, jutalek)
-- 1b.) lépés: Kapcsolatok átírása relációsémákra
HolDolg(dkod, oazon)
KiAFonoke(dkod, fonoke)
-- 2.lépés Táblák összevonása után a végső adatbázis séma:
Osztaly (oazon, onev, telephely)
Dolgozo (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
-- A feladatokban található még egy kódtáblázat is, ennek a sémája:
Fiz_Kategoria (kategoria, also, felso) -- táblázat a fizetési sávokat adja meg
Relációsémák:
Osztaly (oazon, onev, telephely)
Dolgozo (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
Fiz_Kategoria (kategoria, also, felso)
- Gyakorlatok példáihoz a táblák létrehozása Oracle SQL-ben:
>> createSzeret -- 1.példa: szeret(nev, gyumolcs)
>> createDolgozo -- 2.példa: osztaly, dolgozo, fiz_kategoria
2.RÉSZ: Egy tábla lekérdezése (oszlopok vetítése és sorok kiválasztása)
Átírás rel.algebrai unér műveletei <=> SQL SELECT utasítás
pi lista sigma feltétel (T) <=> select lista from T where feltétel
Feladatok relációs algebrai kifejezésekre és átírásuk SQL SELECT-re:
- Rel.algebra vetítés művelete (SQL-ben multihalmaz -> rel.alg.-ban halmaz!)
1. Adjuk meg a dolgozók között előforduló foglalkozások neveit! (select lista)
2. Adjuk meg a dolgozók között előforduló foglalkozások neveit (DISTINCT is),
az eredmény halmaz legyen, vagyis minden foglalkozást csak egyszer írjuk ki!
3. Adjuk meg a dolgozók kódját, nevét és az éves fizetését, amikor kifejezést
használunk az oszlopnevek helyén, ott adjunk új oszlopnevet ("éves fizetés")
- Rel.algebra kiválasztás művelete és az SQL SELECT utasítás WHERE feltétele
- NULL hiányzó érték, lásd SQL Lang.Ref. Nulls, 3 értékű logika, lásd igazságtábla
4. Kik azok a dolgozók, akiknek a fizetése > 2800? (kiválasztás: elemi feltételek)
5. Adjuk meg azokat a dolgozókat, akiknek a foglalkozása 'MANAGER' (kar.tip.érték)
6. Kik azok a dolgozók, akiknek a fizetése 2000 és 4500 között van? (dkod, dnev)
(1.mo: where-ben: intervallum); (HF 2.mo: rel.alg.kiválasztás: összetett feltétel)
7. Kik azok a dolgozók, akik a 10-es vagy a 20-as osztályon dolgoznak?
(1.mo: where-ben: in feltétel); (HF 2.mo: rel.alg.kiválasztás: összetett feltétel)
[ csak SQL: 8. Adjuk meg azon dolgozókat, akik nevének második betűje 'A' (like) ]
9. Kik azok a dolgozók, akiknek a jutaléka nagyobb, mint 600?
10. Kik azok a dolgozók, akiknek a jutaléka kisebb-vagy-egyenlő, mint 600?
11. Kik azok a dolgozók, akiknek a jutaléka ismeretlen/hiányzó adat. (NULL felt)
12. Kik azok a dolgozók, akiknek a jutaléka ismert (vagyis nem NULL)
- Az eredménytábla sorainak rendezése (SELECT utasítás ORDER BY záradéka)
[ Ez nem alap relációs algebrai művelet, de az SQL lekérdezésekben hasznos ]
13. Listázzuk ki a dolgozókat foglalkozásonként, azon belül nevenként rendezve.
14. Listázzuk ki a dolgozókat fizetés szerint csökkenőleg rendezve.
15. Rendezés segítségével az első N sor elérése Oracle 12.2 adatbázisban,
lásd Row Limiting Examples.html (forrás: Oracle Database SQL Lang. Ref. html)
Összefoglalás: SQL SELECT utasítás egytáblás lekérdezések
- Oracle segédanyagok: SQL02_select_lista.pdf; SQL03_where_feltetel.pdf
Az Oracle demo lekérdezésekhez elég szinonimát használni: createHRsyn.txt
de a legjobb, ha az ott szereplő Employees táblákra vonatkozó példákat a
Dolgozo (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
saját tábláin alkalmazza (átírja a megfelelő táblanév, oszlopnév, értékekre).
3.RÉSZ: Lekérdezések kifejezése alap relációs algebrában és átírásuk SQL-be.
Átírás alap rel.algebrai kifejezés <=> SQL SELECT FROM WHERE és hz.műv.
> Egyszerű SFW lekérdezések <=> vetítés kiválasztás szorzás
SELECT lista -- 3. <=> pi lista
FROM R, S, ... -- 1. <=> ______ __________ (R x S x ...)
[WHERE feltétel] -- 2. <=> ______ sigma feltétel
> SQL lekérdezésekben a halmazműveletek használata:
Fontos! Az SQL-ben a halmazműveleteket nem táblákra, hanem
SFW lekérdezésekre alkalmazzuk (azonos dimenzió, kompatibilis típus)
Alapértelmezésben halmazként értelmezve: duplikációk nélkül
"ALL" kiegészítőszóval multihalmazként értelmezve (multiplicitás)
SFW
{UNION [ALL]| MINUS | INTERSECT }
SFW
> Szorzások, összekapcsolások a FROM listán (rövid összefoglaló)
-- Direkt szorzat: SELECT * FROM Dolgozo, Osztaly;
-- Természetes összekapcsolás és az inner join összehasonlítása:
SELECT dkod, dnev, oazon, onev FROM Dolgozo NATURAL JOIN Osztaly;
SELECT dkod, dnev, Dolgozo.oazon, onev FROM Dolgozo, Osztaly
WHERE Dolgozo.oazon=Osztaly.oazon;
SELECT dkod, dnev, Dolgozo.oazon, onev FROM Dolgozo JOIN Osztaly
ON Dolgozo.oazon=Osztaly.oazon;
-- Theta-join:
SELECT * FROM Dolgozo, Fiz_kategoria WHERE fizetes >= also and fizetes <= felso;
SELECT * FROM Dolgozo JOIN Fiz_kategoria ON fizetes BETWEEN also and felso;
SELECT * FROM Dolgozo JOIN Fiz_kategoria ON fizetes >= also and fizetes <= felso;
-- Később 5.gyakorlaton folyt. külső joinok ([LEFT | RIGHT | FULL] OUTER JOIN) és
az alkérdések témakörben is nézünk további példákat szemijoinra, antijoinra.
Feladatok relációs algebrai kifejezésekre és átírásuk SQL SELECT-re:
-- Fejezzük ki alap relációs algebrai kifejezésekkel, majd írjuk át SQL-be!
-- cross join (selfjoin - tábla önmagával vett direkt szorzata)
1. Kik azok a dolgozók, akiknek a főnöke KING? (dkod, dnev, fizetes)
2. Kik azok a dolgozók, akik főnökének a főnöke KING?
3. Adjuk meg azokat a dolgozókat, akik többet keresnek a főnöküknél.
-- natural join és theta join összevetése (más az oazon oszlopra való hivatkozás)
4. Kik azok a dolgozók, akik osztályának telephelye DALLAS vagy CHICAGO?
5. Kik azok a dolgozók, akik osztályának telephelye nem DALLAS és nem CHICAGO?
-- maximum kifejezése függvények nélkül, csak egyszerű tábla műveletekkel
6. Kik azok a dolgozók, akiknek a legmagasabb a fizetésük, itt a lekérdezést
alap relációs algebrai kifejezésként írjuk fel, azaz nem használható rendezés,
nem használhatóak függvények, és a sigma kiválasztási feltételben nem lehet
lekérdezés/tábla, hanem csak elemi összehasonlítások (=, !=, <, <=, >, >=) és
not, and és or logikai műveletek szerepelhetnek csak a kiválasztási feltételben!
-- További feladatokat írjuk fel relációs algebrában is SQL SELECT utasítással is!
7. Kik azok a dolgozók, akiknek van 2000-nél nagyobb fizetésű beosztottja.
8. Kik azok a dolgozók, akiknek nincs 2000-nél nagyobb fizetésű beosztottja.
9. Mely telephelyeken van elemző (ANALYST) foglalkozású dolgozó.
10. Mely telephelyeken nincs elemző (ANALYST) foglalkozású dolgozó.
11. Adjuk meg azon osztályok nevét és telephelyét, amelyeknek
van 1-es fizetési kategóriájú dolgozója.
12. Adjuk meg azon osztályok nevét és telephelyét, amelyeknek
nincs 1-es fizetési kategóriájú dolgozója.
-- Vissza a lap tetejére