INFR EA+GY (Hajas Csilla)

7.FELADATSOR (INFORMÁCIÓS RENDSZEREK)

I. Több táblás lekérdezések, csoportosítás, összesítő függvények használata
   >> Oracle DB SQL példák: SQL07_osszekapcsolas.pdf
   >> Oracle DB SQL Lang.Ref >> Joins (Self Joins, Inner Joins, Outer Joins)

   >> Gyakorlat példái: Dolgozo, Osztaly, Fiz_Kategoria táblák: createDolg.txt
  Dolgozo (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
Osztaly (oazon, onev, telephely)
Fiz_Kategoria (kategoria, also, felso)

>> Folyt.előző gyak: Szorzások, összekapcsolások a FROM listán (ö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 JOIN fiz_kategoria ON fizetes BETWEEN also and felso;
SELECT * FROM dolgozo JOIN fiz_kategoria ON fizetes >= also and fizetes <= felso;

-- Külső összekapcsolások/outer joinok:
SELECT * FROM dolgozo LEFT JOIN osztaly ON dolgozo.oazon=osztaly.oazon;
SELECT * FROM dolgozo RIGHT JOIN osztaly ON dolgozo.oazon=osztaly.oazon;
SELECT * FROM dolgozo FULL JOIN osztaly ON dolgozo.oazon=osztaly.oazon;

   -- Külső összekapcsolások három típusa:
SELECT tábla1.oszlop, tábla2.oszlop
[{LEFT | RIGHT | FULL} OUTER JOIN tábla2
ON (tábla1.oszlopnév = tábla2.oszlopnév)]

Feladatok: Teljes select utasítás, záradékok sorrendje

SELECT kif, ..., kif --- [ha van group by, akkor csop.kif., ... csopfv(kif), ...]
FROM táblák összekapcsolása
[WHERE sorok kiválasztási feltétel]
[GROUP BY csop.attr, csop.kif, ...]
[HAVING csop.kiválasztási feltétel]
[ORDER BY kif, ...];

  Több táblás lekérdezések, összekapcsolások, csoportosítás, összesítések
1. Adjuk meg osztályonként az osztályok nevét, telephelyét és az átlagfizetést.
2. Adjuk meg osztályonként az osztályok nevét, telephelyét és az átlagfizetést,
ahol az átlagfizetés nagyobb mint 2000.
3. Adjuk meg az átlagfizetést és telephelyet azokon az osztályokon, ahol legalább
ketten dolgoznak.
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.
6. Adjuk meg azon osztályok nevét és telephelyét, amelyeknek van 1-es fizetési
kategóriájú dolgozója.
7. Adjuk meg azon osztályok nevét és telephelyét, amelyeknek legalább 2 fő
1-es fizetésű kategóriájú dolgozója van.
8. 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.

   II.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ája, (SFW) sorvaltozo, ...
Példa: Adjuk meg a szeret(nev, gyumolcs) tábla alapján, hogy
kik szeretnek minden gyümölcsöt.

-- 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)

-- Példa: Adjuk meg azoknak a dolgozókat, akiknek a legnagyobb a fizetésük.
   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

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ó.

Visszatérve: Több táblás lekérdezések, direkt szorzat, alkérdések
>> Gyakorlat példái: createSzeret.txt séma: Szeret (nev, gyumolcs)
-- 5.feladatsor folyt. "minden" kifejezése SQL-ben (többféle megoldással is!)
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 3.fejezet feladatai