5.gyak.
Lekérdezések
kifejezése kiterjeszett
relációs
algebrában, csoportosítás
és
összesítések
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.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)
> Köv.héten: I.ZH 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.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.
> Köv.héten: I.ZH