5-6.gyak.
Lekérdezések
kifejezése kiterjeszett
relációs
algebrában, alkérdések, csoportosítás
és
összesítések, külső
összekapcsolások
Feladatok: Ezen
a gyakorlaton olyan
lekérdezéseket fejezük ki SQL-ben,
amit
relációs algebrában nem lehet
kifejezni, majd bevezetjük a kiterjeszett
relációs
algebrát a multihalmazokra, a kiterjesztett
vetítést, az ismétlődések
megszüntetését,
az
összesítési és
csoportosítási műveleteket,
valamint a
rendezés műveletét.
A papíros feladatoknál (mint ahogy a
relációs algebrában volt) ebben a
kiterjesztett
relációs algebrában is
lineáris
jelöléssel és
kifejezésfával is írjuk fel a
lekérdezéseket.
> A gyakorlat gépes része:
>> 1.7.
Alkérdések, korrelált
alkérdések
>> 1.8.
Csoportosítás és
összesítések
az SQL-ben
> A gyakorlat táblás része: >> E3. Kiterjesztett
relációs algebra (papíros feladatok) A
lekérdezési feladatok
megoldásához előbb
az alaptáblákat kell létrehoznunk
(aki volt az előző gyakorlatokon,
azoknak megvan, aki nem
volt,
most
hozza létre) 1.) Oracle
segédanyag
példáinak
kipróbálása:
- Ehhez előkészítés: HR
séma tábláihoz
készítsük el a
szinonimákat create_synonym
>> 1.8.
Csoportosítás és
összesítések
az SQL-ben
>> Kiterjesztett
relációs algebra (papíros feladatok)
3.) A
példatár feladatai
önálló
gyakorlásra:
-- Lásd Feladatok.pdf (2.fejezet
feladatai)
és a táblák
létrehozása cr_dept_emp 1.7.
Alkérdések.
Korrelált
alkérdések.
1.7.1. FROM listán (SFW)
sorváltozó
1.7.2. WHERE záradékban:
(a) t IN (SFW)
(b) t
theta (SFW) -- ahol theta az aritmetikai
összehasonlítás jele
(c) t
theta ANY/ALL(SFW)
(d) 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
4.) egy sort adó
többdimenziós tábla
Feladat:
A szeret táblára
vonatkozó
relációs algebrai feladatait átírtuk
SQL SELECT-re a 2.gyakolraton, ennek a folytatása a hányados átírása.
-- Megoldás/ellenőrzésként:
lásd itt 1.8.
Csoportosítás és
összesítések
az SQL-ben
SELECT column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Feladat_1: Adjuk
meg
osztályonként a
dolgozók összfizetését
a
hr.employees és hr.departments
táblák adatai alapján
(department_name,
sum(salary)) formában.
1. Azok az osztályok is jelenjenek meg ahol nem dolgozik
senki,
ott az összfizetés 0 legyen.
2. Ha van olyan dolgozó, akinek nincs osztálya,
azokat
egy 'FIKTIV' nevű osztályon
gyűjtsük
össze.
3. Csak azokat az osztályokat jelenítsük
meg,
amelyek nevében
van 'B' betű, plusz a FIKTIV
osztályt.
-- Megoldás/ellenőrzésként:
lásd itt
Feladat_2:
A szeret táblára
vonatkozó
relációs algebrai feladatait
átírtuk SQL SELECT-re. Most a
korábbitól eltérő
megoldásokat
adjunk (például a COUNT
függvény és az
alkérdések használatával)
arra, hogy kik azok, akik minden
gyümölcsöt szeretnek?
E3.
Kiterjesztett
relációs algebra
Segédanyag:Lekerd
(nézzük meg SQL-ben is + a Külső
join
művelet példáit is!)
Az alábbi lekérdezéseket
fejezzük ki SQL SELECT-tel, majd írjuk
át a lekérdezést
kiterjesztett relációs algebrai
operátorokat
felhasználó kifejezéssé, majd
rajzoljuk fel
a kifejezésfát is.
- Adjuk meg
osztályonként az osztály
nevét és az ott dolgozók
számát
a dolgozók
száma szerint növekvő sorrendben.
- Adjuk meg azoknak az osztályoknak a
nevét, ahol
az átlagfizetés nagyobb mint 2000.
- Adjuk meg azoknak a foglalkozásoknak
a nevét,
amelyek a 10-es és 20-as
osztályon is
előfordulnak. Ismétlődések ne legyenek
a végeredményben. Fejezd
ki a
lekérdezéseket SQL SELECT-tel és
kiterjeszett
relációs algebrában! 2.)
A gyakorlatok feladatai itt: table_dolgozo.txt
Feladatok az 5.gyakorlatra:
1.8. Csoportosítás, GROUP BY .. HAVING ..
záradékok, alkérdések
HAVING-ben
- Mekkora a minimális és a maximális
fizetés a
dolgozók között?
- Kinek a legkisebb a fizetése?
- Kinek a legnagyobb a fizetése?
- Mennyi a dolgozók összfizetése?
- Mennyi a 20-as osztályon az
átlagfizetés?
- Adjuk meg, hogy hány különböző
foglalkozás fordul elő a dolgozók
között!
- Kik azok a dolgozók, és milyen
munkakörben
dolgoznak, akiknek a legnagyobb
a fizetésük?
- Adjuk meg osztályonként az
átlagfizetést!
- Adjuk meg osztályonként a telephelyet
és az
átlagfizetést!
- Adjuk meg az osztályonkénti legmagasabb
átlagfizetést!
- Adjuk meg azokra az osztályokra az
átlagfizetést, ahol ez nagyobb mint 2000.
- Adjuk meg azokra az osztályokra az
átlagfizetést, ahol legalább
hárman
dolgoznak!
- Melyek azok az osztályok, ahol legalább
hárman
dolgoznak?
- Adjuk meg osztályonként a minimális
fizetést, de csak azokat az
osztályokét, ahol
a minimális fizetés nagyobb, mint a
30-as
osztályon dolgozók minimális
fizetése.
- Adjuk meg osztályonként az ott
dolgozó hivatalnokok (FOGLALKOZAS='CLERK')
átlagfizetését,
de csak azokon az osztályokon, ahol legalább
két hivatalnok dolgozik!
- Adjuk meg, hogy mely dolgozók fizetése jobb,
mint a saját osztályán (ahol dolgozik)
dolgozók
átlagfizetése!
- Adjuk meg, hogy mely dolgozók
átlagjövedelme jobb,
mint a saját
osztályának az
átlagjövedelme,
ahol a jövedelem a fizetés és
jutalék összege, ahol nincs
jutalék
megadva, ott a 0
helyettesítő értékkel
számoljunk (NVL függvény
alkalmazása).
- Adjuk meg, hogy az egyes osztályokon hány ember
dolgozik (azt is, ahol 0=senki).
- 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ályt is
jelenítsük meg az itt dolgozók
összfizetésével
együtt.
Gyakorló
feladatok: Azokat
a feladatokat, amelyekre az órán nem maradt idő,
gyakorló
feladatként otthon be lehet fejezni, aminek célja
az önálló gyakorlás.
Fel a
lap
tetejére
Vissza
az AB1gyak oldalára (főmenü)