Témakör: Több táblás lekérdezések relációs algebrában és SQL-ben,
ekvivalens átírások alap rel.algebrai kifejezések <=> és SQL SELECT
-- Megjegyzés: Relációs algebrában használhatunk segédváltozókat, aminek
az SQL-ben inline nézetet használunk vagyis FROM listán (SFW) temp_tabla
(itt a rövidítés: SFW = SELECT ... FROM ... WHERE ... utasítás)
-- SQL-ben halmazműveleteket azonos típusú lekérdezések között végezzük!
Miben különbözik a halmazműveletek halmaz és multihalmaz szemantikája?
>> Oracle DB SQL példák: SQL04_set_operators.pdf;
>> Oracle DB SQL Lang.Ref. >> 4.Op. >> Set Operators (halmazműv.)
(A fenti demo lekérdezésekhez volt: createHRsyn.txt szinonimák)
-- SQL SELECT utasítás FROM listán táblák direkt szorzata, összekapcsolása
>> Oracle DB SQL példák: SQL07_osszekapcsolas.pdf
>> Oracle DB SQL Lang.Ref >> Joins (Self Joins, Inner Joins, Outer Joins)
I.RÉSZ: Átírás alap rel.algebrai kifejezés <=> SQL (1., 2.gyak.folyt.)
-- Gyakorlat példái: Szeret (nev, gyumolcs) sémájú tábla létrehozása:
Oracle AB-ban SQL: createSzeret.txt és Relax-ban: Relax_Szeret.txt
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.
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
Feladatok:
-- A korábbi relációs algebrai feladatokat írjuk át SELECT utasításra!
- 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ó?
II.RÉSZ Több táblás lekérdezések SQL-ben és relációs algebrában
-- 1.rész: Egy táblás lekérdezések, és a halmazműveletek, lásd fent 1-6.f.;
-- 2.rész: Tábla átnevezése, önmagával vett direkt szorzata, lásd fent 7-10.f.;
- 3.rész: Hányados -> átír SQL-be, lásd fent 11-14.f. (folyt.5.gyak.SQL több mo is)
-- 4.rész: Több táblás lekérdezések, direkt szorzat, természetes összekapcsolás,
és az "Elhagyásos" típusú feladatok, pl. max/min kifejezése alap rel.algebrában:
Rel.alg.4.rész feladataihoz táblák relax-ban Dolgozo, Osztaly: Relax_Dolg_Oszt.txt
-- Gyakorlat példái: Dolgozo, Osztaly, Fiz_Kategoria táblák Oracle AB: createDolg.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.
Osztaly (oazon, onev, telephely)
Dolgozo (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
Fiz_Kategoria (kategoria, also, felso) -- táblázat a fizetési sávokat adja meg
Rövid emlékeztető:
>> Szorzások, összekapcsolások a FROM listán (rövid összefoglaló)
-- Direkt szorzat: SELECT * FROM dolgozo, osztaly;
SELECT *|{[DISTINCT] oszlopnév|kifejezés [másodnév],...}
FROM táblanév [[sorváltozó], táblanév [sorváltozó], ... ]
[WHERE feltétel]
[ORDER BY {oszlopnév [DESC], ...}];
-- 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;
SELECT tábla1.oszlop, tábla2.oszlop
FROM tábla1
[NATURAL JOIN tábla2] |
[JOIN tábla2 USING (oszlopnév)] |
[JOIN tábla2
ON (tábla1.oszlopnév = tábla2.oszlopnév)]
[CROSS JOIN tábla2]
-- Theta-join:
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:
-- 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.
H.F. A Tankönyvből további feladatok lekérdezésekre (Rel.alg. <=> SQL)
>> További gyakorló feladatok a Tankönyv 2.4.szakasz lekérdezési feladatai:
>> create Termékek -- és a Tankönyv feladatai: Termékek-feladatok.pdf
>> create Csatahajók -- és a Tankönyv feladatai: Csatahajók-feladatok.pdf
-- Itt most olyan megoldásokat keressünk, amit előbb az alap relációs algebrai
lekérdezésként adunk meg, majd ezt átírjuk SQL lekérdezésekre.
-- Ha a relációs algebrában segédváltozókat is használunk, ahhoz az SQL-ben
most inline nézeteket, vagyis FROM listán (alkérdés) sorváltozó-t rendelünk.