Gyakorlati alapok III.
SQL (adatbázis)
SQL - csoport függvények
Folytatjuk az előző fejezetekben bemutatott SQL-függvények felhasználását egyre bonyolultabb lekérdezések és/vagy több tábla összevonása formájában. Néhány új SQL-függvény is fel fog merülni, ezek az előfordulásuk előtti bekezdésben kerülnek ismertetésre. Továbbra is kiindulópontunk az auto adatbázis, amely egy teljes, átfogó adatszerkezet egy autókereskedés és szervíz komplex, adatbázisos modellezésére.
Az auto adatbázis a következő további komponenesekből áll, ezen komponenseket táblák és a bennük definiált attribútumok (oszlopok) reprezentálják:
-
auto_csop tábla
-
auto_csop_nev
-
km_dij
-
napi_dij
-
-
tipusok tábla
-
tipus_nev
-
auto_csop_nev
-
leiras
-
szerviz_km
-
-
autok tábla
-
rendszam
-
tipus_nev
-
auto_csop_nev
-
vasarlas_datuma
-
ar
-
futott_km
-
ut_szerviz
-
allapot
-
reszleg_kod
-
alk_kod
-
-
rendeles tábla
-
ugyfel_szam
-
rendeles_datum
-
rendelo_szemely
-
kolcson_kezdete
-
napok
-
rendszam
-
tipus_nev
-
km_kezdet
-
km_veg
-
kolcson_dij
-
fizetes
-
-
ugyfelek tábla
-
ugyfel_szam
-
ugyfel_nev
-
cim
-
varos
-
orszag
-
iranyito_szam
-
megbizott
-
fizetesi_mod
-
-
reszleg tábla
-
reszleg_kod
-
reszleg_nev
-
reszleg_cim
-
-
alkalmazott tábla
-
alk_kod
-
alk_nev
-
beosztas
-
belepes
-
fizetes
-
premium
-
reszleg_kod
-
Erősen ajánlom, hogy az alábbi, néha rendkívül bonyolult SQL-lekérdezések előtt nagyon alaposan tanulmányozzuk át az auto adatbázis összes tábláját és rekordjait. Az eredményes SQL-lekérdezésekhez meg kell értenünk az autókereskedés összes modellezett munkakörülményét!
Függvény-előzmények:
-
Alapfüggvények és egyéb nyelvi elemek - CREATE, DROP, DELETE, INSERT, UPDATE, SELECT, COUNT, AVG, SUM, MAX, MIN, BETWEEN, IN, NOT IN, LIKE, NOT LIKE, ORDER BY (ASC, DESC), IS NULL, IS NOT NULL, operátorok, helyettesítő karakterek, egyéb szintaktikai elemek
-
karakteres függvények LOWER, UPPER, LENGTH, SUBSTR, RPAD, LPAD, RTRIM, LTRIM
-
numerikus függvények - ABS, ROUND, TRUNCATE, SQRT, POW, POWER, CEIL, CEILING, FLOOR, MOD, SIGN
-
dátum függvények - LAST_DAY, SYSDATE, DATE_ADD, DATE_FORMAT, PERIOD_DIFF, DAYNAME, STR_TO_DATE
További függvények:
A függvény a megadott szempontok szerint csoportosítja az eredménytáblát. Legtöbbször valamelyik csoportfüggvénnyel együtt használatos (MIN, MAX, SUM, AVG, stb.). Például kérdezzük le az átlagos kilométeróraállást az AUTOK táblában és csoportosítsuk az eredménytáblát autótípus szerint:
SELECT tipus_nev, AVG(futott_km) FROM autok GROUP BY tipus_nev
Végeredmény:
Ezen függvény legtöbbször a fenti GROUP BY függvénnyel együtt kombinálódik (bár bizonyos további függvényekkel is működőképes, például HAVING COUNT). Segítségével a GROUP BY után további csoportosítási feltételeket állíthatunk be. Például az UGYFELEK táblában számoljuk meg a készpénzzel fizető ügyfeleket:
SELECT COUNT(*) FROM ugyfelek GROUP BY fizetesi_mod HAVING fizetesi_mod <> 'A'
Végeredmény:
4
Feladatok
1 - Irassa ki az alkalmazottak táblájából a minimális, a maximális, az
összes és az átlagfizetést, valamint a létszámot!
2 - Irassa ki a részlegkódot, a minimális, a maximális, az összes és az átlagfizetést, valamint a létszámot részlegenkénti csoportosításban!
3 - Irassa ki a beosztást, a minimális, a maximális, az összes és az átlagfizetést, valamint a létszámot beosztásonkénti csoportosításban!
4 - Irassa ki a részlegkódot, átlagfizetést azokra a részlegekre, ahol az átlagfizetés 8000 Ft-nál nagyobb!
5 - Irassa ki azon a részlegek kódját, ahol pontosan ketten dolgoznak!
6 - Írjon SELECT parancsot, amely megadja a legmagasabb beszerzési árat!
7 - Írjon olyan SELECT parancsot, amely az AUTOK táblából kiírja a különböző autócsoportok nevét és a csoportban lévő autók számát!
8 - Írjon SELECT parancsot, amely a készpénzzel fizető ügyfelek létszámát kiírja! Használja a HAVING utasításrészt azon ügyfelek kiírására, akik nem átutalással fizettek!
9 - Irassa ki az AUTOK táblából a különböző típusokat, azt hogy melyikből hány darab van! Típusonként jelenjen meg az átlagos beszerzési ár! Az eredmény névsor szerint rendezve jelenjen meg!
10 - Irassa ki az AUTOK táblából az átlagos beszerzési árat!
11 - Irassa ki a legtöbbet futott autó adatait!
12 - Irassa ki a legkevesebbet futott autó adatait!
13 - Irassa ki, hogy az AUTOK táblában hány autó adatai szerepelnek!
14 - Irassa ki típusnevenként csoportosítva az átlagos kilométeróra állást!
15 - Írja ki az ALKALMAZOTT tábla alapján a maximális és minimális fizetések eltérését az átlagfizetéshez képest!
16 - Irassa ki típusnevenként csoportosítva a kiadható autók számát és átlagos beszerzési árát!
17 - Irassa ki, mennyi volt az átlagos kölcsönzési idő az 1993. március 30. utáni kölcsönzéseknél!
18 - Irassa ki a már kifizetett rendelések számát!
19 - Irassa ki azokat a megrendeléseket, ahol a kölcsönzés ideje alatt megtett kilométer a maximális, illetve a minimális!
20- Irassa ki a kölcsönzési idő alatt futott átlag kilométer értékeket!
21 - Számolja meg, hogy egy városban hány ügyfél van és rendezze a városnév szerint ABC-sorban!
Pénzes László