Gyakorlati alapok III.
SQL (adatbázis)
SQL - dátum 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
További függvények:
Visszaadja a paraméterként beadott hónap utolsó napját. Például:
SELECT LAST_DAY("2020-01-01")
Végeredmény:
2020-01-31
Természetesen figyelembeveszi a szökőéveket is. Például 2000.02 hó utolsó napja 29., mert ezen esztendő szökőév volt:
SELECT LAST_DAY("2000-02-20")
Végeredmény:
2000-02-29
Visszaadja az aktuális dátumot és időt. Például:
SELECT SYSDATE()
Végeredmény:
2020-01-02 10:46:24
Visszaadja a dátumot a paraméterben beállítottak szerint. Például írja kí az alkalmazottak táblájából a belépés dátumát és annak 5 hónappal megnövelt értékét:
SELECT belepes, DATE_ADD(belepes, INTERVAL 5 MONTH) FROM alkalmazott
Végeredmény:
Paraméterként negatív érték is megadható. Például írja kí az alkalmazottak táblájából a belépés dátumát és annak 5 hónappal csökkentett értékét:
SELECT belepes, DATE_ADD(belepes, INTERVAL -5 MONTH) FROM alkalmazott
Végeredmény:
Paraméterként a következő (kötelezően megadandó) időmértékek lehetnek:
-
MICROSECOND
-
SECOND
-
MINUTE
-
HOUR
-
DAY
-
WEEK
-
MONTH
-
QUARTER
-
YEAR
-
SECOND_MICROSECOND
-
MINUTE_MICROSECOND
-
MINUTE_SECOND
-
HOUR_MICROSECOND
-
HOUR_SECOND
-
HOUR_MINUTE
-
DAY_MICROSECOND
-
DAY_SECOND
-
DAY_MINUTE
-
DAY_HOUR
-
YEAR_MONTH
Ezen függvénnyel a dátum kimeneti fomátumát tudjuk befolyásolni. Például irassuk ki az AUTOK táblából a NORMAL autócsoport minden autójának rendszámát, típusnevét és vásárlási időpontját a the 6 day of Jun, 1994 dátumformátumhoz hasonló módon:
SELECT rendszam,tipus_nev, DATE_FORMAT(vasarlas_datuma, '%d %M %Y') FROM autok WHERE auto_csop_nev='NORMAL'
Végeredmény:
Paraméterként a következő (kötelezően megadandó) dátumformátumok lehetségesek:
Forrás - Source: https://www.w3schools.com/sql/func_mysql_date_format.asp
Visszaadja a paraméterként megadott 2 időpont közti hónapok számát. Például hány hónap van 2018.01 és 2017.01 között?
SELECT PERIOD_DIFF(201701, 201801)
Végeredmény:
-12
Megállapítja a paraméterként beadott dátum melyik napra esett. Például melyik nap volt 2020.01.07?
SELECT DAYNAME('2020-01-07')
Végeredmény:
Tuesday
Szabványos dátumformátummás alakítja a részlegesen szavakkal kiírt dátumokat. Például 1980. január 01:
SELECT STR_TO_DATE("1980 januar 1", "%Y %M %d")
Végeredmény:
1980-01-01
Feladatok
1 - Irassuk ki az AUTOK táblából a NORMAL autócsoport minden autójának
rendszámát, típusnevét és vásárlási időpontját 'the 6 day of Jun,
1994'-hez hasonló formában!
2 - Irassa ki azokat a rendelés adatokat, ahol a rendelés feladása és a kölcsönzés kezdete között 1 hétnél kevesebb idő van!
3 - Készítsen listát az összes olyan rendelés tételről, ahol a kölcsönzési idő 10 napnál hosszabb!
4 - Irassa ki az 1994.03.30 utáni rendelések dátumait!
5 - Irassa ki a rendelések táblájából az ügyfelek számát, a rendelésük dátumát, valamint a kölcsönzés kezdetének időpontját a magyar szokásoknak megfelelően (év, hó, nap)!
6 - Írja ki a mai dátumot követő 90. nap dátumát (SYSDATE)!
7 - Írja kí az alkalmazottak táblájából a belépés dátumát és annak 5 hónappal megnövelt értékét!
8 - Állapítsa meg a 1900. januári és a 2000. januári időszak közötti hónapok számát!
9 - Állapítsa meg, hogy az alkalmazottak belépési dátumai milyen napra estek!
10 - Állapítsa meg a jelenlegi napot (például szerda)!
11 - Irassa ki a '1994.06.07.' karaktersorozat hagyományos amerikai dátumformázott alakját (hónap-nap-év)!
12 - Írja ki az alkalmazottak belépésének dátumát 'Év-hónap-nap' formában!
13 - Alakítsa át kiírt születési dátumát dátumformátummá (például
1980 januar 1)!
Pénzes László