5.GYAKORLAT (ADATBÁZISOK)
Lekérdezések SQL-ben, a teljes SELECT
utasítás
és alkérdések
használata
>> I.RÉSZ
- TELJES SELECT (KÜLSŐ
ÖSSZEKAPCSOLÁSOK,
CSOPORTOSÍTÁS)
>> II.RÉSZ
- ALKÉRDÉSEK HASZNÁLATA (A
SELECT UTASÍTÁS
ZÁRADÉKAIBAN)
I.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)
(A fenti demo
lekérdezésekhez volt: createHRsyn.txt szinonimák)
>> Gyakorlat
példái: Dolgozo, Osztaly, Fiz_Kategoria
táblák sémái,
emlékeztető:
Dolgozo (dkod,
dnev, foglalkozas,
fonoke, belepes, fizetes, jutalek, oazon)
Osztaly (oazon, onev,
telephely)
Fiz_Kategoria (kategoria,
also, felso)
>> Folyt.3.gyak:
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 JOIN
fiz_kategoria ON fizetes BETWEEN also and felso;
SELECT * FROM dolgozo JOIN
fiz_kategoria ON fizetes >= also and fizetes <=
felso;
-- ÚJ ANYAG (ez már kivezet az alap
relációs algebrából!)
Külső/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;
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,
...];
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.
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á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 [már
létrehoztuk korábban: createDolg.txt ]
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 feladatok 3.gyak.
Rel.alg.3.részben voltak rel.alg <-> SQL
átírással, de
keressünk új
megoldásokat
SQL-ben WHERE
záradékban egymásba
beágyazott
korrelált NOT EXISTS
alkérdéssel:
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)
>> 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