Gyakorlati alapok III.
SQL (adatbázis)
SQL - egymásba ágyazott 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
-
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
-
Csoport függvények - GROUP BY, HAVING (COUNT, AVG, SUM, MAX, MIN csoport függvényekkel kapcsolatban lásd az Alapfüggvények és egyéb nyelvi elemek című fejezetben)
-
Egyéb függvények - GREATEST, LEAST, CASE-WHEN-THEN
Egymásba ágyazott lekérdezések fő jellegzetessége, hogy voltaképpen mindig 2 (vagy több) kérdés van feltéve. Az 1. kérdés a kiindulópont, annak megválaszolása után tudunk válaszolni a 2. kérdésre. Például: Irassa ki azon dolgozók nevét és beosztását, akiknek a beosztása azonos 'HERCEG' beosztásával!
SELECT alk_nev,beosztas FROM alkalmazott WHERE beosztas=(SELECT beosztas FROM alkalmazott WHERE alk_nev='HERCEG')
Végeredmény:
Feladatok
1 - Készítsen listát azon dolgozók beosztásáról és fizetéséről, akiknek beosztása és fizetése azonos 'BOGNÁR' beosztásával és fizetésével!
2 - Irassa ki azon dolgozók nevét és fizetését, akik kevesebbet keresnek, mint az azonos beosztású kollégáik átlagfizetése!
3 - Irassa ki azon autók rendszámát, típus nevét és kilométeróra állását, melyek többet futottak, mint az ABR-115!
4 - Irassa ki azon ügyfelek nevét, címét, a várost, az országot, akik RENAULT 19 típusú autót kölcsönöztek!
5 - Irassuk ki az ügyfélszámot, nevet, várost az ügyfelek táblájából azokra, akiknél a rendelést a 'HOTEL CIVIS' adta fel!
6 - Irassuk ki a debreceni és budapesti megrendelők autóinak típusát, rendszámát, kölcsönzési idejének kezdetét!
7 - A rendelési táblából írjunk ki 'IGEN'-t, ha az ügyfél táblában 'PAPP ÁGNES' létezik!
8 - Készítsen listát azokról a megrendelésekről, amelyekben a kölcsönzött autó szervízelési intervalluma hosszabb, mint az átlagos szervízelési intervallum!
9 - Irassa ki azon ügyfelek nevét és ügyfél számát, akiknek fizetési módja megegyezik a 'MOLNÁR PÉTER' nevű ügyfél fizetési módjával!
10 - Irassa ki azon dolgozók nevét és kódját, akiknek beosztása szerepel a 'KÖZPONT' munkahelyen dolgozók beosztásai között!
11 - Készítsen listát azokról az alkalmazottakról, akiknek beosztása megegyezik a 'KIRÁLY' vagy a 'KOVÁCS' nevű dolgozók beosztásával!
12 - Irassa ki azoknak az ügyfeleknek a nevét és lakhelyét, akik az átlagos kölcsönzési díjnál magasabb díjat fizettek!
13 - Irassa ki azon ügyfelek nevét és ügyfél számát, akik olyan autót béreltek, amelyek jelenleg karbantartásra várnak!
Pénzes László