TECHNIKAI KÉRDÉSEK Oracle adatbázisok elérése, sqldeveloper
- Ezen a héten az a célunk, hogy előkészítsük az SQL 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
Témakör: 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.
- A relációs algebrához egy relax környezet táblákkal: dbis uibk github io/relax
- A fenti linken elérhető DB (ELTE-AB1) táblákat is használjuk a lekérdezésekhez,
de magunk is létrehozhatunk Dolgozo, Osztaly táblákat: Relax_Dolg_Oszt.txt
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)
Rövid emlékeztető, SQL lekérdezések átírása relációs algebrába
> 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!
Rel.alg. feladataihoz táblák relax-ban Dolgozo, Osztaly: Relax_Dolg_Oszt.txt
-- 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.
Nézzük meg a fenti feladatokat az Oracle SQL SELECT utasítással!
- 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)
- Gyakorlatok példáihoz a táblák létrehozása Oracle SQL-be:
>> createSzeret -- 1.példa: szeret(nev, gyumolcs)
>> createDolgozo -- 2.példa: osztaly, dolgozo, fiz_kategoria