Lekérdezések kifejezése alap relációs algebrában és átírásuk SQL-be
>> I.RÉSZ - Ullman-Widom Tankönyv 2.4. Relációs algebrai feladatok
>> II.RÉSZ - Szeret (nev, gyumolcs) haladó rel.algebrai lekérdezések
>> III.RÉSZ - Dolgozo, Osztaly, Fiz_kategoria táblákban lekérdezések
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. <=> 3. pi lista
FROM R, S, ... -- 1. <=> ______ __________ 1. (R x S x ...)
[WHERE feltétel] -- 2. <=> ______ 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
-- Megjegyzés: Relációs algebrában használhatunk segédváltozókat, aminek
az átírása SQL-be többféleképpen történhet vagy WITH munkatabla as (SFW)
záradékban adjuk meg közvetlenük a SELECT záradék előtt, ekkor a FROM listán
nem csupán valódi táblák, hanem ezek a munkatáblák is szerepelhetnek vagy
a FROM listán bezárójelezett alkérdést, ú.n. inline nézetet is használhatunk
FROM (SFW) temp_táblanév, ahol (SFW) = (SELECT ... FROM ... WHERE ...)
I.RÉSZ Ullman-Widom Tankönyv rel.algebrai feladatai (rel.alg. <=> SQL)
-- 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
munkatáblákat, vagyis a select lekérdezés előtti with záradékot vagy
inline nézeteket, vagyis FROM listán (alkérdés) sorváltozó-t rendelünk.
-- Tankönyv 2.4.szakasz 2.4.1.lekérdezési feladatai: Termékek-feladatok.pdf
Táblák létrehozása Oracle AB-ban: create Termékek és
>> Relációs algebrához Relax-ban: Relax_PC_Termek.txt
-- Alosztályok használata az E/K diagramban, öröklési osztályhierarchia:
-- Osztályhierarchia átalakítása relációkká, három stratégia hogyan alakítjuk át:
1.) Nullértékek használata relációk egyesítéséhez (egy táblában minden attr.)
2.) Objektumorientált megközelítésben való átalakítás (minden attr.öröklődik)
3.) E/K típusú átalakítás (csak a kulcs öröklődik) 4 tábla lesz, ezt használjuk:
Termek(gyarto, modell, tipus)
PC(modell, sebesseg, memoria, merevlemez, ar)
Laptop(modell, sebesseg, memoria, merevlemez, kepernyo, ar)
Nyomtato(modell, szines, tipus, ar)
-- FELDATOK: Lekérdezések kifejezése alap relációs algebrában és SQL-ben
1. Melyek azok a PC modellek, amelyek sebessége legalább 3.00?
2. Mely gyártók készítenek legalább száz gigabájt méretű merevlemezzel
rendelkezı laptopot?
3. Adjuk meg a B gyártó által gyártott összes termék modellszámát és árát,
típustól függetlenül.
4. Adjuk meg valamennyi színes lézernyomtató modellszámát.
5. Melyek azok a gyártók, akik laptopot árulnak, PC-t viszont nem?
6. Melyek azok a merevlemezméretek, amelyek legalább két PC-ben megtalálhatók?
7. Adjuk meg azokat a PC modell párokat, amelyek ugyanolyan gyorsak és
a memóriájuk is ugyanakkora. Egy pár csak egyszer jelenjen meg, azaz
ha már szerepel az (i, j), akkor a (j, i) ne jelenjen meg.
8. Melyik gyártó gyártja a leggyorsabb számítógépet (PC-t vagy laptopot)?
9. Melyik gyártó gyárt legalább három, különböző sebességű PC-t?
10. Melyek azok a gyártók, akik pontosan három típusú PC-t forgalmaznak?
--- --- --- ---
-- További gyakorló feladatok a Tankönyv 2.4.szakasz 2.4.3.lekérdezési feladai:
>> create Csatahajók -- és a Tankönyv feladatai: Csatahajók-feladatok.pdf
II.RÉSZ: Átírás alap rel.algebrai kifejezések <=> SQL lekérdezésre
-- 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.
Feladatok:
-- A korábbi 1.gyak. relációs algebrai feladatok:
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?
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?
-- Kieg. új rész: Hányados (minden kifejezése) relációs algebrában:
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ó?
III.RÉSZ Több táblás lekérdezések SQL-ben és relációs algebrában
-- Oracle gépes környezetben volt a 2.gyak: Egy táblás lekérdezések (where felt)
-- Kieg. új 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:
-- Gyakorlat példái: Dolgozo, Osztaly, Fiz_Kategoria táblák Oracle AB: createDolg.txt
Relációs algebra feladataihoz a táblák létrehozása relax-ban: 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.
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.
--- --- --- ---
-- Összefoglalás: A demo lekérdezésekhez volt: createHRsyn.txt (szinonimák)
-- 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.)
-- 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)