SQL forráskód

Ki kinek a vezetője?

Szerző:
Organogram logó

HR-organogram-logoAz SQL lekérdezések újabb típusát adják a hierarchikus lekérdezések. Az Oracle adatbázis-szerver már régóta támogatja ezt a lehetőséget. A hierarchia legtöbbször valamilyen fa adatszerkezethez kötődik. Ezek természetesen nem közvetlenül tárolódnak egy normalizált, relációs adatbázisban, de az adatok közötti kapcsolat értelmezése során felépíthető rekurzív módon a fa struktúra.

Hasonló feladat: Organogram készítése, reflexióra építve. Érdemes összehasonlítani a kétféle szemléletmódot.

Ki kinek a vezetője?

Az Oracle HR sémában az EMPLOYEES és DEPARTMENTS táblák között kétirányú 1:N kapcsolat van. Egy EMPLOYEE_ID egyedi kulccsal azonosított alkalmazotthoz tartozik egy nem kötelező DEPARTMENT_ID külső kulcs az EMPLOYEES táblában. Egy kivétellel minden alkalmazott részleghez hozzárendelt.

Oracle HR séma

Egy DEPARTMENT_ID egyedi kulccsal azonosított részleghez tartozik egy nem kötelező MANAGER_ID külső kulcs a DEPARTMENTS táblában. Minden olyan részlegnek van vezetője, amelyikhez legalább egy alkalmazott hozzárendelt. A DEPARTMENTS táblában csak olyan MANAGER_ID szerepelhet, amelyik megtalálható az EMPLOYEES táblában EMPLOYEE_ID-ként. A „legfelsőbb” szinten lévő vezetőnek nincs vezetője.

Előfordulhat, hogy egy-egy részlegen belül többszintű hierarchiát találunk az organogramban, ha a részlegek helyett az alkalmazottak oldaláról közelítjük meg a problémát. Ekkor építhetünk arra, hogy az EMPLOYEES tábla saját magával is kapcsolatban áll (reflexió): egy MANAGER_ID-hez több EMPLOYEE_ID is tartozhat. Másképpen: egy adott vezetőnek több beosztottja is lehet.

A hierarchikus (rekurzív) lekérdező parancs

Ki kinek a vezetője? - Hierarchikus SQL lekérdező parancs

A lekérdező utasítást bele kell építeni egy Java kliensprogramba (MVC architekturális tervezési minta szerint a modell rétegbe), ami JDBC alapon kapcsolódik az Oracle adatbázis-szerver HR sémájához olyan felhasználó nevében, aki csatlakozhat és lekérdezhet. Meg kell tervezni és felügyelni kell a biztonságos kapcsolatot (kivételkezeléssel), annak életciklusát (nyit, lekérdez, zár), valamint gondoskodni kell az eredménytábla megjelenítéséről.

Az eredménytábla (részlet)

Ki kinek a vezetője? - Eredménytábla

A keletkező eredménytábla exportálható Excel-be (XLS, XLSX formátumokba). Az első vagy utolsó oszlop adatait feldolgozva könnyen készíthető egy dinamikus adatmodellel rendelkező, fa struktúrát megjeleníteni képes komponens/felület, ahol szabadon böngészhető a szervezeti hierarchia.

A bejegyzéshez tartozó teljes forráskódot ILIAS e-learning tananyagban tesszük elérhetővé tanfolyamaink résztvevői számára.

A feladat a Java adatbázis-kezelő tanfolyam 9-12. óra: Oracle HR séma elemzése, 13-16. óra: Konzolos kliensalkalmazás fejlesztése JDBC alapon, 1. rész, 33-36. óra: Grafikus kliensalkalmazás fejlesztése JDBC alapon, 2. rész alkalomhoz kapcsolódik.

Az SQL forráskód formázásához a Free Online SQL Formatter-t használtam.

Tagadós lekérdezések

Szerző:
Tagadós lekérdezések

Tagadós lekérdezésekAz SQL nyelv utasításai többféleképpen csoportosíthatók. Például: adatdefiníciós utasítások (DDL), adatmanipulációs utasítások (DML), lekérdező utasítások (DQL), adatelérést vezérlő nyelv (DCL). A lekérdezések tanítása során másféle rendszerezés is adható. Például a tagadást tartalmazó lekérdezések önálló kategóriát alkothatnak.

Az Oracle HR sémát használjuk és bemutatunk tagadást tartalmazó lekérdezésre öt példát.

Oracle HR séma

1. Kik dolgoznak olyan részlegekben, ahonnan senki sem vett részt korábban projektmunkában?

A DEPARTMENTS és EMPLOYEES táblák között 1:N fokszámú kapcsolat van, és a DEPARTMENT_ID köti össze ezeket. A belső lekérdezés visszaadja azoknak a részlegeknek az azonosítóját ( DEPARTMENT_ID), amelyekből már legalább egyszer legalább egy alkalmazott részt vett korábban projektmunkában (ez most egy 6 elemből álló halmaz). A külső lekérdezésben a NOT IN predikátum – építve a belső lekérdezés eredményeire – megadja azon részlegek nevét ( DEPARTMENT_NAME), illetve az ott dolgozó alkalmazottak nevét ( EMPLOYEE_NAME), ahol az alkalmazotthoz tartozó részleg azonosítója nincs benne a belső lekérdezés által visszaadott eredménytáblában. 5 részlegben 15 alkalmazottra teljesül a feltétel. A NOT IN predikátum adja a tagadást.

SQL-1

Az eredmény:

SQL-1-eredmény

2. Melyek azok a részlegek, ahol nem minden alkalmazott azonos munkakörben dolgozik?

A DEPARTMENTS és EMPLOYEES táblák között 1:N fokszámú kapcsolat van, és a DEPARTMENT_ID köti össze ezeket. A lekérdezés csoportosítást végez részleg azonosítóra és névre ( DEPARTMENT_ID, DEPARTMENT_NAME), és aggregálja – most megszámolja – a csoportban előforduló egyedi munkakör azonosítókat ( JOB_ID), majd ezek közül kihagyja azokat, ahol a megszámolás 1-et ad (másképpen: azokat hagyja meg, ahol a megszámolás különbözik 1-től, 0 nem lehet, igazából 1-nél több) – ez biztosítja a tagadást. 7 részleget kapunk eredményül.

SQL-2

Az eredmény:

3. Kik azok az alkalmazottak, akik az életpálya modell alapján már nem kaphatnak fizetésemelést?

A JOBS és az EMPLOYEES táblák között 1:N fokszámú kapcsolat van, és a JOB_ID köti össze ezeket. Az életpálya modellhez tartozik egy munkakörhöz tartozó minimális és maximális fizetés ( MIN_SALARY és MAX_SALARY). Azok az alkalmazottak listázandók, akiknél a fizetés megegyezik a betöltött munkakörükhöz adható legmagasabb fizetéssel ( SALARY=MAX_SALARY). Ez jelenti a tagadást. Eredményül egyetlen alkalmazottat kapunk.

SQL-3

Az eredmény:

SQL-3-eredmény

4. Kik a nem vezető munkakörben dolgozó alkalmazottak?

A belső lekérdezés 18 olyan alkalmazott vezetőjének azonosítóját ( MANAGER_ID) adja vissza, akik lehetnek részlegvezetők vagy középvezetők. A külső lekérdezés minden olyan alkalmazott azonosítóját és nevét ( EMPLOYEE_ID, EMPLOYEE_NAME) adja vissza, akik nincsenek benne a belső lekérdezés által visszaadott eredményhalmazban. Ez adja a tagadást. Eredményül 89 alkalmazottat kapunk.

SQL-4

Az eredmény:

SQL-4-eredmény

5. Milyen részlegek találhatók nem amerikai régió területén lévő országokban?

A REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS táblák között balról-jobbra páronként 1:N fokszámú kapcsolat van és ugyanebben a sorrendben a REGION_ID, COUNTRY_ID, LOCATION_ID köti ezeket össze. A belső lekérdezés azért szükséges, mert vannak üres/fiktív részlegek is, amelyeket ki kell hagyni. Az amerikai régió azonosítója 2 ( REGION_ID), ezt adja most a tagadást. Eredményül 3 részleget kapunk 2 országban és 1 régióban.

SQL-5

Az eredmény:

SQL-5-eredmény

A bejegyzéshez tartozó teljes forráskódot ILIAS e-learning tananyagban tesszük elérhetővé tanfolyamaink résztvevői számára.

A feladatok megoldása során nem foglalkoztam külön azzal az egy alkalmazottal, akinek nincs részlege. A feladatok a Java adatbázis-kezelő tanfolyam 13-16. óra: Konzolos kliensalkalmazás fejlesztése JDBC alapon, 1. rész alkalmához és a 33-36. óra: Grafikus kliensalkalmazás fejlesztése JDBC alapon, 1. rész alkalmához kötődnek.

Az SQL forráskód formázásához a Free Online SQL Formatter-t használtam.

Munkakör, létszám, névsor lekérdezése

Szerző:
Munkakör, létszám, névsor

Munkakör, létszám, névsorAz a fela­da­tunk, hogy az Oracle HR sé­má­ból le­kér­dez­ve állít­suk elő munka­kö­rön­ként cso­por­to­sít­va az al­kal­ma­zottak lét­szá­mát és név­so­rát. Adott a JOBS és az EMPLOYEES táb­lák kö­zötti 1:N kap­cso­lat. A JOBS táb­lá­ban (szó­tár) lé­vő JOB_ID egye­di kulcs­hoz tar­to­zik egy hosszabb szö­ve­ges JOB_TITLE le­í­rás (mun­ka­kör), va­la­mint az EMPLOYEES táb­lá­ban meg­ta­lál­ha­tó a JOB_ID kül­ső kulcs­ként. Az EMPLOYEES táb­lá­ban elér­he­tő az al­kal­ma­zottak neve: FIRST_NAME és LAST_NAME. Min­den mun­ka­kört be­tölt leg­alább 1 al­kal­ma­zott és min­den al­kal­ma­zott­hoz van hozzá­ren­delt mun­ka­kör.

Oracle HR séma

Tanfolyamainkon többféleképpen modellezzük és tervezzük meg a feladat megoldását.

Megoldás (Java SE szoftverfejlesztő tanfolyam)

A Java SE szoftverfejlesztő tanfolyam 45-52. óra: Adatbázis-kezelés JDBC alapon alkalmain a következők szerint modellezünk és tervezünk.

Kiindulunk az alábbi egyszerű SQL parancsból:

Munkakör-létszám-névsor-SQL-1

Eredményül ezt kapjuk (részlet):

Munkakör, létszám, névsor eredmény 1

A kapott eredménytáblát a Java kliensprogram fejlesztése során leképezzük egy generikus POJO listába, a rekordonként összetartozó 3 adatból előállítva az objektumok tulajdonságait. A generikus listát csoportváltás algoritmussal feldolgozva, könnyen listázzuk a létszámot és a névsort munkakörönként csoportosítva. A munkakörönkénti létszámot a listafeldolgozás során megkapjuk. Ezt most nem részletezzük, de tanfolyamaink hallgatói számára ILIAS e-learning tananyagban tesszük elérhetővé a teljes forráskódot. Ennél a megoldásnál egyszerűbb a lekérdező parancs, de összetett az eredmény feldolgozása.

Megoldás (Java adatbázis-kezelő tanfolyam)

A Java adatbázis-kezelő tanfolyam 9-12. óra: Oracle HR séma elemzése, 13-16. óra: Konzolos kliensalkalmazás fejlesztése JDBC alapon, 1. rész, 33-36. óra: Grafikus kliensalkalmazás fejlesztése JDBC alapon, 2. rész alkalmával a következők szerint modellezünk és tervezünk.

Denormalizált eredményt közvetlenül visszaadni képes összetett SQL parancsot készítünk:

Munkakör, létszám, névsor SQL-2

Eredményül ezt kapjuk (részlet):

Munkakör, létszám, névsor, eredmény-2

A kapott eredménytáblát a Java kliensprogram fejlesztése során közvetlenül kiíratjuk, hiszen minden szükséges adatot tartalmaz. Az utolsó oszlopban összefűzve megkapjuk az adott részleghez tartozó alkalmazottak névsorát. Ezt most nem részletezzük, de tanfolyamaink hallgatói számára ILIAS e-learning tananyagban tesszük elérhetővé a teljes forráskódot. Ennél a megoldásnál összetettebb a lekérdező parancs, de egyszerű az eredmény feldolgozása.

Érdemes átgondolni és összehasonlítani a kétféle különböző megközelítés lehetőségeit, korlátait. Ha egyensúlyozni kell a kliensprogram és az adatbázis-szerver terhelése között, valamint az MVC modell összetettsége, karbantarthatósága, könnyen dokumentálhatósága a/is szempont, akkor többféle alternatív módszer is bevethető, valamint építhetünk a különböző Oracle verziók (dialektusok) képességeire is.

Az SQL forráskódok formázásához a Free Online SQL Formatter-t használtam.