Adatbázisrendszerek gyakorlati segédlet gazdasági informatikusok számára Szeghalmy Szilvia Debreceni Egyetem
Tartalomjegyzék Adatbázisrendszerek gyakorlati segédlet ............................................................................................ 1 1. Bevezetés......................................................................................................................................... 2 1.1. A jegyzetben használt jelölések............................................................................................... 2 1.2. Környezet................................................................................................................................. 2 2. SQL Oracle környezetben ................................................................................................................ 2 2.1. Néhány beépített típus............................................................................................................ 2 2.2. Operátorok .............................................................................................................................. 3 2.3. Data Definition Language (DDL) .............................................................................................. 4 2.3.1. Tábla létrehozása............................................................................................................. 4 2.4. DQL: Adatok lekérdezése ........................................................................................................ 6 2.4.1. Egy táblára vonatkozó egyszerű lekérdezések ................................................................ 7 2.4.2. Rendezés.......................................................................................................................... 8 2.4.3. Sorok leválogatása ........................................................................................................... 9 2.4.4. Csoportok képzése (GROUP BY, HAVING) ..................................................................... 10 2.4.5. Táblák összekapcsolása ................................................................................................. 12 2.4.6. Beágyazott lekérdezések ............................................................................................... 15 2.4.7. Első n sor lekérdezése ................................................................................................... 18 2.4.8. Összetett lekérdezések .................................................................................................. 19 3. Függelék......................................................................................................................................... 23 3.1. Boltocska kezdetleges cikk nyilvántartó rendszere ............................................................... 23 3.2. Leegyszerűsített bűnügyi nyilvántartó rendszer ................................................................... 24
1. Bevezetés A segédanyag nem lektorált, ezért tartalmazhat, és minden bizonnyal tartalmaz is hibákat. Használata csak kellő körültekintéssel javasolt.
1.1.
A jegyzetben használt jelölések
Az SQL utasítások pontos szintaktikája az adatbázis-kezelő rendszer referencia könyvében keresendő, a jegyzetben csak egyszerűsített változatok szerepelnek. A jegyzetben használt jelölések: []
Opcionális (nem kötelező a zárójelek közti rész szerepeltetése)
{a|b}
Kizáró vagy: a vagy b, de csak az egyik
…
Ismétlődés
A leírás alapján helyettesítendő
kód
A kódrészletek betűtípusa Courier, a sorok számozása sosem része a kódnak!
1.2.
Környezet
A gyakorlaton Oracle adatbázis-kezelő rendszert fogunk használni, Oracle SQL Developer kliensen keresztül, mely a Campuson kívülről is elérhető a tárgyra járó hallgatók számára. Az SQL Developerben az adatbázis-szerverhez való kapcsolódáshoz a következő adatokat kell megadni: User: Password: Hostname: codd.inf.unideb.hu Port: 1521 SID: ora12c
2. SQL Oracle környezetben 2.1.
Néhány beépített típus
A Oracle beépített típusai közül a gyakorlaton az alábbiakat fogjuk használni: CHAR[(hossz)]: Fix hosszon tárolt karaktersorozat. Az alapértelmezett hossz 1. Ha az oszlopba hosszabb szöveget próbálunk beszúrni hiba lép fel. Jellemzően akkor használjuk, ha a táblázat minden sorában azonos hosszúságú érték fog állni. Például: személyi igazolvány szám, termék kódok tárolására. VARCHAR2[(hossz)]: Változó hosszon tárolt karaktersorozat. DATE: dátumtípus
NUMBER[(p [, s])]: Ahol p megadja hány jegyen történik az ábrázolás, s pedig megadja ebből mennyi lesz a tört rész tárolására felhasználva. Ha a táblázatba szúrandó adat tizedespont utáni része s-nél több jegyből áll kerekítés történik.
2.2.
Operátorok
Aritmetikai operátorok Rendre: pozitív, negatív előjel, összeadás, kivonás, (valós)osztás, szorzás +(unáris)
-(unáris)
+
-
/
*
Megj.: A maradékos osztás a Mod(osztandó, osztó) függvénnyel valósítható meg. Kerekítést a Round(szám [,jegy]) függvénnyel végezhetünk, ahol a jegy azt adja meg hány tizedesjegyre történjen a kerekítés. A tizedesjegyek levágására Floor( szám [, jegy]) függvén használható. Karakteres operátor Karaktersorozatok összefűzése (bináris). ||
Logikai operátorok Rendre és, vagy, tagadás. And
Or
Not
Hasonlító operátorok Rendre: kisebb, kisebb vagy egyenlő, nagyobb, nagyobb vagy egyenlő, egyenlő, nem egyenlő. <
<=
>
>=
=
<>
null érték vizsgálat kifejezés IS [NOT] NULL
Mintaillesztést a LIKE operátor segítségével végezhetünk. kifejezés LIKE minta
A kifejezés igaz, ha a bal oldali operandus illeszkedik a jobb oldalon megadott mintára. A mintában szerepelhet aláhúzás jel (_), mely egyetlen tetszőleges karaktert helyettesít vagy százalék jel (%) melyre tetszőleges karaktersorozat illeszkedik. kifejezés [NOT] BETWEEN érték1 AND érték2
Később tárgyaljuk (a beágyazott lekérdezéseknél): IN, ALL, ANY, EXISTS Halmazoperátorok Unió, kivonás, metszet:
Union [All]
Minus
Intersect [All]
Az All használatával az azonos sorok is megmaradnak (multihalmaz)
2.3.
Data Definition Language (DDL)
Az SQL nyelv adatdefiníciós utasításai segítségével alakíthatjuk ki az adatok tárolására szolgáló sémát: többek között táblákat, táblák közti kapcsolatokat, indexeket, megszorításokat hozhatunk létre, módosíthatunk, vagy törölhetünk. 2.3.1.
Tábla létrehozása
Az adatbázisokban az összefüggő adatok egy, vagy több táblában tárolódnak. A táblában az azonos jellemzőkkel rendelkező objektumok (Piroska nénitől kezdve a csokoládén át a banki tranzakciókig bármi objektumnak számíthat) tulajdonságainak tárolására lesz lehetőségünk. A táblákat olyan táblázatként képzelhetjük el, melynek fejlécében az oszlopnevek állnak, soraiban pedig egy-egy konkrét objektumhoz tartozó értékek szerepelnek. Neptunkód XYABDF RGHHJR
Név Kis Ilona Piroska néni
Született 1980.01.24. 1989.04.24.
Egy tábla létrehozásának általános alakja: CREATE TABLE táblanév( oszlopdefiníció [,oszlopdefiníció]… [táblaszintű megszorítás]… ); Oszlopdefiníció: oszlopnév típus [oszlopszintű_megszorítások] Oszlop szintű megszorítások: Egyetlen oszlopra vonatkoznak. Ezeket az oszlop után írva adhatjuk meg. oszlopnév [CONSTRAINT nev] megszorítás [megszorítás]... Táblaszintű megszorítások: Több oszlopra is vonatkoznak A create table parancsban az oszlopdefiníciók után áll. [CONSTRAINT nev] megszorítás ( oszlopnév [, oszlopnév]...)
Megszorítások: Elsődleges kulcs megszorítás (Primary Key): Minden táblának egy elsődleges kulcsa lehet, ami lehet összetett is (több attribútumból álló). Az elsődleges kulcsot alkotó attribútumokban nem állhat null érték, és nem szerepelhet két olyan sor a táblában, melyek értéke az elsődleges kulcsot alkotó attribútumokon megegyezik. (Összetett kulcs esetében egy-egy attribútumot tekintve lehet azonosság, az a lényeg, hogy együtt nézve az értékeket egyediek legyenek.)
Egyediség megszorítás (Unique): Több ilyen megszorítás is szerepelhet egy táblára vonatkozóan. Hasonlóan az elsődleges kulcshoz elvárjuk hogy az értékek egyediek legyenek, de null érték szerepelhet. [NOT] null megszorítás: Minden típus tartományába beletartozik egy speciális érték a null. Ezzel az értékkel jelezhetjük, hogy az adott érték nincs értelmezve, vagy nem létezik, esetleg csak nem ismerjük. A null megszorítás megengedi, hogy az oszlopban null értékek szerepeljenek, a NOT null tiltja. Mivel a null az alapértelmezett, csak a NOT null megszorítást szokás kiírni. Ez a megszorítás csak egy oszlopra vonatkozóan állhat, a táblaszintű megszorítások közt nem is szerepelhet. Külső kulcs megszorítás: Két tábla közti kapcsolat szabályozására szolgál. Biztosítja, hogy a külső kulcsot tartalmazó tábla külső kulcsot alkotó attribútumaiban csak null érték vagy olyan értékek állhatnak, mely a hivatkozott tábla (amelyhez ez a tábla kapcsolódik) hivatkozott oszlopaiban ténylegesen szerepel. Formája kicsit eltér az általános megszorítás alakjától, hiszen a hivatkozott táblát is meg kell adnunk. Állhat oszlop után írva oszlopmegszorításként: [CONSTRAINT megszorításnév] REFERENCES hivatkozott_tábla (oszlopnév)
vagy az oszlopdefiníciók után táblaszintű megszorítás formájában: [CONSTRAINT megszorításnév] FOREIGN KEY (oszlopnév [, oszlopnév]...) REFERENCES hivatkozott_tábla (oszlopnév [,oszlopnév]...)
Általános megszorítás: Ezzel biztosíthatjuk egy adott oszlopba csak egy feltételnek eleget tevő érték kerüljön be. A feltétel akár több oszlopra is vonatkozhat, ekkor táblaszintű megszorításként kell megadnunk. [CONSTRAINT megszorításnév] CHECK (feltétel)
Példa: create table cikk( cikk_kod CHAR(3) PRIMARY KEY, cikknev VARCHAR2(40), gyarto CHAR(2), beszerar NUMERIC(6) CONSTRAINT jajj NOT null, eladar NUMERIC(6), CHECK (beszerar < eladar), CONSTRAINT jajj2 UNIQUE (cikknev, gyarto) );
A példában lévő táblában termékeket tárolunk. A tábla elsődleges kulcsa a cikk_kod, tehát minden cikk egyedi kóddal rendelkezik, amelyet kötelező megadni. Biztosak lehetünk abban is, hogy egy gyártótól nem szerepel kétszer ugyan az a cikk a táblában, hiszen a gyártó és cikknév együttesen egyedi kell, hogy legyen. Ha ezt a megszorítást később esetleg törölnénk, akkor a jajj2 néven érhetjük el. A beszerzési árat kötelező megadni. Ezt a megszorítást is elneveztük: jajj-ra. Ezen kívül van egy általános megszorításunk is (check), ami biztosítja, hogy egyetlen termék eladási ára se legyen kisebb, mint amennyiért beszereztük azt. Néhány példa arra nézve milyen adatok szúrhatók be a táblába, és milyenek nem: insert into cikk values('1BB', 'Izo Tej','00', 100, 200); ’beszúrja
insert insert insert insert insert insert insert insert insert insert
into into into into into into into into into into
cikk(gyarto, beszerar) values('AA', 4); ’hiba: PK nem lehet null. cikk values('1BB', 'Kifli','01', 100, 200); ’hiba: PK már szerepel cikk values('1BD', 'Vaj','2', 300, 200); ’hiba: beszerar > eladar cikk values('1BE', 'Vaj','2', 100, 300); ’beszúrja cikk values( 512, 'Keksz','4', 10, 20); ’beszúrja (konverzió) cikk values('1BE', 'Vaj','2', 100, 200); ’hiba: szerepel a Vaj, 2 cikk values('2BB', 'Vaj','4', 100, 200); ’beszúrja cikk values('5BB', 'Tej','01', null, 200); ’hiba: not null cikk values('ADD', 'Ropi','4', '15', 80); ’beszúrja (konverzió) cikk values('X2', 'Íz','01', 'ten', 80); ’hiba: nem konvertálható
Feladat 1: Hozzunk létre egy gyártók táblát, melyben a gyártó kódját és nevét tároljuk, és alakítsuk úgy a fenti cikk tábla létrehozására szolgáló kódot, hogy az a gyarto oszlopon keresztül kapcsolódjon a ehhez a táblához. A gyártónevek egyediek, és kötelező a megadásuk. create table gyartok( gyartokod CHAR(2) PRIMARY KEY, nev VARCHAR2(40) NOT null UNIQUE ); create table cikk( cikk_kod CHAR(3) PRIMARY KEY, cikknev VARCHAR2(40), gyarto CHAR(2) REFERENCES gyartok( gyartokod) , beszerar NUMERIC(6) CONSTRAINT jajj NOT null, eladar NUMERIC(6), CHECK (beszerar < eladar), CONSTRAINT jajj2 UNIQUE (cikknev, gyarto) );
Feladat 2: Mely sorok végrehajtása során kapunk hibaüzenetet az alábbi sorok futtatása során, feltételezve hogy az előbb létrejött táblákkal dolgozunk és mindkét tábla üres kezdetben? 1. 2. 3. 4. 5.
insert insert insert insert insert
into into into into into
gyartok values('000', 'Lipo'); gyartok values('01', 'Bali'); gyartok( gyartokod) values('12'); gyartok values('2', 'Jodo'); gyartok( nev, gyartokod) values( 'Kincs', '05');
6. 7. 8. 9. 10. 11.
insert insert insert insert insert insert
into into into into into into
cikk values('A', 'Tészta','01', 100, 200); cikk values('X', 'Tészta','09', 100, 200); cikk(cikk_kod, gyarto, beszerar) values('B', 2, 10); cikk values('C', 'Kifli','02', 100, 200); cikk values('E', 'Vaj','2 ', 500, 400); cikk values('F', 'Méz','01', 34, null);
Megoldás 1. sor: Túl hosszú a gyártókódhoz adott érték. (2 karakter van csak a tárolására) 3. sor: A gyártónév nem lehet null ezért kötelező megadni 7. sor: Külső kulcs hiba, mert 09-es gyártó nem létezik. 9. sor: Külső kulcs hiba, mert 02-es gyártó nincs, csak '2 '-es 10. sor: beszerzési ár nem lehet nagyobb az eladási árnál. (A külső kulcs jó.)
2.4.
DQL: Adatok lekérdezése
Az adatok lekérdezésének az általános (de egyszerűsített) alakja a következő:
SELECT [{ALL|DISTINCT}] mezőkifejezés [álnév] [,mezőkifejezés [álnév]]... FROM táblakifejezés [álnév] [WHERE feltétel ] [GROUP BY csoportosítómező [, csoportosítómező ]...] [HAVING feltétel ] [ORDER BY mezőkifejzés [, mezőkifejezés]... ]
A mezőkifejezés általában a FROM kulcsszó utáni táblakifejezés egy attribútumát jelenti, de ahogy a későbbiekben látni fogjuk tényleges kifejezések is szerepelhetnek ott. Ezen túl egy speciális jel is állhat az attribútum nevek helyett, a *, mely a FROM kulcsszó után álló táblakifejezés összes mezőjének kiírását helyettesíti (ezért mellette mezőnév már nem állhat). A mezők minősíthetők azzal a táblanévvel, amelyből származnak. A minősítés alakja táblanév.mezőnév. A * jelet is minősíthetjük táblanévvel, ekkor az adott tábla összes mezőjét helyettesíti. Lehetőségünk van az mezőkifejezésekhez és a táblához álnevet rendelni, amivel a későbbi műveletek során hivatkozhatunk rájuk. Táblakifejezés alatt konkrét táblát, vagy olyan műveletet értünk, mely táblát ad eredményül. A SELECT után megadhatjuk, hogy teljesen azonos sorok esetén mi a teendő. A relációalgebrai megközelítéstől eltérően itt az alapértelmezett az azonos sorok megtartása (ALL). Ha csak az egyedi sorok érdekelnek bennünket, akkor a DISTINCT kulcsszót kell kiírnunk. Látható, hogy számos opcionálisan megadható része lehet a szelekt utasításnak. Ezek a jegyzet további részében kerülnek bemutatásra, itt csak 1-1 mondat erejéig kerülnek bemutatásra: WHERE: GROUP BY: HAVING: ORDER BY: 2.4.1.
Az utána álló feltételnek megfelelő sorok leválogatása. Az utána álló mezőkifejezések alapján csoportosítja az adatokat. Feltételnek megfelelő sorok leválogatása a csoportosítás után. Sorok rendezése a megadott mezők alapján.
Egy táblára vonatkozó egyszerű lekérdezések
A lekérdezés legegyszerűbb formája, amikor egy tábla egész tartalmát szeretnénk, esetleg bizonyos oszlopainak tartalmára vagyunk kíváncsiak. Példa: Kérdezzük le az összes gyártó összes adatát! SELECT gyartokod, nev FROM gyartok; SELECT * FROM gyartok; 'Ez is ugyan azt csinálja.
Példa: Kérdezzük le a cikkek kódját és nevét! SELECT cikk_kod, cikknev FROM cikk;
Korábban említésre került, hogy az oszlopokat át is nevezhetjük, vagy kifejezéseket is szerepeltethetünk a SELECT utáni részben. A kifejezés jelenthet egy vagy több attribútummal, konstanssal végzett aritmetikai műveletet, de akár függvényhívást is. Az eredmény relációban a kifejezés új attribútumként jelenik meg. Nézzünk erre is néhány példát. Példa: Kérdezzük le a cikkek kódját és nevét! A cikk_kod mezőt nevezzük át kódra! SELECT cikk_kod kód, cikknev FROM cikk;
Példa: Jelenítsük meg mennyi árrés van az egyes termékeknél! SELECT cikk_kod, cikknev, eladar-beszerar Árrés FROM cikk;
Példa: Jelenítsük meg a cikknevet, és a cikkkód első karakterét! SELECT cikknev, substr(cikknev, 1, 1) FROM cikk;
Feladat 3: Feladat 4: Feladat 5: 2.4.2.
Kérdezzük le a cikkek összes adatát! Kérdezzük le az összes cikk nevét, és gyártókódját! A cikknév névként jelenjen meg! Kérdezzük le a különböző gyártókódokat a cikkek táblából! (Distinct)
Rendezés
Általános alak: SELECT ... FROM... ORDER BY mezőkifejezés [{ASC|DESC}][,mezőkifejezés [{ASC|DESC}]]...
A megadott mezőkifejezések alapján rendezve jeleníti meg a sorokat. Az ASC növekvő rendezettséget jelent, ez az alapértelmezett, ezért nem kötelező kiírni. A DESC csökkenő sorrendben rendezi a sorokat. Több kifejezés alapján is lehet rendezni. Ilyenkor a felírás sorrendjében legelső kifejezés alapján kerülnek rendezésre a sorok, majd az adott kifejezésen belül egyező értékek a következő kifejezés alapján rendeződnek, stb. Példa: Kérdezzük le a cikk tábla összes sorát, gyártó szerint növekvő sorrendben. Az azonos gyártóhoz tartozó termékek neve csökkenő sorrendben jelenjen meg! SELECT * FROM cikk ORDER BY gyarto ASC, cikknev DESC;
Hatására a sorok az olyan rendezettséget mutatnak, mint ami az alábbi táblában látható: cikk_kod AAG AAF AAC AAB AAE AAA AAD
Feladat 6: Feladat 7: Feladat 8:
cikknev Tej Málnás joghurt Banásnos joghurt Sóspálcika Kakaós keksz Kakaópor Majonéz
gyarto 1 1 1 2 2 3 4
beszerar 100 30 40 20 30 150 500
eladar 200 50 60 30 50 280 1000
Kérdezd le a bűnözők összes adatát név szerint növekvő sorrendben! Írasd ki a bűnözők adatait úgy, hogy a nők jelenjenek meg előbb. A nevek legyenek növekvő sorrendben! Jelenítsük meg a Lecsukva tábla tartalmát, szig és tol szerint csökkenőleg rendezve!
2.4.3.
Sorok leválogatása
A WHERE záradék megadásával csak azok a sorok kerülnek leválogatásra, melyek a WHERE után álló logikai kifejezésnek eleget tesznek. A logikai kifejezés természetesen több részfeltételből is állhat, melyeket logikai operátorokkal (AND, OR, NOT) kapcsolhatunk össze. A részfeltételek a FROM után álló táblák bármelyik attribútumát, konstanst, függvényhívást, halmazt, vagy konstanst illetve halmazt tartalmazhat a típusoknak megfelelő operátorokkal összekapcsolva. Példa: Jelenítsük meg a 100 Forintnál drágábban árult termékeket név szerint rendezve! SELECT * FROM cikk WHERE eladar > 100 ORDER BY cikknev;
Példa: Jelenítsük meg az 50 Ft-nál drágábban árult, de 40 Ft-nál olcsóbban vett termékeket! SELECT * FROM cikk WHERE eladar > 50 AND beszerar < 40;
Példa: Jelenítsük meg azon cikkeket, ahol a gyártó szerepel a '00', '01', '05' halmazban. SELECT * FROM cikk WHERE gyarto in ('00', '01', '05'); SELECT * FROM cikk WHERE gyarto = '00' OR gyarto = '01' OR gyarto = '05';
Feladat 9: Feladat 10: Feladat 11: Feladat 12: Feladat 13: Feladat 14:
Jelenítsük meg azon börtönöket, ahol több mint 800 rab fér el! Írasd ki a férfi bűnözőket! Jelenítsük meg a koedukált börtönök nevét kapacitás szerint csökkenőleg rendezve! Jelenítsük meg azon budapesti börtönöket, ahol fiatalkorúakat tartanak fogva! Jelenítsd meg a tisztán női, és tisztán férfi börtönöket! Írasd ki azokat a börtönöket, ahol 500-nál több rab fér el, és nőket is fogadnak.
Null érték kezelése A null érték vizsgálatára külön kulcsszó áll rendelkezésre, az IS [NOT]. Amennyiben két érték összehasonlításánál az egyik operandus null érték, az eredmény is null értéket hordoz majd (pl: a > null, a <> null). Egy null értéket tartalmazó logikai kifejezés eredménye viszont csak abban az esetben lesz null, amennyiben a kifejezés értéke függne attól, hogy a null érték helyén igaz, vagy hamis érték szerepelne-e. A szelekciós feltétel null értéke esetén a sorok nem kerülnek leválogatásra. Példák: null IS null 1 = 0 AND null 1 = 0 OR null '' IS null 1 = null Is null
'igaz 'hamis (a kifejezés értéke nem függ a 2. tagtól) 'null (a kifejezés értéke függ a 2. tagtól) 'igaz 'igaz, mert 1 = null eredménye null
Feladat 15: Mely lekérdezések adnak vissza sort az alábbiak közül? 1. 2. 3. 4. 5. 6. 7.
SELECT SELECT SELECT SELECT SELECT SELECT SELECT
'True' 'True' 'True' 'True' 'True' 'True' 'True'
FROM FROM FROM FROM FROM FROM FROM
dual dual dual dual dual dual dual
where where where where where where where
null = null; null < 0; null = 0; 1 = null or 1 = 1; 1 = null and 1 = 1; not 1 = null; null is null;
8. SELECT 'True' FROM dual where 1 is not null; 9. SELECT 'True' FROM dual where '' is null; 10. SELECT 'True' FROM dual where 'a' is null;
A null érték kezelésére használható az Nvl függvény, mely első paraméterként egy attribútumnevet vár, második paraméterként pedig azt az értéket, melyet vissza akarunk kapni, ha az 1. paraméter az éppen vizsgált cellában null értéket vesz fel. Példa: Jelenítsük meg a cikk kódokat és neveket. Ahol nincs megadva név ott az 'ismeretlen' szó szerepeljen! select cikk_kod, nvl(cikknev, 'ismeretlen') from cikk;
Feladat 16: Jelenítsd meg az eladási árakat. Ahol nincs megadva ott beszerzési ár 120%-a álljon! Feladat 17: Írasd ki azokat a bűnözőket, akiknél nincs megadva, milyen betegségben szenvednek! 2.4.4.
Csoportok képzése (GROUP BY, HAVING) SELECT … FROM … [GROUP BY csoportosítókifejezés1 [, csoportosítókifejezés2]… ] [HAVING feltétel]
Az SQL lehetővé teszi, hogy a sorokat bizonyos tulajdonság alapján csoportosítsuk, és az egyes csoportokon számításokat végezzünk. Amennyiben nincs GROUP BY záradék, akkor az összes sor egyetlen csoportnak lesz tekintve. Egyébként a GROUP BY után álló kifejezések alapján történik a csoportképzés. Több csoportosító kifejezés esetén a legelső szerint történik első körben a csoportosítás, majd az egyes csoportokon belüli történik újabb csoportosítás a következő kifejezés alapján. A HAVING megadása esetén a csoportosítás után előállt sorok közül csak a feltételnek megfelelő sorok kerülnek az eredményhalmazba. Az attribútumlistában a csoportosítás alapján képező kifejezések, konstantkifejzések valamint összesítőfüggvények vagy azokkal alkotott kifejezések szerepelhetnek. Olyan attribútum azonban nem szerepelhet csoportosító függvényen kívül, mely a csoportosításban nem vesz részt. Összesítő függvények:
avg(kifejezés): A csoport összes sorára kiértékeli a kifejezést, és ezek átlagát adja vissza. min(kifejezés): A csoport összes sorára kiértékeli a kifejezést, és ezek minimumát adja vissza. max(kifejezés): A csoport összes sorára kiértékeli a kifejezést, és ezek maximumát adja vissza. sum(kifejezés): A csoport összes sorára kiértékeli a kifejezést, és ezek összegét adja vissza. count({attribútumsorszám|attrinév}): Megszámolja a csoportban szereplő nem null értékeket. Gyakran használjuk count(distinct attributum) formában, mely a különböző értékek számát adja vissza. count(*): Visszaadja a csoportban szereplő sorok számát.
Az avg, min, max, sum a null értékeket figyelmen kívül hagyják. Példa: Határozzuk meg a termékek átlagárát!
select avg(eladar) from cikk;
Példa: Mennyibe kerül a boltban a legdrágább termék? select max(eladar) as Legdrágább from cikk;
Példa: Mennyi az egyes gyártóktól rendelt termékek átlagos beszerzési ára? select nvl(gyarto, 'ismeretlen') , avg(beszerar) from cikk group by gyarto;
Példa: Írassuk ki gyártónkénti bontásban a maximális árat, ha az meghaladja a 100 Ft-ot! select nvl(gyarto,'ismeretlen') , max(beszerar) from cikk group by gyarto having max(beszerar) > 100;
Példa: Hány terméket rendelünk a „00” kódú gyártótól? select count(cikk_kod) from cikk where gyarto = '00';
Példa: Készítsünk kimutatást korosztály (10-20, 20-30,...) és nem szerinti bontásban a bűnözők számáról! select CONCAT( CONCAT(TO_CHAR(round( (sysdate - szuldatum) / 3650 )*10), '-'), TO_CHAR(round( (sysdate - szuldatum) / 3650 )*10+10) ) as Korosztály, nem, count(1) Fő from bunozok group by round( (sysdate - szuldatum ) /3650) , nem order by Korosztály, nem;
Az alábbi táblázat a parancs kimenetének első pár sorát mutatja. A táblázatból leolvasható, hogy a 10-20 éves korosztályban egyetlen férfi bűnöző volt, női bűnöző ebben a korosztályban nincs nyilvántartva. A 20-30 éves korosztálynál már férfi, és női bűnözőket is találunk, csak úgy, mint a 3040 illetve a 40-50 évesek között. Korosztály 10-20 20-30 20-30 30-40 30-40 40-50 40-50 Feladat 18: Feladat 19: Feladat 20: Feladat 21: Feladat 22: Feladat 23: Feladat 24:
Nem F F N F N F N
Fő 1 6 4 3 5 1 1
Hány éves a legidősebb nyilvántartott bűnöző? Írasd ki a maximális óradíjat a munkák táblából! Hányféle külső illetve belső munkát végezhetnek a rabok? Hány bűnöző neve kezdődik K betűvel? Mennyi a rabok átlagos életkora? Hány gyártótól származnak a boltban lévő cikkek? (Csak az ismert gyártókat számold)! Hány cikkhez nincs megadva gyártó? (null érték szerepel)
2.4.5.
Táblák összekapcsolása
Korábban már volt arról szó, hogy az összetartozó adatok, nem feltétlenül egy táblában vannak tárolva az adatbázisban. Azt is tudjuk már, hogy a táblákban lehet külső kulcs, melynek az értékei egy másik táblabeli kulcsértékeknek felelnek meg, így a külső kulcs alapján egyértelműen kiderül, mely sorok tartoznak össze a két táblából. Direkt szorzat + where feltétel A kereszt szorzat létrehozása a táblakifejezések közé írt vesszővel, vagy a CROSS JOIN kulcsszóval lehetséges. SELECT ... FROM táblakifejezés { , |CROSS JOIN} táblakifejezés }
Hatására egy olyan eredménytábla jön létre, melyben szerepel a bal oldalon álló tábla összes attribútuma, majd a jobb oldalon álló tábla összes attribútuma. Ha az attribútumok között névegyezőség volt, akkor a bal oldali tábla attribútumnevei kerülnek megváltoztatásra. Az eredménytáblában a két tábla sorainak összes lehetséges kombinációja pontosan egyszer jelenik meg. A fenti szintaktikát WHERE záradékkal kiegészítve gondoskodhatunk arról, hogy a halmazból csak a számunkra szükséges sorok maradjanak meg. Összekapcsolás esetében ez azt jelenti, hogy a külső kulcs attribútuma és a kapcsolódó tábla hivatkozott oszlopának (általában az elsődleges kulcs) megfelelő relációját írjuk elő. (Megj.: az rendszer nem fogja előállítani az összes kombinációt, ha nem kell.) Példa: Jelenítsük meg a cikkek és gyártók sorainak összes lehetséges kombinációját! SELECT * FROM cikk CROSS JOIN gyartok;
Példa: Jelenítsük meg a cikkek és a cikkek gyártójának neveit! SELECT cikknev, gyartok.nev FROM cikk CROSS JOIN gyartok WHERE gyarto = gyartokod;
Belső összekapcsolás SELECT ... FROM táblakifejezés INNER JOIN táblakifejezés ON feltétel
Az eredményét tekintve a belső szorzat megegyezik az alábbi direkt szorzatos változattal, de nem állít elő direkt szorzatot. Ha : SELECT ... FROM táblakifejezés, táblakifejezés WHERE feltétel
A feltétel csak úgy, mint a WHERE záradékban tetszőleges logikai kifejezés lehet, használható konstans, függvényhívás, attribútumnév, akár algebrai műveletekkel együtt is. De az ON után lehetőleg csak olyan feltételt írjunk, mely a két tábla összekapcsolásához valóban szükséges. (A feltételben mindkét tábla attribútumai szerepeljenek valamilyen operátorral összekapcsolva.)
Példa: A boltban új nyilvántartási rendszer bevezetésére készülnek, melyhez ellenőriznünk kell, hogy a cikk kódok hosszabbak legyenek minden gyártókódnál. Jelenítsük meg azon termékek nevét, ahol a kódot ki kell cserélni? (A hosszba a jobb oldali szóközök ne számítsanak bele.) SELECT DISTINCT * FROM cikk INNER JOIN gyartok ON length ( rtrim(cikk_kod) ) <= length ( rtrim(gyartok.kod) );
ROSSZ Példa, ne legyen ragadós: Kapcsoljuk össze a cikk és gyártók tábla azon sorait, ahol a gyártókód nullával kezdődik! SELECT cikknev, gyartokod, gyartok.nev FROM cikk JOIN gyartok ON substr(gyartokod, 1, 1) = '0';
Helyette javasolt olyan megoldást használni, ahol első ránézésre is látszik, hogy a feltétel csak az egyik táblára vonatkozik, és itt valójában direktszorzatról van szó. Ehhez előbb lekérdezzük azon sorokat a gyártók táblából, ahol a gyártókód nullával indul: select * from gyartok where substr(gyartokod, 1, 1) = '0'
Majd ezt táblaként használva előállítjuk a direkt szorzatot: SELECT cikknev, gyartokod, nev FROM cikk, (select * from gyartok where substr(gyartokod, 1, 1) = '0');
Külső összekapcsolás Láthattuk, hogy a belső összekapcsolás esetén csak azok a sorok jelennek meg az eredmény táblában, melyekhez van kapcsolódó sor a másik táblából. Ez viszont információvesztést eredményezhet. Tegyük fel, hogy van egy Rendelesek( rendelesId, ugyfelId, cikk_kod, mennyiseg, datum) táblánk, melyből az egyes termékek iránti keresletet az elmúlt időszakra meghatározhatjuk. Jelenítsük meg a azt a három terméket, melyből a legkevesebb fogyott. Bár a lekérdezést a mostani tudásunkkal még nem tudjuk megoldani, azt tudjuk, hogy a két táblát inner joinnal összekapcsolva, azok a termékek nem is jelennének meg az eredménytáblában, amit senki sem rendelt az elmúlt időszakban, pedig pont ezeket a termékeket lenne értelme lecserélni. Ahhoz, hogy azok a sorok is megjelenjenek, melyhez nincs kapcsolódó sor a táblából külső összekapcsolást kell végeznünk. Általános alakja: SELECT ... FROM táblakifejezés {LEFT|RIGHT|FULL} OUTER JOIN táblakifejezés ON feltétel
Baloldali külső összekapcsolás (LEFT OUTER JOIN) A bal oldalon álló tábla minden sora bekerül az eredménytáblába a jobb oldali tábla összes olyan sorával, mely a feltételnek eleget tesz. Ha nincs kapcsolódó sor a jobb oldali táblából, akkor a sor úgy kerül be az eredménytáblába, hogy a jobb oldali táblához tartozó attribútumok null értékkel töltődnek fel. Jobboldali külső összekapcsolás (RIGHT OUTER JOIN)
Ugyan az, mint a baloldali összekapcsolás, de itt a jobboldali tábla összes sora kerül be az eredménytáblába, vagy a hozzá kapcsolódó sorral, sorokkal, vagy annak hiányában a baloldali táblát alkotó attribútumokon null értékekkel. Kétoldali külső összekacsolás (FULL OUTER JOIN) Mindkét táblából megjelenik minden sor a kapcsolódó soraikkal, valamint mindkét oldalon megjelenik az összes kapcsolódó sorral nem rendelkező sor egyszer, melyek a másik tábla attribútumain null értékekkel töltődnek fel. Példa: Jelenítsük meg, hogy a gyártók mely termékeit forgalmazza a boltocska. Azok a gyártók is szerepeljenek, akiktől semmit sem forgalmaz. SELECT gyartokod, nev, cikk_kod, cikknev FROM gyartok LEFT OUTER JOIN cikk ON gyarto = gyartokod; vagy SELECT gyartokod, nev, cikk_kod, cikknev FROM cikk RIGHT OUTER JOIN gyartok ON gyarto = gyartokod;
Equi join (Egyenlőségen alapuló összekapcsolás) A belső összekapcsolás olyan speciális esetének tekinthető, amikor a táblák kapcsolódó attribútumainak páronkénti egyenlőségét írjuk elő. Természetesen ebben az esetben is használható az INNER JOIN a megszokott szintaktikával, de ha a kapcsolódó attribútumok nevei megegyeznek, akkor lehetőségünk van tömörebb forma alkalmazására is. Az Oracle ebben az esetben csak az egyik táblából hagyja meg a kapcsolódó attribútumot. SELECT … FROM táblakifejezés INNER JOIN táblakifejezés USING(attribútum)
Példa: Kérdezzük le mely bűnöző mettől meddig volt lecsukva! SELECT nev, tol, ig FROM bunozok INNER JOIN lecsukva USING(szig);
Natural join (Természetes összekapcsolás) A természetes összekapcsolás során a két tábla azonos nevű attribútumait tekintjük a kapcsolódó attribútumoknak, és hasonlóan az Equi joinhoz itt is az attribútumok egyenlőségét írjuk elő. A kapcsolódó attribútum-párok közül csak az egyik tag jelenik meg az eredménytáblában. Természetesen ezzel most sem veszítünk információt, hiszen a feltétel alapján ezen attribútumok neve és tartalma is azonos volt. Csupán a redundanciát szüntettük meg. Példa: Kérdezzük le mely bűnöző mettől meddig volt lecsukva! SELECT nev, tol, ig FROM bunozok NATURAL JOIN lecsukva;
Feladat 25: Feladat 26: Feladat 27: Feladat 28: Feladat 29: Feladat 30:
Írasd ki a bűnözők adatait, és börtöneik nevét! Írasd ki Kés Elek, és Enyves Béla milyen bűncselekményeket követett el? Írasd ki hogy, mely bűnözők vannak jelenleg fogva tartva! Enyves Béla melyik börtönben volt fogva tartva 1962.01.01-en? Börtönben volt-e Kés Elek 1971-ben? Ha igen írd ki a nevét! Írasd ki a rabok magaviseletére kapott pontszámokat! Ügyelj a NULL értékekre!
Feladat 31: Melyik börtönben vannak olyanok, akik hűtlen kezelést követtek el? Feladat 32: Mi a különbség a két lekérdezés között? SELECT * FROM Bunozok b inner join mb_kapcsolat mb on b.szig = mb.szig; SELECT * FROM Bunozok b left outer join mb_kapcsolat mb on b.szig = mb.szig;
Feladat 33: Írasd ki a bűnözők foglalkozását! Ha valaki nem dolgozik ott a „munkanélküli” szó álljon. 2.4.6.
Beágyazott lekérdezések
Az SQL nyelv lehetővé teszi a SELECT utasítások egymásba ágyazását, illetve később látni fogjuk, hogy más utasításokba is ágyazhatunk be SELECT utasításokat. Az egymásba ágyazások maximális száma 255, ez tényleges korlátozást aligha jelent. Az alszelekteknek alapvetően két fajtáját különböztethetjük meg aszerint, hogy a külső és belső SELECT milyen kapcsolatban áll egymással: Korrelálatlan (egyszerű) alszelekt Amennyiben a beágyazott SELECT önmagában is értelmezhető (nem függ a külső SELECT utasítástól), korrelálatlan alszelektről beszélünk. Ebben az esetben a teljese SELECT utasítás kiértékelése során először az alszelekt értékelődik ki és az általa előállított értékkel kerül át a külső SELECT utasításhoz, ami ez után értékelődik ki. Az alszelekt tehát egyetlen egyszer fut le. Korrelált alszelekt A beágyazott SELECT önmagában nem értékelhető ki, mert hivatkozik valamely külső SELECT utasításban szereplő attribútumra (kifejezésre). Korrelált vagy más néven kapcsolt alszelekt esetén a külső selectnél kezdődik a kiértékelés, átadja a hivatkozott értéket a belső selectnek mely előállítja az értékekhez tartozó eredményt. Ezután a belső select által előállított értékkel folytatódik a külső select kiértékelése. Az alszelekt tehát újra, és újra lefut, minden átadott értéknél. Ha a külső és a belső select azonos táblára vonatkozik akkor az egyik táblát át kell nevezni, hogy a minősített nevek különbözzenek egymástól. Alselectek kezelése Az alszelektet mindig zárójelek közt kell szerepeltetni. A lekérdezések során minden alszelektet eredményét kezelhetjük táblaként, vagyis bárhol szerepelhet, ahol korábban a táblakifejezés szót láthattuk a szintaktikában. Példa: Kérdezzük le azon gyártók kódját, akiktől rendelt (boltban forgalmazott) termékek átlagára 100Ft-nál nagyobb! (táblaként kezelt alszelekt, from kulcsszó után) select * from (select gyarto, avg(beszerar) atlagar from cikk group by gyarto ) where atlagar > 100;
Példa: Kérdezzük le azon cikkek nevét, gyártójának nevét ahol az árrés kisebb a beszerzési ár 30 százalékánál! (táblaként kezelt alszelekt egy összakapcsolásban) select cikknev, gyartok.nev from (select * from cikk where (eladar-beszerar) < beszerar*0.9) T inner join
gyartok on T.gyarto = gyartok.gyartokod;
Amennyiben az alszelekt feltételben szerepel, akkor aszerint kell kezelni hány sort illetve oszlopot adhat vissza az beágyazott lekérdezés. Egy sort (értéket) visszaadó alszelekt Ha a beágyazott SELECT egyetlen értéket (skalár értéket: 1 oszlop, 1 sor) állít elő – figyeljünk rá, hogy minden körülmények közt 1 értéket kell visszakapnunk a tábla tartalmától függetlenül, különben hiba léphet fel –, akkor úgy kezelhetjük, mintha a visszaadott érték típusának megfelelő konstanssal dolgoznánk. Ez azt jelenti, hogy szerepeltethetjük az attribútumlistán vagy szelekciós feltételben, szerepelhet például algebrai kifejezésekben, összehasonlításokban, de még függvények paramétere is lehet az alszelekt. Példa: Egy szöveg, ami minden cikk_kod mellett megjelenik: select cikk_kod, (select 'ez_minden_sorban_megjelenik' from dual) from cikk;
Példa: Oracle alatt függvényekben is szerpelhet alszelekt. select cikk_kod, concat( (select 'ossze' from dual) , (select 'is fuzheto' from dual)) from cikk;
Példa: Jelenítsük meg azon termékeket, ahol a beszerzési ár meghaladja az átlagos beszerzési ár kétszeresét! select cikknev from cikk where beszerar > 2*(select avg(beszerar) from cikk);
Példa: Jelenítsük meg az összes cikk mellett a minimális beszerzési árat. Mi sem egyszerűbb: select cikk_kod, cikknev, min(beszerar) from cikk; 'HIBA!!!
Jól néz ki? Talán... mégsem működik. Nem írhatunk olyan attribútumokat a csoportosító függvény mellé, ami nem szerepel a csoportosításban. Jelenleg semmi nem szerepel, hiszen nincs is GROUP BY záradék, ami után állhatna. De használhatjuk helyette alszelektet az attribútumlistában: select beszerar, (select avg(beszerar) from cikk) from cikk;
vagy képezhetjük a cikkek tábla és az átlag (mint egy oszlopból és egy sorból álló tábla) direktszorzatát: select beszerar, Atlag from cikk, (select avg(beszerar) Atlag from cikk);
Példa: Jelenítsük meg a cikknevek mellett a termék gyártóját! (korrelált alszelekt) select cikknev, (select nev from gyartok where gyartokod = cikk.gyarto) As Gyarto from cikk;
Figyeljük meg, hogy az aláhúzott rész bal oldali attribútuma a belső, a jobb oldali attribútuma a külső select utasításban lévő táblában szerepel. Ez okozza, hogy az alszelekt önmagában nem
kiértékelhető, hiszen ha kimásolnánk a zárójelek közti részt, és futtatni próbálnánk, akkor hibajelzést kapnánk, mivel a cikk.gyarto nem szerepel a gyartok táblában. Azt is megfigyelhetjük, hogy nincs csoportosító függvény a belső lekérdezésben, szintaktikáját nézve akár több értéket is előállíthatna. Jelen esetben azonban használhatjuk mivel a relációs séma alapján tudjuk, hogy minden cikk maximum egy gyártóval rendelkezhet. (Ha nem rendelkezik gyártóval, akkor null érték kerül a táblába. ) Halmazt visszaadó alszelekt Amennyiben a select utasítás 0, 1 vagy több sort is visszaadhat, az eredményt használhatjuk azt táblaként (a legutolsó példában láttuk, hogy ezt a skalárt visszaadó alszelektekkel is megtehettük), vagy ha feltételben szerepel alszelekt, akkor a következő halmazoperátorok segítségével kezelhetjük: ALL: Ezzel a kulcsszóval felírt feltétel akkor teljesül, ha az alszelekt által visszaadott összes sorra teljesül a kifejezés operátor kifejezés_2 feltétel. … kifejezés operátor ALL (select kifejezés_2 from …)
ANY: Ezzel a kulcsszóval feltétel akkor teljesül, ha az alszelekt által visszaadott sorok közül legalább egyre teljesül a kifejezés operátor kifejezés_2 feltétel. … kifejezés operátor ANY (select kifejezés_2 from …)
IN: Ezzel a kulcsszóval felírt feltétel akkor teljesül, ha az IN előtt álló kifejezés, vagy zárójelbe tett kifejezések szerepelnek az alszelekt által visszaadott sorok közt. … kifejezés1 … IN (select kifejezés2… from …) … (kifejezés_1, … , kifejezés_n) … IN (select kifejezés_1…kifejezés_n from …)
EXISTS: Ezzel a kulcsszóval felírt feltétel akkor teljesül, ha az utána álló alszelekt legalább 1 sort visszaad. … EXISTS(select kifejezés2… from …)
A NOT kulcsszót az IN és az EXISTS előtt használhatjuk közvetlenül: NOT IN, NOT EXISTS. A többi esetben csak az egész kifejezést tudjuk tagadni a NOT szóval. … kifejezés operátor NOT IN (select kifejezés_2 from …) … NOT ( kifejezés operátor ANY (select kifejezés_2 from …) )
Természetesen minden operátornál elvárás, hogy az összehasonlítandó értékek típusa és száma egymásnak megfelelő legyen! Példa: Jelenítsük meg azon cikkeket, amelyhez tartozik gyártó! select * from cikk where gyarto in (select DISTINCT gyartokod from gyartok);
Ugyan az korrelált alszelekttel: select * from cikk where exists (select gyartokod from gyartok where cikk.gyarto = gyartok.gyartokod);
Példa: Jelenítsük meg azokat a „tejitalok”-at, melyeket többért szerzünk be, mint valamelyik normális „tej”-et. (A Lower függvény kisbetűssé alakítja a paraméterét) select * from cikk where LOWER(cikknev) LIKE '%tejital%' and beszerar > ANY (select beszerar from cikk where LOWER(cikknev) LIKE '%tej%');
Példa: A boltocskába minden termék kódjának első karaktere a termék típusát írja le (pl.: axx-tej, Ajoghurt, bxx-sajt, Bxx-vaj, stb, ahol xx tetszőleges karakter). A kimutatásban minden terméknél látni akarjuk, hogy mennyivel kerül többe a beszerzése, mintha az azonos típusú legolcsóbb terméket vásárolnánk. select cikk_kod, cikknev, beszerar - (select min(beszerar) from cikk where substr(cikk_kod, 1, 1)=substr(c.cikk_kod, 1, 1)) from cikk c;
vagy: select cikk_kod, cikknev, beszerar - minar from cikk inner join ( select substr(cikk_kod, 1, 1) as tipus, min(beszerar) as minar from cikk group by substr(cikk_kod, 1, 1) ) on substr(cikk_kod, 1, 1) = tipus;
Az első megoldás korrelált eset. Minden egyes termékre külön-külön lefut az alszelekt, ami megadja mennyi a minimális beszerzési ár az adott terméktípusnál. A második megoldásnál előbb minden terméktípusra kiszámítjuk a minimális értékeket, majd ennek eredményét táblaként kezelve hozzákapcsoljuk a cikkek táblához az első karakter alapján. Beágyazott lekérdezéssel oldjuk meg a következő feladatokat! Feladat 34: Feladat 35: Feladat 36: Feladat 37: Feladat 38: Feladat 39: Feladat 40: 2.4.7.
Mely „tejitalok”-at szerezzük be drágábban, mint az összes „tej”-et! Írassuk ki azon bűnözőket, akik még sosem kerültek börtönbe! Mely férfi bűnözők voltak külső munkán 1980.01.01 után? (TO_DATE) Kik követtek el gyilkosságot? Írasd ki azokat a bűnözőket, akik magaviseletre kaptak pontot. Kik vannak szabadlábon a bűnözők közül? (Nincs börtönben, sem előzetesben.) Írasd ki „EXISTS”-el azokat a bűnözőket, akik börtönhöz vannak rendelve!
Első n sor lekérdezése
A ROWNUM egy pszeudo-oszlop, mely azt adja meg hányadikként olvasta ki az Oracle a táblából vagy a halmazból az adott sort. Olyankor használható, amikor egy táblának, vagy halmaznak a legelső valahány sorára van szükségünk. A peszudo-oszlop azt takarja, hogy az oszlop fizikailag nincs tárolva, de úgy kezelhetjük mint, minden más attribútumot. A számozás 1-től indul, és egyesével növekszik. Példa: Jelenítsük meg a cikkek tábla adatait sorszámmal ellátva a sorokat. select ROWNUM, cikk.* from cikk;
A ROWNUM használatának leginkább rendezett adatoknál van értelme, ezért gyakran egy olyan select where feltételeként jelenik meg, mely egy alszelektet tartalmaz az adatok rendezésére.
Példa: Jelenítsük meg a maximális eladási áru cikket! (Egyezés esetén a kod szerint kisebbet.) select * from (select cikk.* from cikk order by nvl(eladar,0) DESC, cikk_kod) where rownum <= 1;
2.4.8.
Összetett lekérdezések
Példa: Írjuk ki azon gyártó(k) nevét, mely(ek)nek a legtöbb termékét forgalmazza a boltocska (az ismeretlen gyártók nem érdekesek). Az összetettebb feladatokat érdemes részfeladatokra bontani. Attól a résztől kell kiindulni, mely önmagában is meghatározható. Ebben a példában az első lépés a gyártók által forgalmazott termékek darabszámának meghatározása: select count (*) from cikk where gyarto is not null group by gyarto
-- csak az ismert gyártójú cikkeket számolja -- gyartonkent szamol
Vagy kihasználhatjuk azt, hogy a count függvény a null értékeket kihagyja a számításból, ha nem *-ot írunk a paraméter helyére. Ekkor a gyarto attribútumot kell paraméternek beírni, más nem jó! select count (gyarto) from cikk group by gyarto
-- ahol a gyarto null 0-at ad vissza -- gyartonkent szamol
Ezek után meg kell határoznunk ezek közül a maximálisat. Szerencsére az ORACLE megengedi hogy a csoportosított oszlopokon maximumot is számoljunk, ezért nem szükséges alszelektet használunk, ha a csoportosítás utáni eredmények közül kell a maximumot lekérdezni. Egész egyszerűen bővítsük ki a fentiek közül a szimpatikusabb megoldást egy max függvénnyel: select max( count(*) ) from cikk group by gyarto;
Már csak annyi dolgunk maradt, hogy megadjuk melyik gyártóhoz tartozik ez a maximum. Ehhez újra meghatározzuk azt, hogy melyik gyártótól hány terméket forgalmaz a boltocska - de most összekapcsoljuk a gyártó táblával is annak érdekében, hogy a gyártó neve is szerepeljen - és egy HAVING záradék segítségével kiválasztjuk azokat a sorokat, ahol a darabszám egyezik a maximális számmal. select gyartokod, nev from gyartok inner join cikk on gyarto = gyartokod group by gyartokod, nev having count(gyarto) = (select max ( count (gyarto)) from cikk group by gyarto);
Vegyük észre, hogy a gyartokod mellett a gyártók neve is bekerült a csoportosító mezők közé, az a kérdés, hogy megtehetjük-e ezt, vagy ez megváltoztatja a csoportokat (csoportokon belül több alcsoport áll majd elő). Tudjuk, hogy az egy csoportba tartozó sorokban a gyartokod azonos, hiszen e szerint csoportosítottuk a sorokat. Alcsoportok akkor jelennek meg, ha egy csoporton belül a másik csoportosító mező alapján többféle érték is előfordul, vagyis ha egy gyártó kódjához több gyártónév is tartozna. Ez viszont nem lehetséges, hiszen a gyartokod az elsődleges kulcs, ami egyértelműen azonosítja a gyártót. (Általános esetben is igaz, ha egy tábla elsődleges kulcsa alapján csoportosítunk,
akkor a tábla tetszőleges attribútuma felvehető a csoportosító kifejezések közé az elsődleges kulcs után, anélkül, hogy az a csoportosítás eredményét befolyásolná. Az elsődleges kulcs szerinti csoportosításnak akkor van értelme, ha korábban összekapcsoltuk más táblával.) Példa: Mely bűnöző(k) voltak legtöbbször lecsukva? Hasonlóan az előző példához most is a számlálással kezdünk, vagyis meghatározzuk ki hányszor volt lecsukva: select szig, count(1) from Lecsukva group by szig;
Válasszuk ki ebből a maximumot! Itt fájó szívvel elbúcsúzunk attól az információtól kikhez tartozik a maximum, hiszen a max függvény a számlálás utáni összes soron hajtódik végre (nincs csoportosítás), ezért nem írható mellé a személyi igazolvány számokat tartalmazó oszlop. select max ( count(1) ) Lecsukva group by szig;
És most visszaszerezzük azt az információt, melyet az előbb elvesztettünk. Ehhez újból csoportosítjuk a lecsukva tábla sorait a személyi igazolványszám alapján, és leválogatjuk azokat a csoportokat, ahol a maximummal egyező a sorok száma: select szig from Lecsukva group by szig having count(1) = (select max( count(1)) from Lecsukva group by szig);
Már csak azt kell kiderítenünk kihez tartozik ez a szig. szám. Ehhez vagy az előbbihez hasonlóan összekapcsoljuk a táblákat, vagy alszelekttel oldjuk meg a feladatot: select * from bunozok where szig in ( select szig from Lecsukva group by szig having count(1) = (select max( count(1)) from Lecsukva group by szig) );
A maximum kiválasztás helyett használhatjuk a következő halmaz operátort is: select * from bunozok where szig in ( select szig from Lecsukva group by szig having count(1) >= ALL (select count(1) from Lecsukva group by szig) );
Példa: Az öt legolcsóbb (beszerzési ár) termék közül hányat gyárt a Bali? Első lépésként rendezzük a beszerzési árakat növekvő sorrendben: select beszerar from cikk order by beszerar;
Válasszuk ki az első 5 beszerzési árat (elég csak a különbözőket megtartani, de az ismétlődés se gond): select distinct beszerar from (select * from cikk order by beszerar) where rownum <= 5;
Most nézzük meg, hány olyan Bali termék van, aminek a beszerzési ára benne van az előbb előállított halmazban: select * from cikk inner join gyartok on gyarto=gyartokod where nev = 'Bali' and beszerar in ( select distinct beszerar from (select * from cikk order by beszerar) where rownum <= 5 ) ;
Példa: Jelenítsük meg melyik típusú terméknél mennyi eltérés van a legjobb és a legrosszabb beszerzési ár közt. (Emlékeztetőül: a termék típusát a cikk kódjának első karaktere hordozza (kis és nagybetű eltérő)) select max(beszerar)-min(beszerar) , from cikk group by substr( cikk_kod, 1, 1);
Példa: Jelenítsük meg egy termék típuson belül melyik(ek) a legolcsóbb, és melyik(ek) a legdrágább cikk(ek). Határozzuk meg az előzőhöz hasonlóan a legrosszabb beszerzési árakat minden típushoz: select substr(cikk_kod, 1, 1) T, max (beszerar) Ar from cikk group by substr(cikk_kod, 1, 1);
Kapcsoljuk hozzá a legjobb beszerzési árakat is: select substr(cikk_kod, 1, 1) T, max (beszerar) Ar from cikk group by substr(cikk_kod, 1, 1); union all select substr(cikk_kod, 1, 1) T, min (beszerar) Ar from cikk group by substr(cikk_kod, 1, 1);
Most már nincs más dolgunk, csak összekapcsolni a cikkek táblával a kapott halmazt, hogy megtudjuk melyik cikkhez tartozik a típus, és a beszerzési ár páros. A jobb átláthatóság miatt egyúttal rendezzük is a sorokat: select T, cikk_kod, nvl(cikknev, 'nevtelen'), beszerar from cikk inner join (select substr(cikk_kod, 1, 1) T, max (beszerar) Ar from cikk group by substr(cikk_kod, 1, 1) union all select substr(cikk_kod, 1, 1) T, min (beszerar) Ar from cikk group by substr(cikk_kod, 1, 1) ) on substr(cikk_kod, 1, 1) = T and beszerar = Ar order by T, beszerar;
Természetesen előfordulhat, az is, hogy ugyan azok a termékek a legolcsóbbak és a legdrágábbak is egyben. Az összekacsolás helyett szintén alkalmazhatjuk az in operátort is, hiszen érték n-esek tartalmazását is vizsgálhatjuk vele. A különbség annyi lesz, hogy úgy egy cikk csak egyszer kerül be a táblába. select substr(cikk_kod, 1, 1) T, cikk_kod, nvl(cikknev, 'nevtelen'), beszerar from cikk where ( substr(cikk_kod, 1, 1), beszerar ) in (select substr(cikk_kod, 1, 1), max (beszerar) from cikk group by substr(cikk_kod, 1, 1) union all select substr(cikk_kod, 1, 1), min (beszerar) from cikk group by substr(cikk_kod, 1, 1) ) order by T, beszerar;
Feladat 41: Feladat 42: Feladat 43: Feladat 44: Feladat 45: Feladat 46: Feladat 47: Feladat 48: Feladat 49: Feladat 50:
Hány bűnöző volt több alkalommal lecsukva? Névsor szerint rendezve írasd ki! Kik voltak többször is lecsukva? Lecsukás és névsor szerint rendezve írasd ki! Melyik munkát végezték a legtöbbször a bűnözők? Melyik munkát végezték a legtöbben a bűnözők közül? Ki a legidősebb bűnöző? (elég egyet megjeleníteni) Ki a legidősebb bűnöző? (azonosság esetén minden nevet írjon ki) Bedi Lizett mennyit keresett összesen a börtönévei alatt végzett munkával? Melyik börtönben lehet az átlagosnál több rabot fogva tartani? Mit követett el az a bűnöző, aki a leghosszabb börtönbüntetést kapta? Ki volt leghosszabb ideig börtönben, ha az összesen hűvösön töltött éveket tekintjük?
3. Függelék 3.1.
Boltocska kezdetleges cikk nyilvántartó rendszere
Cikk Cikk_kod Cikknev Gyarto Beszerar Eladar
Char(3) Varchar2(40) Char(2) Numeric(6) NOT NULL Numeric(6)
Gyartok Gyartokod Gyartonev
Char(2) Varchar2(40) NOT NULL
Cikkek: cikk_kod: A termékek egyeni azonosító száma. Első karaktere típusmegjelölésként használt. gyarto: Külső kulcs. Meghatározza melyik terméket melyik gyártótól rendeli a bolt. A nyilvántartás nem teljes, ezért még előfordulnak null értékek. beszerar: A termék beszerzési ára eladar: A termék eladási ára. Még nincs mindenhol feltöltve, ezért itt is vigyázni kell a null értékek kezelésére! A beszerzési ár mindig kisebb vagy egyenlő, mint az eladási ár. Valamint nem szerepelhet ugyanattól a gyártótól két egyforma nevű termék.
3.2.
Fiktív bűnügyi nyilvántartó rendszer séma
A táblákat, és a köztük lévő kapcsolatokat az alábbi ábra tartalmazza. A táblázatok fejléce a tábla adatbázisbeli nevét tartalmazza. A sorok a táblában szereplő mezőket és azok típusát valamint néhány esetben a hozzájuk kötődő megszorításokat írják le. A félkövér mezők a tábla elsődleges kulcsát jelölik. Munkak Munkaid Megnevezes Kulsomunka Fizetes Munkaido
Dolgozik Szig Tol Ig Mit
Number(4) Varchar2(50) Char(1) (I, N) Number(5) Number(2)
Char(8) Date Date Number(4)
Bunozok Szig Nev Szuldatum Nem Betegseg Elozetes
Char(8) Varchar2(50) Date Char(1) (N, F) Varchar2(40) Char(1) (I, N)
Lecsukva Szig BortonId Buntenyid Tol Ig Magaviselet
Char(8) Number(2) Number (4) Date Date Number(2) (-1, 0, 1)
Borton BortonId Nev Varos Kapacitas Fiatalkoruak Nem
Number(2) Varchar2(80) Varchar(40) Number(4) Char(1) (I ,N) Char(1) (F, N, K)
Buntenyek Buntenyid Megnevezes MinBuntetes MaxBuntetes
Number (4) Varchar2(80) Number(2) Number(3)
Bunozok: A nyilvántartásba vett bűnözők adatainak tárolására szolgál. Szig: személyi igazolvány szám Borton: A magyarországi büntetés-végrehajtási intézetek adatainak tárolására szolgál. Kapacitás: a börtönben elhelyezhető rabok száma Fiatalkorúak: fiatalkorúak számára fenntartott intézmény Nem: F, N, K, rendre: férfi, női, koedukált börtön Buntenyek: A bűntények megnevezését, és a bűntények elkövetése esetén kiszabható minimális és maximális büntetés mértékét tartalmazza (években). Lecsukva: Ez a tábla tartalmazza ki (szig), mettől (tol) meddig(ig), hol (bortonid) volt lecsukva és mit (buntenyid) követett el. Magaviselet: -1, 0, 1 és null értéket vehet fel, pozitív magaviselet esetén a rabok előbb szabadulhatnak, illetve külső munkavégzésre is beoszthatók. Munkak: A börtönben illetve a börtönön kívül (külsőmunka = 'I') rabok által végezhető munkák adatai. Fizetés: az adott munkához kapcsolódó bér. Jelenleg órabérben van megadva. Munkaidő: napi munkaórák száma. Dolgozik: Ez a tábla tartalmazza ki (szig), mikortól (tol), meddig(ig), milyen munkát (mit) végzett.
