Gyakorlati alapok III.
SQL (adatbázis)
SQL - bonyolultabb lekérdezések
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
További függvények:
Ha egy rekord NULL értékű, akkor lekérdezéskor többféle zavart képes okozni. Ennek elhárítására használható ez a függvény, amellyel képesek vagyunk a NULL előfordulásokat problémamentesen lekezelni. Ennek megértésére nézzük meg az alábbi példát!
Legyen az alaplekérdezés az alkalmazottak neve, fizetése, prémiuma, fizetés szerint rendezve:
SELECT alk_nev, fizetes, premium FROM alkalmazott ORDER BY fizetes
Végeredmény:
Láthatjuk, hogy MOLNAR és TOTH nem kapott még prémiumot, premium rekordértékeik NULL.
Ha alkalmazottak teljes jövedelmére kérdezünk rá (fizetes + premium), akkor a NULL értékek miatt a lekérdezés MOLNAR és TOTH esetében már pontatlan lesz:
SELECT alk_nev, fizetes + premium as 'teljes jovedelem' FROM alkalmazott ORDER BY fizetes
Végeredmény:
Ilyen jellegű hibák lekezelésére alkották meg COALESCE függvényt, amely figyelembe veszi, egyúttal megfelelően le is kezeli a lekérdezést:
SELECT alk_nev, fizetes + COALESCE(premium,0) as 'teljes jovedelem' FROM alkalmazott ORDER BY fizetes
Végeredmény:
Feladatok
1 - Irassa ki az alkalmazottak nevét, beosztását és teljes jövedelmét (fizetés + prémium) névsor szerinti sorrendben!
2 - Irassa ki azokat a dolgozókat, akiknek fizetése 50000 és 100000 Ft közötti!
3 - Készítsen listát a 120000 és 200000 közötti kilométert futott autókról!
4 - Irassa ki azon dolgozók adatait, akiknek a beosztása TELEPHELYVEZETŐ vagy SZERELŐ!
5 - Irassa ki a 10, 20 vagy 60-as telepen dolgozók adatait!
6 - Irassa ki azon dolgozók adatait akiknek a neve 'H' betűvel kezdődik!
7 - Irassa ki az ALKALMAZOTT tábla tartalmát azokra, akiknek a nevében 'O' betű szerepel!
8 - Irassa ki azon alkalmazottak adatait, akik nevének második betűje az 'O' betű!
9 - Irassa ki azokat a részlegre vonatkozó adatokat, amelyek nevében nem szerepel az 'AUTO'!
10 - Irassa ki azon alkalmazottak adatait, akik a 10-es részlegben dolgoznak és beosztásuk ELADÓ, vagy nem kaptak prémiumot!
11 - Készítsen listát azon dolgozók adatairól, akik fizetése 60000 és 90000 Ft közötti és beosztásuk nem TELEPHELYVEZETŐ vagy ELADÓ!
12 - Készítsen listát azokról a dolgozókról, akik nem kaptak prémiumot!
13 - Irassa ki azon dolgozók adatait, akiknek a fizetése kisebb, mint a prémiumuk kétszerese!
14 - Irassa ki azokat a rendelés adatokat, amelyekben a kölcsönzés végén a kilométeróra állása legalább 1000-rel mutat többet, mint az elején vagy a kölcsönzött napok száma nagyobb 10-nél!
15 - Készítsen listát azon megrendelésekben tárolt adatokról, amelyekben a következő rendszámú autók közül kölcsönöztek: ABC-123, ABR-047, BAH-115, CDE-324!
16 - Irassa ki azon megrendelések adatait, amelyekben a kölcsönzött (nem kifizetett) autó típusnevében szerepel a RENAULT szó!
17 - Készítsen listát azon autók adatairól, amelyek kilométer díja 100 és 300 Ft között van és napi kölcsönzési díja kevesebb, mint 6000 Ft!
18 - Listázza ki azokat az autótípus jellemzőket, amelyek az E-vel kezdődő nevű autócsoportba tartoznak!
19 - Irassa ki azon autótípus adatokat, ahol a kötelező szervízek intervalluma 10000 és 15000 kilométer között van és az autó típusneve nem a RENAULT szóval kezdődik!
20 - Irassa ki az összes olyan megrendelés tételt, amely nincs kifizetve és a kölcsönzési idő 1994. január 1. előtt kezdődik vagy fizetve van és a kölcsönzés 1994. május 1. után esedékes!
Pénzes László