HÍREK
LEGUTÓBBI MÓDOSÍTÁSOK SAJÁT
MUNKÁK OKTATÁS és KAPCSOLAT EGYÉB
MÓDSZERTANOK TANULD MEG A JAVA JAVÁT!

Gyakorlati alapok III.

SQL (adatbázis)

 

SQL - táblák összekapcsolása

 

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

• COALESCE függvény

• 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 az eddig külön vizsgált táblákat és tartalmaikat összekapcsoljuk egyetlen, bár néha igen bonyolult MySQL-utasításban. Sok ilyen jellegű, azaz több táblából kiinduló lekérdezés voltaképpen a relációs algebra műveleteit valósítja meg. Általánosságban ezek lehetnek:

• unió,

• különbség,

• Descartes-szorzat,

• projekció,

• szelekció,

• hányados,

• metszet,

• összekapcsolás,

• természetes összekapcsolás.

Alább látni fogjuk, hogy milyen hatékonyan valósítják meg a MySQL beépített függvényei ezen műveleteket. Nézzük is meg bevezető példánkat:

 

Készítsünk listát, amely tartalmazza a dolgozó nevét, beosztását, részleg kódját, a részleg nevét és címét névsor szerinti sorrendben!

 

Először nézzük meg a 2 tábla elvi konstrukcióját:

• reszleg tábla

  • reszleg_kod

  • reszleg_nev

  • reszleg_cim

• alkalmazott tábla

  • alk_kod

  • alk_nev

  • beosztas

  • belepes

  • fizetes

  • premium

  • reszleg_kod

Észrevehető a közöttük lévő egyetlen kapocs (másnéven kulcs): mindkét tábla tartalmazza a reszleg_kod attribútumot. Ezeket a WHERE feltételben összekapcsolva a 2 tábla értékei tetszőleges további feltételekkel egyesíthetők, szűrhetők. A közös attribútumot a (.) jellel és a táblanévval tesszük egyedien azonosíthatóvá:

 

...WHERE alkalmazott.reszleg_kod = reszleg.reszleg_kod...

 

SELECT alk_nev, beosztas, alkalmazott.reszleg_kod, reszleg_nev, reszleg_cim FROM alkalmazott, reszleg WHERE alkalmazott.reszleg_kod = reszleg.reszleg_kod ORDER BY alk_nev

 

Végeredmény:

 

Érdekesség: a közös attribútumot a rendszer barna vonallal jelöli ki.

 

Arra is van lehetőségünk, hogy a táblák neveihez külön változónevet rendeljünk hozzá. Legyen például az ALKALMAZOTT tábla egyszerű a betű, a RÉSZLEG tábla pedig r betű:

 

...FROM alkalmazott a, reszleg r...

 

SELECT alk_nev, beosztas, a.reszleg_kod, reszleg_nev, reszleg_cim FROM alkalmazott a, reszleg r WHERE a.reszleg_kod = r.reszleg_kod ORDER BY alk_nev

 

Végeredmény:

 

További függvények:

• UNION

• JOIN-műveletek

UNION

A függvény több SELECT lekérdezés eredményét egyesíti egyetlen eredményhalmazba (unió). Például egyesítsük ALKALMAZOTT és RÉSZLEG tábla kijelölt attribútumait:

 

SELECT alk_nev FROM alkalmazott
UNION
SELECT reszleg_kod FROM reszleg

 

Végeredmény:

 

A kapott eredménytábla most kivételesen tartalmilag semmitmondó, de kiválóan megmutatja a művelet halmazelméleti unió jellegét.

 

JOIN-műveletek

A JOIN-műveletek 2 halmaz elemei közti összehasonlítások konkrét implementációi. Ez így még nem igazán érthető, ezért gyorsan kezdjük is el tanulmányozni az alábbi képet:

 

 

A halmazösszehasonlítások képei és az alattuk lévő SQL-utasítások annyira beszédesek és egyértelműek, hogy most nem dolgozok ki konkrét példákat, hanem azt javaslom a Tisztelt Olvasónak, addig tanulmányozza a kép információit, amíg kellő bátorságot nem fog érezni az alábbi feladatokkal való szembesülésre.

 

Feladatok

 

1 - Irassa ki az ELADÓ beosztású dolgozók nevét, részleg kódját és a részleg címét!

SQL-megoldás

2 - Irassa ki a Győrött dolgozók adatait!

SQL-megoldás

3 - Irassa ki részleg nevenként csoportosítva a legmagasabb és legalacsonyabb fizetést!

SQL-megoldás

4 - Irassa ki részleg címenként csoportosítva az átlagfizetést és a létszámot!

SQL-megoldás

5 - Irassa ki azoknak a dolgozóknak a nevét, beosztását és fizetését, akiknek a fizetése több, mint 'BALOGH' fizetése!

SQL-megoldás

6 - Írjon SELECT parancsot, amely megadja a legmagasabb beszerzési árat!

SQL-megoldás

7 - Irassa ki azon részlegek kódját, nevét és címét, amelyekben nem dolgozik senki!

SQL-megoldás

8 - Irassa ki azon autók rendszámát és típusát, melyek már több, mint kétszer voltak kötelezően szervízben!

SQL-megoldás

9 - Irassa ki a 3000 és 10000 Ft közötti napidíjért kölcsönözhető autók rendszámát, típusát és napidíját!

SQL-megoldás

10 - Irassa ki azokat az ügyfeleket, akiknek jelenleg rendelésük van! A listában legyen kiírva az ügyfél száma, neve és címe a rendelés, kölcsönzés dátumával együtt!

SQL-megoldás

11 - Irassa ki azokat az ügyfeleket, akik a LUXUS autócsoportból rendeltek autót! Írja ki az ügyfélszámot, a típust, a rendszámot, a napi és kilométerenkénti kölcsönzési díjat valamint a megrendelés dátumát!

SQL-megoldás

12 - Irassa ki a rendszámot, típust, kölcsönzési díjat, a rendelés dátumát, a kölcsönzés kezdetét, az ügyfélszámot, nevet, címet az összes megrendelésre, amely 1994. május 1. óta történt!

SQL-megoldás

13 - Irassa ki azon autók típusát, rendszámát, napi kölcsönzési díját, amelyeknél a kölcsönzési díj értéke kevesebb, mint a RENAULT 19 típusé!

SQL-megoldás

14 - Készítsen listát, amely a rendelés számokat, a rendelő személy nevét, a rendelt autó típusát, leírását és a rendelés dátumát tartalmazza!

SQL-megoldás

15 - Irassa ki a jelenleg kölcsönzött autók rendszámát, típus nevét, a kölcsönző ügyfél számát és nevét, valamint a rendelés dátumát!

SQL-megoldás

16 - Irassa ki az összes autó rendszámát és típusát, akár az AUTOK táblában, akár a RENDELES táblában szerepel!

SQL-megoldás