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 - Oracle Dolgozo, Osztaly, Fiz_kategoria táblák lekérdezése
>> 3.RÉSZ - Szeret(nev, gyumolcs) egyszerű táblára nehezebb lekérdezések
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)
- Gyakorlatok példáihoz a táblák létrehozása Oracle SQL-be:
>> createSzeret -- Szeret(nev, gyumolcs)
>> createDolgozo -- Osztaly, Dolgozo, Fiz_kategoria
- 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)
II.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.
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!
-- 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.
III.RÉSZ: A korábbi relációs algebrai feladatokat is folytatjuk új kérdésekkel:
-- Szeret (nev, gyumolcs) tábla létrehozása relax-ban: Relax_Szeret.txt
A tábla létrehozása Oracle SQL-ben: createSzeret -- Szeret(nev, gyumolcs)
Szeret (nev, gyumolcs) tábla sok-sok kapcsolatot ír le, azaz egy vevő
több gyümölcsöt is szerethet és egy gyümölcsöt több vevő is szerethet.
- volt Rel.alg.1.rész: egy táblára vonatkozó lekérdezések, +halmazműveletek
1. Kik szeretik az almát?
2. Kik nem szeretik az almát? (de valami mást igen)
3. Kik szeretik vagy az almát vagy a körtét?
4. Kik szeretik az almát is és a körtét is?
5. Kik azok, akik szeretik az almát, de nem szeretik a körtét?
6. Kik szeretik vagy az almát vagy a körtét, de csak az egyiket?
- volt Rel.alg.2.rész: Tábla átnevezése, önmagával vett direkt szorzata
- Feladatok direkt szorzatra (táblákkal, összesítő függvények nélkül)
7. Kik szeretnek legalább kétféle gyümölcsöt? (direkt szorzattal)
8. Kik szeretnek legalább háromféle gyümölcsöt?
9. Kik szeretnek legfeljebb kétféle gyümölcsöt?
10. Kik szeretnek pontosan kétféle gyümölcsöt?
- új Rel.alg.3.rész: Hányados (minden kifejezése) rel.algebrában -> SQL-ben
11. Kik szeretnek minden gyümölcsöt?
(Kik szeretik az összes olyan gyümölcsöt, amit valaki szeret?)
12. Kik azok, akik legalább azokat a gyümölcsöket szeretik, mint Micimackó?
13. Kik azok, akik legfeljebb azokat a gyümölcsöket szeretik, mint Micimackó?
14. Kik azok, akik pontosan azokat a gyümölcsöket szeretik, mint Micimackó?
-- SQL-ben a relációs algebrai kifejezések SQL-be való átírásával nézzük meg,
és később az 5.gyakorlaton majd alkérdésekkel is megoldjuk SQL-ben!