Gyakorlati alapok III.
SQL (adatbázis)
SQL - adatdefiníciós utasítások
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
Ebben a fejezetben a már ismert CREATE, ALTER, DROP függvények használatát gyakoroljuk összetettebb lekérdezések formájában.
További függvények:
A kifejezés voltaképpen egy önállóan létrehozott és elkülönített eredménytáblát jelent, amelybe tetszőleges lekérdezési eredményeket vezethetünk be. A phpMyAdmin-felületen az adatbázison belül az eredeti tábláktól elkülönítetten férhetünk hozzá a nézettáblákhoz (Nézetek):
Például legyen feladat, hogy az AUTO_CSOP tábla összes adatát egy külön nézettáblába vezessük:
CREATE VIEW AUTOCSOP_NEZET AS SELECT auto_csop_nev, napi_dij, km_dij FROM auto_csop
Végeredmény egy külön autocsop_nezet nevű nézettábla:
A függvény segítségével névátnevezéseket hajthatunk végre. Például nevezzük át a uj_reszl2 táblát reszleg2 táblára:
RENAME TABLE uj_reszl2 TO reszleg2
Végeredmény:
a tábla átnevezése
Általánosságban ez a mechanizmus felgyorsítja az adatok keresését és adatbázisból való kinyerését. A mechanizmus a felhasználók számára láthatatlanul dolgozik. Például lássuk el indexeléssel az AUTO_CSOP tábla auto_csop_nev nevű oszlopát:
CREATE INDEX index_nev ON auto_csop (auto_csop_nev)
Végeredmény az auto_csop_nev nevű oszlop indexelése, amelyet a Szerkezeti nézetben a név mellett egy kis kulcs szimbolizál:
Feladatok
1 - Hozza létre az alábbi szerkezetű, DOLGOZOK nevű táblát:
-
KOD VARCHAR(4) NOT NULL
-
NEV VARCHAR(30) NOT NULL
-
FIZETES INT
-
SZUL_DAT DATE
2 - Bővítse a DOLGOZOK táblát a CIM oszloppal, melynek típusa VARCHAR(30)! A NEV hosszát módosítsa 40-re!
3 - Hozza létre az UJ_RESZL1 nevű táblát, melynek szerkezete azonos a RESZLEG nevű tábla szerkezetével!
4 - Hozza létre az UJ_RESZL2 nevű táblát, melynek szerkezete és tartalma azonos a RESZLEG nevű tábla szerkezetével és tartalmával!
5 - Nevezze át az UJ_RESZL2 táblát RESZLEG2 névre!
6 - Hozza létre azt a NEZET nevű nézettáblát, amely az ALKALMAZOTT és a RESZLEG táblából csak az ELADÓ beosztású dolgozók kódját, nevét, beosztását, részlegének kódját, nevét és címét tartalmazza!
7 - Hozza létre azt a VIDEK nevű nézettáblát, amely csak a nem budapesti részlegek adatait tartalmazza!
8 - Hozza létre az ATLAG nevű nézettáblát, amely a részlegek kódját és az ott dolgozók átlagfizetését tartalmazza! Készítsen listát a létrehozott nézettábla segítségével, amelyben a dolgozók neve, fizetése, részlegének kódja és a részleg átlagfizetése szerepel!
9 - Az előző feladatban létrehozott ATLAG nevű nézettábla felhasználásával
irassa ki a dolgozók nevét, fizetését, részlegének nevét és címét, valamint a részlegben dolgozók átlagfizetését!
10 - Készítse el a RENDELES és AUTOK táblák alapján az UJ_RENDELES nézettáblát, melynek oszlopai az ügyfélszám, az autócsoport, a kölcsönzött autó típusa, a rendelés dátuma, a megrendelő személy neve, a kölcsönzési idő kezdete és időtartama, valamint a fizetés módja legyenek! Listázza ki a tábla tartalmát!
11 - Módosítsa az előző feladatban létrehozott nézettábla szerkezetét úgy, hogy egy oszlop a kölcsönzési idő alatt futott km-ek számát is mutassa!
12 - Hozza létre az UGYFELEK, TIPUSOK, AUTO_CSOP és RENDELES táblák alapján azt az UJ_UGYFEL nevű nézettáblát, amely a következő oszlopokat tartalmazza: az ügyfél száma, neve, a kapcsolatot tartó személy neve, a rendelt autó típusa, rendszáma, rendelési ideje, a kölcsönzés ideje alatt futott km, a kölcsönzési díj km-enként és naponként!
13 - Hozza létre a KOLCSON_SZAM nevű nézettáblát a RENDELES tábla alapján, amely rendszámonként tartalmazza a kölcsönzések számát! Irassa ki a nézettábla tartalmának felhasználásával az AUTOK táblában szereplő autók rendszámát, típusát és a kölcsönzések számát! Az egyszer sem kölcsönzött autóknál a kölcsönzések száma 0 legyen!
14 - Hozza létre azt a táblát, amely a rendszámot, az utolsó szervíz idején mutatott és a jelenlegi km-óra állást, valamint a kötelező szervíz intervallumot tartalmazza! A tábla neve KARBANTART legyen!
15 - A KARBANTART nevű táblában növeljük meg a kilométeróra állását mutató oszlop hosszát 8-ra!
16 - Bővítse a KARBANTART táblát a következő szervíz nevű oszloppal! Hossza legyen 8, típusa numerikus!
17 - Hozzon létre indexet az AUTOK táblára a rendszám alapján!
18 - Hozzon létre indexet a RENDELES táblához az ügyfélszám és az autó típus név szerint!
19 - Törölje az UJ_RESZL1 nevű táblát!
Pénzes László