Hajas Csilla Adatbazisok-I.gyakorlat

5.GYAKORLAT (ADATBÁZISOK)

   Témakör: Többtáblás lekérdezések SQL-ben, alkérdések használata
   >> 1.RÉSZ - EMLÉKEZTETŐ: SELECT UTASÍTÁS ÉS REL.ALGEBRA KAPCSOLATA
   >> 2.RÉSZ - TELJES SELECT (KÜLSŐ ÖSSZEKAPCSOLÁSOK, CSOPORTOSÍTÁS)
>> 3.RÉSZ - ALKÉRDÉSEK HASZNÁLATA (A SELECT UTASÍTÁS ZÁRADÉKAIBAN)

1.RÉSZ: EMLÉKEZTETŐ: SELECT UTASÍTÁS ÉS REL.ALGEBRA KAPCSOLATA
- Volt: 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.
   > 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;

Feladatok (voltak) rel.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.

   2.RÉSZ Az előző gyakorlaton megnéztük a teljes SELECT utasítás záradékait,
- Hogyan történik a csoportosítás, GROUP BY záradékra mi lehet a SELECT listán,
- Mi a különbség a WHERE és a HAVING feltételek között, Végén az ORDER BY, stb.
- Ma ugyanezt több táblás lekérdezésekre gyakoroljuk, FROM listán [külső] joinok
   >> Oracle DB SQL példák: SQL07_osszekapcsolas.pdf
   >> Oracle DB SQL Lang.Ref >> Joins (Self Joins, Inner Joins, Outer Joins)

-- ÚJ ANYAG (ez már kivezet az alap relációs algebrából!) Külső/outer joinok:
SELECT dkod, dnev, d.oazon, o.oazon, onev
     FROM dolgozo d
LEFT OUTER JOIN osztaly o
ON d.oazon=o.oazon;
SELECT dkod, dnev, d.oazon, o.oazon, onev
     FROM dolgozo d
RIGHT OUTER JOIN osztaly o
ON d.oazon=o.oazon;
SELECT dkod, dnev, d.oazon, o.oazon, onev
FROM dolgozo d
FULL OUTER JOIN osztaly o
ON d.oazon=o.oazon;

Feladatok: Teljes select utasításra, fontos betartani a záradékok sorrendjét

SELECT kif, ..., kif --- [ha van group by, akkor csop.kif., ... csopfv(kif), ...]
FROM tábla1, ... [ táblák direkt szorzata vagy táblák összekapcsolása ]
   [{LEFT | RIGHT | FULL} OUTER JOIN tábla2
ON (tábla1.kapcs_oszlop = tábla2.kapcs_oszlop)]
[WHERE sorok kiválasztási feltétel]
[GROUP BY csop.attr, csop.kif, ...]
[HAVING csop.kiválasztási feltétel]
[ORDER BY kif, ...];

1. Adjuk meg osztályonként a telephelyet és az átlagfizetést! (oazon, telephely, atlag)
2. Adjuk meg az átlagfizetést és telephelyet azokon az osztályokon, ahol
legalább 4-en dolgoznak. (oazon, telephely, atlag)
3. Adjuk meg azon osztályok nevét és telephelyét, ahol az átlagfizetés
   nagyobb mint 2000. (onev, telephely)
4. Adjuk meg azokat a fizetési kategóriákat, amelybe pontosan 3 dolgozó fizetése esik.
5. Adjuk meg azokat a fizetési kategóriákat, amelyekbe eső dolgozók mindannyian
   ugyanazon az osztályon dolgoznak. (kategoria)
6a. Adjuk meg azon osztályok nevét és telephelyét, amelyeknek van 1-es fizetési
kategóriájú dolgozója. (onev, telephely) [Ez a feladat már volt korábban, de most
segíthet a következőnek a megoldásában.]
6b. Adjuk meg azon osztályok nevét és telephelyét, amelyeknek legalább 2 fő
   1-es fizetési kategóriájú dolgozója van. (onev, telephely)
7. (Kende-Nagy feladatgyűjtemény: 2.17 feladat) Készítsünk listát a páros és páratlan
   azonosítójú (dkod) dolgozók számáról. (paritás, szám)
8. (Kende-Nagy feladatgyűjtemény: 2.23 feladat) Listázzuk ki foglalkozásonként
a dolgozók számát, átlagfizetését (kerekítve) numerikusan és grafikusan is.
200-anként jelenítsünk meg egy '#'-ot. (foglalkozás, szám, átlag, grafika)
9. Adjuk meg az osztályok azonosítóját, nevét, az osztályon dolgozók számát és
az összes ott dolgozó összfizetését (ahol nem dolgozik egy dolgozó sem,
ott az utóbbi kettő legyen 0). Mindezeket csak azokra az osztályokra adjuk meg,
   ahol az összesített fizetés kevesebb, mint 10000. (oazon, onev, létszám, összeg)
10. Adjuk meg osztályonként a dolgozók összfizetését az osztály nevét megjelenítve
ONEV, SUM(FIZETES) formában, és azok az osztályok is jelenjenek meg ahol
nem dolgozik senki, ott az összfizetés 0 legyen. Valamint ha van olyan dolgozó,
akinek nincs megadva, hogy mely osztályon dolgozik, azokat a dolgozókat
egy 'FIKTIV' nevű osztályon gyűjtsük össze. Minden osztályt a nevével plusz
ezt a 'FIKTIV' osztált is jelenítsük meg az itt dolgozók összfizetésével együtt.

   3.RÉSZ Az alkérdéseket használata FROM, WHERE és HAVING záradékokban
>> Oracle DB SQL példák: SQL08_alkerdes1.pdf; SQL08_alkerdes2.pdf
>> Az alkérdések témakörben nézünk példákat szemijoinra, antijoinra.

-- Alkérdések (SFW) bezárójelezett SQL-lekérdezések

-- FROM listán: táblák listáján szerepelhetnek zárójelezett (SFW) temp_tabla,
ezt használtuk korábban [3.gyak], amikor a relációs algebrai kifejezéseket
átírtunk SQL-be: a segédváltozóknak FROM (SFW) temp_tabla felelt meg.

-- A mai gyakorlaton "tisztán SQL"-es olyan alternatív megoldásokat keressünk,
amikor nem használunk inline nézetet (azaz alkérdést a FROM záradékban),
hanem alkérdéseket csak a WHERE illetve HAVING záradékban használunk:

-- WHERE és HAVING záradékban:
(a) t theta (SFW) -- ahol theta az aritmetikai összehasonlítás jele
(b) t theta ANY/ALL(SFW)
(c) t [NOT] IN (SFW)
(d) [NOT] EXISTS (SFW)

-- Az alábbi típusú alkérdések közül melyeknél használható (a), (b), (c) ill.(d)?
1.) skalárértéket adó alkérdések
2.) skalárértékekből álló halmazt illetve multihalmazt adó alkérdések
3.) teljes, többdimenziós tábla

-- Figyelem! Relációs algebrában a szelekció/kiválasztás/szűrés/sigma művelet
   szűrési feltételében csak elemi összehasonlítás és logikai műveletek lehetnek
   továbbra is, mint eddig, vagyis ott nem használhatunk lekérdezéseket, hanem
   rel.algebrában a több táblás lekérdezéseket összekapcsolásokkal oldjuk meg!

-- Példák alkérdésekre: 1.) skalár értékű, 2.) skalárhalmaz, 3.) tetszőleges tábla
   Adjuk meg azoknak a dolgozóknak a nevét, akiknek a legnagyobb a fizetésük.

Feladatok több táblára és alkérdésekre -- dolgozo, osztaly, fiz_kategoria
1. Skalárértékű alkérdéssel:
Kik azok és milyen munkakörben dolgoznak a legnagyobb fizetésű dolgozók?
2. Skalárhalmaz értékű alkérdéssel:
Kik azok és milyen munkakörben dolgoznak a legnagyobb fizetésű dolgozók?
3. Korrelált alkérdéssel:
   Adjuk meg, hogy mely dolgozók fizetése jobb, mint a saját osztályán (vagyis
azon az osztályon, ahol dolgozik az ott) dolgozók átlagfizetése!
4. Adjuk meg azokat a foglalkozásokat, amelyek csak egyetlen osztályon fordulnak elő,
és adjuk meg hozzájuk azt az osztályt is, ahol van ilyen foglalkozású dolgozó.
5. Adjuk meg osztályonként a legnagyobb fizetésu dolgozó(ka)t, és a fizetést.
6. Adjuk meg, hogy az egyes osztályokon hány ember dolgozik (azt is, ahol 0=senki).
7. Adjuk meg azokat a fizetési kategóriákat, amelyekbe beleesik legalább három
olyan dolgozónak a fizetése, akinek nincs beosztottja.
8. Adjuk meg a legrosszabbul kereső főnök fizetését, és fizetési kategóriáját.
9. Adjuk meg, hogy (kerekítve) hány hónapja dolgoznak a cégnél azok a dolgozók,
akiknek a DALLAS-i telephelyű osztályon a legnagyobb a fizetésük.
10. Adjuk meg azokat a foglalkozásokat, amelyek csak egyetlen osztályon fordulnak elő,
és adjuk meg hozzájuk azt az osztályt is, ahol van ilyen foglalkozású dolgozó.

-- szeret táblában [már létrehoztuk korábban: createSzeret.txt ]
Az alábbi feladatokat már láttuk relációs algebrában, rel.alg <-> SQL átírással
("elhagyásos" típusú feladat, a halmazműveleti különbségek átírhatóak SQL-be).
!!! Fontos!!! Relációs algebrában a szelekció (sorok kiválasztása sigma-művelet)
  feltételében nem szerepelhetnek táblák, segédváltozók (alkérdések), hanem csak
attribútumok, konstansok, aritmetikai összehasonlítások (=, <>, <, <=, >, >=) és
logikai műveletek (and, or, not), zárójelek szerepelhetnek csak a szűrőfeltételben.
Most SQL-ben keressünk új megoldásokat (ami csak SQL-ben működik), például
  WHERE záradékban egymásba ágyazott korrelált NOT EXISTS alkérdések!
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ó?

--- ---
>> Önálló gyakorlás: Oracle Példatár Feladatok.pdf 1-3.fejezet SQL feladatai
(kivéve a 3.fej. Hierarchikus lekérdezések connect by később lesz: 8.gyak)

-- Vissza a lap tetejére