DOLGOZO tábla (ahol NULL nem érték!!!) DKOD DNEV FOGLALKOZAS FONOKE BELEPES FIZETES JUTALEK OAZON ------------------------------------------------------------------- 7369 SMITH CLERK 7902 1980.12.17 800 NULL 20 7499 ALLEN SALESMAN 7698 1981.02.20 1600 300 30 7521 WARD SALESMAN 7698 1981.02.22 1250 500 30 7566 JONES MANAGER 7839 1981.04.02 2975 NULL 20 7654 MARTIN SALESMAN 7698 1981.09.28 1250 1400 30 7698 BLAKE MANAGER 7839 1981.05.01 2850 NULL 30 7782 CLARK MANAGER 7839 1981.06.09 2450 NULL 10 7788 SCOTT ANALYST 7566 1982.12.09 3000 NULL 20 7839 KING PRESIDENT NULL 1981.11.17 5000 NULL 10 7844 TURNER SALESMAN 7698 1981.09.08 1500 0 30 7876 ADAMS CLERK 7788 1983.01.12 1100 NULL 20 7900 JAMES CLERK 7698 1981.12.03 950 NULL 30 7902 FORD ANALYST 7566 1981.12.03 3000 NULL 20 7934 MILLER CLERK 7782 1982.01.23 1300 NULL 10 7877 ADAMS CLERK 7902 1981.01.12 800 NULL NULL OSZTALY tábla OAZON ONEV TELEPHELY -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON =================== Tk.2.4.fej. Feladatok lekérdezésekre relációs algebrában és az SQL-ben: =================== a.) Fogalmazzuk meg a lekérdezéseket természetes nyelven "táblákkal" b.) Adjuk meg a lekérdezéseket formálisan relációs algebrában lineáris jelöléssel! c.) Rajzoljuk fel kifejezésfával (a levelekben: relációnevek vagy konstans-táblák, a közbülső csomópontokban pedig relációs algebrai alapműveletek állhatnak), a kifejezésfa kiértékelése alulról-felfelé történik, legfelül van az output reláció. d.) SQL-ben SELECT lista FROM lista WHERE feltétel lekérdezések között a halmazműveletekkel (UNION, EXCEPT/MINUS, INTERSECT). =================== - Kik azok a dolgozók, akiknek a fizetése > 2800? - Kik azok a dolgozók, akik 1982.01.01 után léptek be a céghez? - Kik azok a dolgozók, akik a 10-es vagy a 20-as osztályon dolgoznak? - Kik azok a dolgozók, akiknek a főnöke KING? - Kik azok a dolgozók, akik főnökének a főnöke KING? - Adjuk meg azoknak a főnököknek a nevét, akiknek a foglalkozása nem 'MANAGER'. - Adjuk meg azokat a dolgozókat, akik többet keresnek a főnöküknél. - Kik azok a dolgozók, akik osztályának telephelye DALLAS vagy CHICAGO? - Kik azok a dolgozók, akik osztályának telephelye nem DALLAS és nem CHICAGO? - Adjuk meg azoknak a nevét, akiknek a fizetése > 2000 vagy a CHICAGO-i osztályon dolgoznak. =================== További feladatok (relációs algebrai változatot átírva) - Kik azok a dolgozók (mi a nevük), akiknek a legnagyobb a fizetése? - Kik azok a dolgozók (mi a nevük), akiknek a legkisebb a fizetése? =================== =================== Feladatok lekérdezésekre az SQL-ben: =================== Tk.6.1.fej. Lekérdezések egy táblára - SELECT.. FROM.. WHERE.. záradékok: =================== - Adjuk meg a dolgozók nevét, az éves fizetését és hány hete állt munkába! - Adjuk meg a dolgozók között előforduló foglalkozások neveit (az eredmény most halmaz legyen, vagyis minden foglalkozást csak egyszer írjuk ki!) - Kik azok a dolgozók, akiknek a fizetése > 2800? - Kik azok a dolgozók, akiknek a fizetése 3000 és 4500 között van? - Kik azok a dolgozók, akik a 10-es vagy a 20-as osztályon dolgoznak? - Adjuk meg azon dolgozókat, akik nevének második betűje 'A'. - Kik azok a dolgozók, akiknek a jutaléka ismert? (vagyis nem NULL) =================== Oracle SQL sorfüggvények hogyan használjuk az Oracle dokumentációban szereplő példákat SQL Language Reference -> 5 Functions -> Single-Row Functions =================== - Adjuk meg azon dolgozókat, akik nevében van legalább két 'L' betű. (Többféle megoldást is keressünk a lekérdezésre, LIKE, INSTR függvény) - Adjuk meg a dolgozók fizetéseinek négyzetgyökét és 3-mal vett maradékát. - Számoljuk ki, hogy a mai nap hány naposak vagyunk, illetve azt is hány hónaposak vagyunk (DUAL tábla) - Adjuk meg, hogy hány hete dolgozik a cégnél ADAMS és milyen hónapban és milyen nap (hétfő, kedd, stb) lépett be. - Adjuk meg a dolgozók éves jövedelmét, ahol a havi jövedelem a fizetés és jutalék összege, ahol nincs jutalék megadva,ott a 0 helyettesítő értékkel számoljunk (NVL függvény alkalmazása). =================== ORDER BY záradék: - Adjuk meg osztályok azonosítója sorrendjében a dolgozókat, azonos osztályon belül a fizetés szerint csökkenően listázzuk ki a dolgozók neveit. =================== =================== 6.4.fej. Egytáblás lekérdezések GROUP BY.. HAVING.. ORDER BY.. záradékok, 6.4.fej.->5.1-5.2.fej. Kiterjesztett műveletek a relációs algebrában =================== - Mekkora a minimális és a maximális fizetés a dolgozók között? - Kinek a legkisebb a fizetése? - Kinek a legnagyobb a fizetése? - Mennyi a dolgozók összfizetése? - Mennyi a 20-as osztályon az átlagfizetés? - Adjuk meg, hogy hány különbözo foglalkozás fordul elo a dolgozók között! - Kik azok a dolgozók, és milyen munkakörben dolgoznak, akiknek a legnagyobb a fizetésük? - Adjuk meg osztályonként a telephelyet és az átlagfizetést! - Adjuk meg az osztályonkénti legmagasabb átlagfizetést! - Adjuk meg osztályonként az átlagfizetést! - Adjuk meg azokra az osztályokra az átlagfizetést, ahol ez nagyobb mint 2000. - Adjuk meg azokra az osztályokra az átlagfizetést, ahol legalább hárman dolgoznak! - Melyek azok az osztályok, ahol legalább hárman dolgoznak? - Adjuk meg osztályonként a minimális fizetést, de csak azokat az osztályokét, ahol a minimális fizetés nagyobb, mint a 30-as osztályon dolgozók minimális fizetése. - Adjuk meg osztályonként az ott dolgozó hivatalnokok (FOGLALKOZAS='CLERK') átlagfizetését, de csak azokon az osztályokon, ahol legalább két hivatalnok dolgozik! =================== 6.2-6.3.fej. Több táblás lekérdezések, összekapcsolások, alkérdések =================== Keressünk több megoldást is, különböző összekapcsolásokkal, esetleg a tábla önmagával való szorzatával oldjuk meg illetve ugyanezeket a feladatokat alkérdésekkel is nézzük meg összehasonlítva a szemantikát, melyik a hatékonyabb, és eredményez-e valamilyen különbséget, stb. =================== - Kik azok a dolgozók, akiknek a főnöke KING? - Kik azok a dolgozók, akik osztályának telephelye BOSTON vagy CHICAGO? - Kik azok a dolgozók, akik osztályának telephelye nem (BOSTON vagy CHICAGO)? - Kik azok a dolgozók, akiknek ugyanaz a főnökük és ugyanazon az osztályon dolgoznak, mint 'MARTIN'? - Melyek azok az osztályok, ahol nem dolgozik senki? =================== - Adjuk meg, hogy mely dolgozók fizetése jobb, mint a saját osztályán (ahol dolgozik) dolgozók átlagfizetése! - Adjuk meg, hogy mely dolgozók átlagjövedelme jobb, mint a saját osztályán dolgozók átlagjövedelme, ahol a jövedelem a fizetés és jutalék összege, ahol nincs jutalék megadva, ott a 0 helyettesítő értékkel számoljunk (NVL függvény alkalmazása). - Adjuk meg, hogy az egyes osztályokon hány ember dolgozik (azt is, ahol 0=senki). - Adjuk meg osztályonként a dolgozók összfizetését az osztály nevét megjelenítve ONEV, SUM(FIZETES) formában, és azok az osztályok is jelenjenek meg ahol nem dolgozik senki, ott az összfizetés 0 legyen. Valamint ha van olyan dolgozó, akinek nincs megadva, hogy mely osztályon dolgozik, azokat a dolgozókat egy 'FIKTIV' nevű osztályon gyűjtsük össze. Minden osztályt a nevével plusz ezt a 'FIKTIV' osztályt is jelenítsük meg az itt dolgozók összfizetésével együtt. =================== - Kik azok a dolgozók, akiknek a fizetése nagyobb, mint 'ALLEN' fizetése? - Kik azok a dolgozók, és milyen munkakörben dolgoznak, akiknek a legkisebb a fizetésük? (köv.héten lesz a min csoportfüggvénnyel, ma theta ALL alkérdéssel) - Kik azok a dolgozók, és milyen munkakörben dolgoznak, akiknek a legnagyobb a fizetésük? (köv.héten lesz a max csoportfüggvénnyel, ma theta ALL alkérdéssel) - Kik azok a dolgozók, akiknek a fizetése kisebb, mint valamelyik hivatalnok ('CLERK') fizetése. (ugyanez a kérdés, csak most minden hivatalnok fizetésénél legyen kisebb) =================== =================== 10.2.fej. Feladatok hierarchikus lekérdezésekre és rekurzióra =================== - Listázzuk ki az dolgozo tábla alapján a főnökökhöz tartozó beosztottak nevét és osztályukat. a.) A dolgozo tábla önamagára való hivatkozásával (többtáblás lekérdezés sorváltozókkal). b.) A CONNECT BY utasításrész használatával, a hierarchikus szerkezetet 'KING'-től felülről lefelé bejárva. c.) Alulról felfelé járjuk be a hierarchikus szerkezet egy ágát 'SMITH'-től kezdve.