Lekérdezések kifejezése alap relációs algebrában és átírásuk SQL-be
>> I.RÉSZ - Szeret (nev, gyumolcs) sok-sok kapcsolatot leíró táblában
>> II.RÉSZ - Dolgozo, Osztaly, Fiz_kategoria táblákban lekérdezések
>> III.RÉSZ - További példák az Ullman-Widom Tankönyvből: Termékek
-- 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ábla, ahol (SFW) = (SELECT ... FROM ... WHERE ...) alkérdé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.
III.RÉSZ Tankönyvből további feladatok lekérdezésekre (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 SQL script: 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.) E/K típusú átalakítás (4 tábla lesz, ezt használjuk, csak a kulcs öröklődik)
2.) Objektumorientált megközelítésben való átalakítás (minden attr.öröklődik)
3.) Nullértékek használata relációk egyesítéséhez (egy táblában minden attr.)
-- 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