Programozás | SQL » SQL példatár

SQL PÉLDATÁR SQL PÉLDATÁR TARTALOM Bevezetés. 2 I.Rész Példatár 3 0. Az Alaptáblák 4 1. Egyszerű lekérdezések 5 2. Egytáblás csoportosító lekérdezések 6 3. Többtáblás lekérdezések allekérdezésekkel 1 7 4. Többtáblás lekérdezések allekérdezésekkel 2 8 5. Többtáblás lekérdezések allekérdezésekkel 3 9 6. Interaktív SQL programok 10 7. SQL Zárthelyi Dolgozat 11 8. Az SQL DDL-DML utasításainak használata 12 9. SQL-DCL Zárthelyi Dolgozat 14 II.Rész MEGOLDÁSOK 15 1. Egyszerű lekérdezések 16 2. Egytáblás csoportosító lekérdezések 17 3. Többtáblás lekérdezések allekérdezésekkel 1 18 4. Többtáblás lekérdezések allekérdezésekkel 2 24 5. Többtáblás lekérdezések allekérdezésekkel 3 29 6. Interaktív SQL programok 37 7. SQL Zárthelyi Dolgozat 41 8. Az SQL DDL-DML utasításainak használata 46 9. SQL-DCL Zárthelyi Dolgozat 68 Utolsó oldal . 71 (SQL példatár) -1- BEVEZETÉS E példatár a

Budapesti Műszaki Főiskola Neumann János Informatikai Karán a Műszaki Informatikus és a Gazdasági Informatikus hallgatói számára oktatott Adatbázis-kezelés című tárgy gyakorlati anyagának elsajátításához kíván segítséget nyújtani. A gyakorlati képzés az Oracle adatbázis-kezelő rendszer SQL*Plus környezetének használatán alapszik, ezért feltételezzük, hogy (élve az Oracle Magyarország Academic Licence által biztosított lehetőséggel) minden hallgatónk ezt (Oracle 9.2) már telepítette az otthoni számítógépén A példatár első része csak a feladatokat tartalmazza, míg a második részben találhatók a megoldások. A feladatok szövegét a könnyebb kezelhetőség érdekében megismételtük A közölt feladatok a “Scott” felhasználó emp, dept és salgrade tábláit használják, melyek tartalmát és felépítését még a feladatok előtt megadjuk. A példák megoldásainak futtatása a legegyszerűbben úgy oldható meg, hogy a

megoldás script programját oszlop-blokkmásolással átvisszük az SQL*Plus prompt jel elé. (Bal-Alt billentyű nyomvatartása közben az egérrel kijelöljük a blokkot, majd annak bal gombjával rögzítjük azt Ezután a szokásos Ctrl-C és Ctrl-V billentyűpárossal elvégezzük a vágólapra helyezést és a blokk másolását) A képzés tankönyve (mely bemutatja az elméleti hátteret, valamint további mintapéldákat is tartalmaz): Kende Mária – Kotsis Domokos – Nagy István: ADATBÁZIS-KEZELÉSAZ ORACLE-RENDSZERBEN (PANEM kiadó, 2002) (SQL példatár) -2- I.RÉSZ PÉLDATÁR Az alábbi feladatok megoldásai a II. részben (Megoldások) megtalálhatóak (SQL példatár) -3- 0. AZ ALAPTÁBLÁK Az emp tábla: SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 800 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD

SALESMAN 7698 81-FEB-22 1250 500 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7900 JAMES CLERK 7698 81-DEC-03 950 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7934 MILLER CLERK 7782 82-JAN-23 1300 10 SQL> desc emp; Név Üres? Típus ----------------------------------------- -------- --------------EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) A dept tábla: SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> desc dept; Név Üres? Típus

----------------------------------------- -------- --------------DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) A salgrade tábla: SQL> select * from salgrade; GRADE LOSAL HISAL ---------- ---------- ---------1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 SQL> desc salgrade; Név Üres? Típus ----------------------------------------- -------- --------------GRADE NUMBER LOSAL NUMBER HISAL NUMBER (SQL példatár) -4- 1. EGYSZERŰ LEKÉRDEZÉSEK 1.A Listázza ki a Research nevű részleg minden dolgozójának nevét, azonosítóját, jövedelmét és főnökének azonosítóját. 1.B Listázza ki a Dallas-i telephely minden dolgozójának nevét, munkakörét, fizetését és részlegének azonosítóját. (SQL példatár) -5- 2. EGYTÁBLÁS CSOPORTOSÍTÓ LEKÉRDEZÉSEK 2.A Listázzuk főnökönként (mgr) a főnökhöz tartozó legkisebb dolgozói fizetéseket. Hagyjuk ki azon dolgozók fizetését, akiknek nincs főnökük, valamint

azokat a csoportokat, ahol a legkisebb fizetés nagyobb 2000 USD-nél. Rendezzük a listát a legkisebb fizetések szerint növekvően 2.B Listázzuk főnökönként (mgr) a főnökhöz tartozó dolgozói átlagfizetéseket. Hagyjuk ki azon dolgozók fizetését, akiknek nincs főnökük, valamint azokat a csoportokat, ahol az átlagfizetés nagyobb 3000 USD-nél. Rendezzük a listát az átlagfizetések szerint csökkenően (SQL példatár) -6- 3. TÖBBTÁBLÁS LEKÉRDEZÉSEK ALLEKÉRDEZÉSEKKEL 1 3.A Az emp és dept tábla felhasználásával listázza minden olyan alkalmazott nevét, részlegének nevét és fizetését, akiknek fizetése megegyezik valamelyik Dallas-ban dolgozóéval. Legyen a lista fejléce  NÉV, RÉSZLEGNÉV, FIZETÉS , és a lista legyen a  FIZETÉS, RÉSZLEGNÉV  szerint rendezett. 3.B Listázzuk a főnökeik (mgr) szerint csoportosítva azokat a dolgozókat, akiknek fizetése e csoportosítás szerint a legnagyobb, de kisebb 3000 USD-nél. A

lista a fizetés csökkenő értéke szerint legyen rendezett. Legyen a lista fejléce  FŐNÖK KÓDJA, DOLGOZÓ NEVE, FIZETÉSE  3.C Listázza minden olyan alkalmazott azonosítóját és nevét, akik olyan részlegen dolgoznak, ahol van nevében „T” betűt tartalmazó alkalmazott. Legyen a lista fejléce  AZONOSÍTÓ, NÉV, RÉSZLEG NEVE , és a lista legyen  RÉSZLEG NEVE, NÉV  szerint rendezett. 3.D Listázzuk a főnökeik (mgr) szerint csoportosítva azokat a dolgozókat, akiknek fizetése e csoportosítás szerint a legkisebb, de nagyobb 1000 USD-nél. A lista a fizetés növekvő értéke szerint legyen rendezett Legyen a lista fejléce  FŐNÖK KÓDJA, DOLGOZÓ NEVE, FIZETÉSE  3.E Listázzuk főnökönként (mgr) a legkisebb fizetésű dolgozókat. Hagyjuk ki azon dolgozók fizetését, akiknek nincs főnökük, valamint azokat a csoportokat, ahol a legkisebb fizetés nagyobb 3000 USD-nél Rendezzük a listát a legkisebb fizetések szerint

növekvően (Lásd a 2.A feladatot) (SQL példatár) -7- 4. TÖBBTÁBLÁS LEKÉRDEZÉSEK ALLEKÉRDEZÉSEKKEL 2 4.A Listázza ki az egyes részlegek telephelyének nevét, a részlegek vezetőinek nevét, a fizetésüket, valamint a részleg dolgozóinak átlagjövedelmét (ennek adjon nevet) ez utóbbi adat szerint rendezve. 4.B Listázza ki mindazon dolgozók nevét, foglalkozását, telephelyét, valamint jövedelmük és a részlegük átlagjövedelme közti különbséget, akiknél a munkakörük átlagjövedelme kisebb az összes dolgozó átlagjövedelménél. A listát rendezze telephely szerint 4.C Listázza ki azon dolgozók nevét, munkakörét, jövedelmét, telephelyét, a munkakörük átlagjövedelmét, akiknek jövedelme a munkakörük átlagjövedelménél kisebb. A lista legyen a dolgozók neve szerint rendezve 4.D Listázza ki mindazon dolgozók nevét, foglalkozását, részlegük nevét, valamint részlegük átlagjövedelme és saját jövedelmük közti

különbséget, akiknek a munkaköri átlagjövedelme kisebb az összes dolgozó átlagjövedelménél. A listát rendezze a részleg neve szerint (SQL példatár) -8- 5. TÖBBTÁBLÁS LEKÉRDEZÉSEK ALLEKÉRDEZÉSEKKEL 3 5.A Listázza ki minden dolgozó nevét, munkakörét, telephelyét, fizetését, valamint a saját telephelyén a munkakörének átlagfizetését, és telephelyének átlagfizetését. A listát munkakör és telephely szerint növekvő módon rendezze 5.B Listázza ki minden dolgozó nevét, munkakörét, telephelyét, fizetését valamint fizetését a saját telephelyén a munkaköre átlagfizetésének százalékában,és a fizetését a saját telephelye átlagfizetésének százalékábana százalékértékeket egészként kiíratva) a munkakör és a telephely szerint növekvő módon rendezve. 5.C Listázza munkakörönként azon dolgozókat, akiknek fizetése több a munkakörük átlagfizetésénél. 5.D Listázza a legkisebb átlagfizetésű

részleg dolgozóit, e részleg nevét és átlagfizetését. 5.E Listázza a legkisebb létszámú foglalkozási csoport dolgozóinak nevét és foglalkozását. (SQL példatár) -9- 6. INTERAKTÍV SQL PROGRAMOK 6.A Mit csinál az alábbi Példa.sql script program? set verify off SELECT * FROM &tabla1; SELECT * FROM &&tabla2; ACCEPT tabla3 PROMPT "A tábla neve: " SELECT * FROM &tabla3; DEFINE tabla4="salgrade" SELECT * FROM &tabla4; DEFINE ACCEPT abc PROMPT "Táblanevek törlése: (Üsd le az Enter billentyűt.)" UNDEFINE tabla2 UNDEFINE tabla3 UNDEFINE tabla4 UNDEFINE abc DEFINE set verify on 6.B Listázza ki rendezve azon foglalkozási csoportok dolgozóinak nevét, amelyekbe a felhasználó által megadott számnál többen tartoznak. A lista fejléce legyen  Foglalkozás, Azonosító, Név, Csoportlétszám . 6.C Listázza ki azon részlegek telephelyét, a részlegek tagjainak nevét és jövedelmüket, ahol az

átlagjövedelem meghaladja a felhasználó által megadott értéket. A lista fejléce  Telephely, Név, Jövedelem . (SQL példatár) - 10 - 7. SQL ZÁRTHELYI DOLGOZAT A1. Listázza ki a Research nevű részleg minden dolgozójának nevét, azonosítóját, jövedelmét és főnökének azonosítóját. A2. Listázza ki a felhasználó által megadott jövedelmi tartományba eső dolgozókat Legyen a lista fejléce TELEPHELY, DOLGOZÓ NÉV, MUNKAKÖR, JÖVEDELEM . A lista legyen a TELEPHELY és a DOLGOZÓ NÉV szerint növekvően rendezett. A3. Listázza ki főnökeik (mgr) szerint csoportosítva a főnökkel rendelkező dolgozókat, valamint a fizetésük és a csoportjuk átlagfizetése közötti különbséget. Legyen a lista fejléce  Főnök Kód, Főnök Név, Telephely, Dolgozó Név, Különbség . A lista a Főnök Név szerint növekvően, a Különbség szerint pedig csökkenően legyen rendezett. B1. Listázza ki a Dallas-i telephely minden

dolgozójának nevét, munkakörét, fizetését és részlegének azonosítóját. B2. Listázza ki a felhasználó által megadott azonosítójú főnök (mgr) beosztottjait Legyen a lista fejléce TELEPHELY, DOLGOZÓ NÉV, JÖVEDELEM, FŐNÖK KÓD. A lista legyen a TELEPHELY és a DOLGOZÓ NÉV szerint növekvően rendezett. B3. Listázza ki a munkakörük szerint csoportosítva a dolgozókat, valamint a csoportjuk legnagyobb jövedelme és a saját jövedelmük közötti különbséget. Legyen a lista fejléce Munkakör, Dolgozó Név, Különbség, Főnök Név, Telephely . A lista a Munkakör szerint növekvően, a Különbség szerint pedig csökkenően legyen rendezett. (SQL példatár) - 11 - 8. AZ SQL DDL-DML UTASÍTÁSAINAK HASZNÁLATA 8.A Hozzon létre az emp táblából egy dolg nevű táblát, és nevezze át annak ename oszlopát azonosító névre. 8.B (Feladatsor) B.1) Törölje a dolgozo nevű táblát B.2) Hozzon létre egy dolgozo nevű táblát az

emp tábla azon dolgozóiból, akiknek 500 USD-nél több jövedelmük, és 1980. január 1-e után léptek be B.3) Nevezze át a tábla oszlopneveit értelemszerűen a következőkre: AZONOSÍTÓ, NÉV, MUNKAKÖR, FŐNÖK ID, BELÉPÉS, FIZETÉS, JUTALÉK, RÉSZLEG ID. B.4) Bővítse a dolgozó táblát a LAKHELY oszloppal B.5) Töltse fel a tábla LAKHELY oszlopát a következőképpen: a.) aki New York-ban dolgozik, annak lakhelye is New York, de King Long Beach-en lakik, b.) a Dallas-i clerk munkakörűek Garland-ban, a többiek Dallas-ban laknak, c.) a Chicago-ban dolgozók Boston-ban, és a Boston-ban dolgozók Chicago-ban laknak, kivéve azon Chicago-i dolgozokat, akiknek Blake a főnökük, mert azok Indianapolisban laknak, feltéve, hogy nem clerk munkakörűek, mert akkor sehol sem laknak. B.6) Bővítse a táblát Kováccsal, akinek a foglalkozása nagyfőnök, 9999 az azonosítója, 2001. január 5-én lépett be a céghez, 9999 USD a fizetése, ugyanennyi a jutaléka, a

99-es azonosítójú Nagyfőnökség a munkahelye Budapesten, a King nevű alkalmazott a beosztottja, és Soroksáron lakik. E bővítéshez tegyen mentési pontot B.7) Bővítse a táblát a következő alkalmazottal: Neve: Kelemen; Azonosítója: 1111; Munkaköre: megegyezik a legkisebb fizetésű dolgozó munkakörével, Főnöke: megegyezik Ford főnökével, Belépése: az aktuális dátum, Fizetése: a legnagyobb munkaköri átlagfizetés, de kisebb mint 5000 USD, Jutaléka: 111 USD, Részlege: megegyezik a budapesti részleg számával, Lakóhelye: Soroksár. A bővitéshez tegyen mentési pontot. B.8) Bővítse a táblát még két dolgozóval, majd érvénytelenítse a bővítéseket, és véglegesítse az adattáblát (SQL példatár) - 12 - 8.C (Feladatsor) C.1 Adja ki a SELECT * FROM emp WHERE ROWNUM = 3; majd a SELECT * FROM emp WHERE ROWNUM <= 3; utasítást. Indokolja meg a tapasztalatait C.2 Mi a kapcsolat az alábbi két utasítás között? SELECT * FROM

emp WHERE ROWNUM <= 3; és SELECT * FROM emp; C.3 Hozzon létre az emp tábla 300 USD-nél nagyobb jövedelmű dolgozóiból egy dolgozó táblát. C.4 Listázza ki a dolgozó táblát ename szerint rendezve C.5 Listázza ki az ename szerint rendezett dolgozó tábla első három sorát C.6 Az előző listához hozza létre a jó dolgozók nézettáblát C.7 Listázza a jó dolgozók nézettáblát C.8 Kérdezze le a megfelelő adatszótárbeli nézetekből (user objects, user catalog, vagy user views). C.9 Bővítse a dolgozó táblát az ABSOLON nevű dolgozóval (A többi érték tetszőleges) C.10 Listázza a jó dolgozók nézettáblát Indokolja az eredményt C.11 Bővítse a jó dolgozók nézettáblát (!) az ABASÁMUEL nevű dolgozóval, majd listázza a dolgozó táblát. Indokolja meg az eredményt C.12 Tiltsa meg a jó dolgozók nézettábla bővítését, majd kíséreljen meg egy bővítést C.13 Most hozzunk létre a dolgozó táblából egy DolgozóNézet

nézetet, amely a dolgozó tábla empno, ename, job és deptno oszlopait tartalmazza. A nézet oszlopnevei legyenek: azonosító, név, foglalkozás, részleg. C.14 Bővítsük ezt a DolgozóNézet nézetet az ABASÁMUEL dolgozóval, majd dobjuk el C.15 Hozzuk létre úgy a DolgozóNézet nézetet, hogy ne lehessen új sort beszúrni 8.D (Összetett Feladatok) D.1 Hozza létre az emp táblával azonos dolgozó táblát Listázza ebben a legkisebb átlagfizetésű részleg dolgozóinak adatait. D.2 Hozza létre az emp táblával azonos dolgozó táblát Növelje meg 10%-al ebben a legkisebb átlagfizetésű részleg dolgozóinak fizetését, és listázza e részleg nevét, az itt dolgozók nevét, valamint azok régi és új fizetését. D.3 Hozza létre az emp táblával azonos dolgozó táblát Növelje meg 20%-al ebben a három legrosszabbul fizetett dolgozó fizetését az eredeti fizetés oszlopban és listázza ki a dolgozó táblát. (SQL példatár) - 13 - 9. SQL-DCL

ZÁRTHELYI DOLGOZAT A.1 a.) Hozzon létre egy szerepkört b.) Rendeljen a szerepkörhöz felhasználót c.) Adjon tábla és nézet-létrehozási jogokat a szerepkörnek A.2 Hozzon létre a system felhasználón belül egy saját név felhasználót. Változtassa meg a saját név felhasználó a jelszavát. A.3 A system felhasználó adjon a raktár táblához lekérdezési és módosítási jogokat minden felhasználónak. B.1 Hozzon létre egy felhasználót a system felhasználón belül. A felhasználó neve a vezetéknevének értelemszerű rövidítése legyen, jelszava pedig „dolgozat” legyen. Változtassa meg a jelszót a saját utónevére. B.2 A system felhasználó adjon önnek olyan jogokat, hogy hozzáférhessen az SQL-hez, az adatbázishoz, tudjon táblát, nézetet, eljárást létrehozni, kezelni. B.3 A system felhasználó adjon jogosultságokat az ön nevével jelzett felhasználónak, olyan jogokat, hogy tudjon táblát módosítani, képes legyen

adatokat lekérdezni, módosítani és törölni a „forgalmazó” nevű táblából és legyen továbbadási joga is. (SQL példatár) - 14 - II.RÉSZ MEGOLDÁSOK Az alábbi feladat-megoldások előtt megismételjük a feladat szövegét a példatár könnyebb kezelhetősége érdekében. (SQL példatár) - 15 - 1. EGYSZERŰ LEKÉRDEZÉSEK 1.A Listázza ki a Research nevű részleg minden dolgozójának nevét, azonosítóját, jövedelmét és főnökének azonosítóját. Megoldás SQL> SELECT ename, empno, sal+NVL(comm,0), mgr 2 FROM emp, dept 3 WHERE emp.deptno=deptdeptno AND 4 UPPER(dept.dname)='RESEARCH'; ENAME EMPNO SAL+NVL(COMM,0) MGR ---------- ---------- --------------- ---------SMITH 7369 800 7902 JONES 7566 2975 7839 SCOTT 7788 3000 7566 ADAMS 7876 1100 7788 FORD 7902 3000 7566 1.B Listázza ki a Dallas-i telephely minden dolgozójának nevét, munkakörét, fizetését és részlegének azonosítóját. Megoldás SQL> SELECT ename, job,

sal, dept.deptno 2 FROM emp, dept 3 WHERE emp.deptno=deptdeptno AND 4 UPPER(dept.loc)='DALLAS'; ENAME JOB SAL DEPTNO ---------- --------- ---------- ---------SMITH CLERK 800 20 JONES MANAGER 2975 20 SCOTT ANALYST 3000 20 ADAMS CLERK 1100 20 FORD ANALYST 3000 20 (SQL példatár) - 16 - 2. EGYTÁBLÁS CSOPORTOSÍTÓ LEKÉRDEZÉSEK 2.A Listázzuk főnökönként (mgr) a főnökhöz tartozó legkisebb dolgozói fizetéseket. Hagyjuk ki azon dolgozók fizetését, akiknek nincs főnökük, valamint azokat a csoportokat, ahol a legkisebb fizetés nagyobb 2000 USD-nél. Rendezzük a listát a legkisebb fizetések szerint növekvően Megoldás SQL> r 1 SELECT mgr AS főnök, 2 MIN(sal) AS "Legkisebb Fizetés" 3 FROM emp 4 WHERE mgr IS NOT NULL 5 GROUP BY mgr 6 HAVING MIN(sal) <= 2000 7* ORDER BY "Legkisebb Fizetés"; FŐNÖK Legkisebb Fizetés ---------- ----------------7902 800 7698 950 7788 1100 7782 1300 2.B Listázzuk főnökönként (mgr) a

főnökhöz tartozó dolgozói átlagfizetéseket. Hagyjuk ki azon dolgozók fizetését, akiknek nincs főnökük, valamint azokat a csoportokat, ahol az átlagfizetés nagyobb 3000 USD-nél. Rendezzük a listát az átlagfizetések szerint csökkenően Megoldás SQL> r 1 SELECT mgr AS főnök, 2 ROUND(AVG(sal)) AS "Átlagfizetés" 3 FROM emp 4 WHERE mgr IS NOT NULL 5 GROUP BY mgr 6 HAVING AVG(sal) <= 3000 7* ORDER BY AVG(sal) DESC; FŐNÖK Átlagfizetés ---------- -----------7839 2758 7698 1310 7782 1300 7788 1100 7902 800 (SQL példatár) - 17 - 3. TÖBBTÁBLÁS LEKÉRDEZÉSEK ALLEKÉRDEZÉSEKKEL 1 3.A Az emp és dept tábla felhasználásával listázza minden olyan alkalmazott nevét, részlegének nevét és fizetését, akiknek fizetése megegyezik valamelyik Dallas-ban dolgozó alkalmazottéval. Legyen a lista fejléce  NÉV, RÉSZLEGNÉV, FIZETÉS , és a lista legyen a  FIZETÉS, RÉSZLEGNÉV  szerint rendezett Megoldás SQL> SELECT

e.ename as név, 2 d.dname as "Részleg neve", 3 e.sal as fizetés 4 FROM emp e, dept d 5 WHERE (d.deptno = edeptno) AND 6 sal 7 IN 8 (SELECT sal 9 FROM emp e, dept d 10 WHERE d.loc = 'DALLAS' AND 11 d.deptno = edeptno) 12 ORDER BY fizetés, "Részleg neve"; NÉV Részleg neve FIZETÉS ---------- -------------- ---------SMITH RESEARCH 800 ADAMS RESEARCH 1100 JONES RESEARCH 2975 SCOTT RESEARCH 3000 FORD RESEARCH 3000 3.B Listázzuk a főnökeik (mgr) szerint csoportosítva azokat a dolgozókat, akiknek fizetése e csoportosítás szerint a legnagyobb, de kisebb 3000 USD-nél. A lista a fizetés csökkenő értéke szerint legyen rendezett. Legyen a lista fejléce  FŐNÖK KÓDJA, DOLGOZÓ NEVE, FIZETÉSE  Megoldás Először nézzük meg kik tartoznak egy egy főnökhöz SQL> BREAK ON főnök kódja 1 SELECT al.mgr AS főnök kódja, 2 ename AS dolgozó neve, 3 sal AS fizetés 4 FROM emp , 5 (SELECT mgr 6 FROM emp 7 GROUP BY mgr) al 8* WHERE

al.mgr = empmgr; FÖNÖK KÓDJA DOLGOZÓ NE FIZETÉS ---------- ---------- ---------7566 SCOTT 3000 FORD 3000 (SQL példatár) - 18 - 7698 ALLEN WARD JAMES TURNER MARTIN 7782 MILLER 7788 ADAMS 7839 JONES CLARK BLAKE 7902 SMITH 1600 1250 950 1500 1250 1300 1100 2975 2450 2850 800 13 sor kijelölve. Írjuk ki főnökönként a legnagyobb fizetésűeket, de csak azokat akiknek fizetése kisebb 3000USD-nél, és rendezzük legnagyobb fizetés szerint csökkenően. SQL> r 1 SELECT al.mgr AS főnök, 2 ename AS név, 3 sal AS fizetés max, 4 al.legnagyobb AS "Ez a maximum" 5 FROM emp, 6 (SELECT mgr, 7 MAX(sal) AS legnagyobb 8 FROM emp 9 GROUP BY mgr 10 HAVING MAX(sal) < 3000) al 11 WHERE al.mgr = empmgr AND 12 sal = al.legnagyobb 13* ORDER BY al.legnagyobb DESC; --csak ellenőrzésképp FŐNÖK NÉV FIZETÉS MAX Ez a maximum ---------- ---------- ---------- -----------7839 JONES 2975 2975 7698 ALLEN 1600 1600 7782 MILLER 1300 1300 7788 ADAMS 1100 1100 3.C

Listázza minden olyan alkalmazott azonosítóját és nevét, akik olyan részlegen dolgoznak, ahol van nevében „T” betűt tartalmazó alkalmazott. Legyen a lista fejléce  AZONOSÍTÓ, NÉV, RÉSZLEG NEVE , és a lista legyen  RÉSZLEG NEVE, NÉV  szerint rendezett. Megoldás SQL> r 1 SELECT empno AS AZONOSÍTÓ, 2 ename AS NÉV 3 FROM emp 4 WHERE deptno IN 5 (SELECT deptno 6 FROM emp 7* WHERE ename LIKE '%T%'); (SQL példatár) - 19 - AZONOSÍTÓ NÉV ---------- ---------7369 SMITH 7876 ADAMS 7902 FORD 7788 SCOTT 7566 JONES 7499 ALLEN 7698 BLAKE 7654 MARTIN 7900 JAMES 7844 TURNER 7521 WARD 11 sor kijelölve. Listázzuk a részleg helyét is, valamint loc, ename szerint rendezzük. SELECT empno as AZONOSÍTÓ, ename as NÉV, loc FROM emp,dept WHERE emp.deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%T%') AND emp.deptno = deptdeptno Order by loc,ename AZONOSÍTÓ NÉV LOC ---------- ---------- ------------7499 ALLEN CHICAGO 7698

BLAKE CHICAGO 7900 JAMES CHICAGO 7654 MARTIN CHICAGO 7844 TURNER CHICAGO 7521 WARD CHICAGO 7876 ADAMS DALLAS 7902 FORD DALLAS 7566 JONES DALLAS 7788 SCOTT DALLAS 7369 SMITH DALLAS 11 sor kijelölve. Ha a részlegnevét is listáztatjuk, valamint dname, ename szerint rendezett: 1 SELECT empno as AZONOSÍTÓ, 2 ename as NÉV, 3 dname 4 FROM emp,dept 5 WHERE emp.deptno IN 6 (SELECT deptno 7 FROM emp 8 WHERE ename LIKE '%T%') 9 AND emp.deptno = deptdeptno 10* ORDER BY dname,ename (SQL példatár) - 20 - AZONOSÍTÓ NÉV DNAME ---------- ---------- -------------7876 ADAMS RESEARCH 7902 FORD RESEARCH 7566 JONES RESEARCH 7788 SCOTT RESEARCH 7369 SMITH RESEARCH 7499 ALLEN SALES 7698 BLAKE SALES 7900 JAMES SALES 7654 MARTIN SALES 7844 TURNER SALES 7521 WARD SALES 11 sor kijelölve. 3.D Listázzuk a főnökeik (mgr) szerint csoportosítva azokat a dolgozókat, akiknek fizetése e csoportosítás szerint a legkisebb, de nagyobb 1000 USD-nél. A lista a fizetés növekvő

értéke szerint legyen rendezett Legyen a lista fejléce  FŐNÖK KÓDJA, DOLGOZÓ NEVE, FIZETÉSE  Megoldás SQL> r 1 SELECT al.mgr AS fönök kódja, 2 ename AS dolgozó neve, 3 sal AS fizetés 4 FROM emp, 5 (SELECT mgr, 6 MIN(sal) AS legkisebb 7 FROM emp 8 GROUP BY mgr 9 HAVING MIN(sal) > 1000) al 10 WHERE al.mgr = empmgr AND 11 sal = al.legkisebb 12* ORDER BY al.legkisebb FÖNÖK KÓDJA DOLGOZÓ NE FIZETÉS ----------- ---------- ---------7788 ADAMS 1100 7782 MILLER 1300 7839 CLARK 2450 7566 SCOTT 3000 7566 FORD 3000 3.E Listázzuk főnökönként (mgr) a legkisebb fizetésű dolgozókat. Hagyjuk ki azon dolgozók fizetését, akiknek nincs főnökük, valamint azokat a csoportokat, ahol a legkisebb fizetés nagyobb 3000 USD-nél Rendezzük a listát a legkisebb fizetések szerint növekvően (Lásd a 2.A feladatot) Megoldás A szükséges allekérdezés (lásd a 2.A-ban): (SQL példatár) - 21 - SQL> SELECT mgr AS Főnök, 2 MIN(sal) AS MinFiz 3 FROM emp 4

WHERE mgr IS NOT NULL 5 GROUP BY mgr 6 HAVING MIN(sal) <= 3000 7 ORDER BY MinFiz; FŐNÖK MINFIZ ---------- ---------7902 800 7698 950 7788 1100 7782 1300 7839 2450 7566 3000 6 sor kijelölve. A teljes lekérdezés: SQL> SELECT ename AS Név , 2 mgr AS Főnök, 3 sal AS Fizetés , 4 MinFiz AS Legkisebb Fizetés 5 FROM (SELECT mgr AS Főnök, 6 MIN(sal) AS MinFiz 7 FROM emp 8 WHERE mgr IS NOT NULL 9 GROUP BY mgr 10 HAVING MIN(sal) <= 3000 11 ORDER BY MinFiz) kisfizetés, 12 emp 13 WHERE kisfizetés.Főnök = empmgr AND 14 kisfizetés.MinFiz = empsal; NÉV FŐNÖK FIZETÉS LEGKISEBB FIZETÉS ---------- ---------- ---------- ----------------SCOTT 7566 3000 3000 FORD 7566 3000 3000 JAMES 7698 950 950 MILLER 7782 1300 1300 ADAMS 7788 1100 1100 CLARK 7839 2450 2450 SMITH 7902 800 800 7 sor kijelölve. Megjegyzés 1. A teljes lekérdezés azért tartalmaz eggyel több sort, mint az allekérdezés, mivel a Scott-nak és a Ford-nak egyaránt minimális a fizetése. 2. A teljes

lekérdezés eredménye még nem rendezett Az allekérdezés rendezettsége nem elég. Ki kell egészíteni a külső lekérdezést a rendezéssel (ekkor persze a belső lekérdezésben nem érdemes megtartani). SQL> SELECT ename AS Név , 2 mgr AS Főnök, 3 sal AS Fizetés , 4 MinFiz AS Legkisebb Fizetés 5 FROM (SELECT mgr AS Főnök, (SQL példatár) - 22 - 6 7 8 9 10 11 12 13 14 MIN(sal) AS MinFiz FROM emp WHERE mgr IS NOT NULL GROUP BY mgr HAVING MIN(sal) <= 3000) kisfizetés, emp WHERE kisfizetés.Főnök = empmgr AND kisfizetés.MinFiz = empsal ORDER BY Legkisebb Fizetés; NÉV FŐNÖK FIZETÉS LEGKISEBB FIZETÉS ---------- ---------- ---------- ----------------SMITH 7902 800 800 JAMES 7698 950 950 ADAMS 7788 1100 1100 MILLER 7782 1300 1300 CLARK 7839 2450 2450 SCOTT 7566 3000 3000 FORD 7566 3000 3000 7 sor kijelölve. (SQL példatár) - 23 - 4. TÖBBTÁBLÁS LEKÉRDEZÉSEK ALLEKÉRDEZÉSEKKEL 2 4.A Listázza ki az egyes részlegek telephelyének nevét, a

részlegek vezetőinek nevét, a fizetésüket, valamint a részleg dolgozóinak átlagjövedelmét (ennek adjon nevet) ez utóbbi adat szerint rendezve. Megoldás SQL> r 1 SELECT e.mgr, aldeptno, alatlag AS átlag 2 FROM emp e, 3 (SELECT deptno, AVG(sal+NVL(comm,0)) atlag 4 FROM emp 5 GROUP BY deptno) al 6* WHERE e.deptno = aldeptno MGR DEPTNO ÁTLAG ---------- ---------- ---------7839 10 2916.66667 10 2916.66667 7782 10 2916.66667 7902 20 2175 7788 20 2175 7566 20 2175 7566 20 2175 7839 20 2175 7698 30 1933.33333 7839 30 1933.33333 7698 30 1933.33333 7698 30 1933.33333 7698 30 1933.33333 7698 30 1933.33333 14 sor kijelölve. Ezután ezt helyezzük a külső SELECT-be, ahol a nevüket és a telephelyeket listázzuk. SQL> r 1 SELECT d.loc AS TELEPHELY, 2 e.ename AS "VEZETŐ NEVE", 3 e.sal AS "VEZETŐ FIZETÉSE", 4 ROUND(belso.átlag) AS "DOLGOZÓK ÁTLAGJÖVEDELME" 5 FROM emp e, dept d, 6 (SELECT e.mgr AS amgr, 7 al.deptno AS adeptno, 8 al.atlag AS

átlag 9 FROM emp e, 10 (SELECT deptno, AVG(sal+NVL(comm,0)) atlag 11 FROM emp 12 GROUP BY deptno) al 13 WHERE e.deptno = aldeptno) belso 14 WHERE (e.empno = belsoamgr) AND 15 (e.deptno = ddeptno) AND 16* (e.deptno = belsoadeptno) (SQL példatár) - 24 - TELEPHELY VEZETŐ NEV VEZETŐ FIZETÉSE DOLGOZÓK ÁTLAGJÖVEDELME ------------- ---------- --------------- ----------------------NEW YORK KING 5000 2917 NEW YORK CLARK 2450 2917 DALLAS FORD 3000 2175 DALLAS SCOTT 3000 2175 DALLAS JONES 2975 2175 DALLAS JONES 2975 2175 CHICAGO BLAKE 2850 1933 CHICAGO BLAKE 2850 1933 CHICAGO BLAKE 2850 1933 CHICAGO BLAKE 2850 1933 CHICAGO BLAKE 2850 1933 11 sor kijelölve. Az ismétlődéseket szűrve a belső SELECT-ből, és rendezve az átlagjövedelem szerint: SQL> r 1 SELECT d.loc AS TELEPHELY, 2 e.ename AS "VEZETŐ NEVE", 3 e.sal AS "VEZETŐ FIZETÉSE", 4 ROUND(belso.átlag) AS "DOLGOZÓK ÁTLAGJÖVEDELME" 5 FROM emp e, dept d, 6 (SELECT distinct e.mgr AS

amgr, 7 al.deptno AS adeptno, 8 al.atlag AS átlag 9 FROM emp e, 10 (SELECT deptno, AVG(sal+NVL(comm,0)) atlag 11 FROM emp 12 GROUP BY deptno) al 13 WHERE e.deptno = aldeptno ) belso 14 WHERE (e.empno = belsoamgr) AND 15 (e.deptno = ddeptno) AND 16 (e.deptno = belsoadeptno) 17* ORDER BY "DOLGOZÓK ÁTLAGJÖVEDELME"; TELEPHELY VEZETŐ NEV VEZETŐ FIZETÉSE DOLGOZÓK ÁTLAGJÖVEDELME ------------- ---------- --------------- ----------------------CHICAGO BLAKE 2850 1933 DALLAS JONES 2975 2175 DALLAS SCOTT 3000 2175 DALLAS FORD 3000 2175 NEW YORK CLARK 2450 2917 NEW YORK KING 5000 2917 6 sor kijelölve. Tehát 6 főnök van. 4.B Listázza ki mindazon dolgozók nevét, foglalkozását, telephelyét, valamint jövedelmük és a részlegük átlagjövedelme közti különbséget, akiknél a munkakörük átlagjövedelme kisebb az összes dolgozó átlagjövedelménél. A listát rendezze telephely szerint (SQL példatár) - 25 - Megoldás Nézzük meg munkakörönként az

átlagjövedelmet: SELECT emp.ename AS "NÉV", emp.job AS munkakör, dept.dname AS "részleg neve", részlegátlag – ROUND(emp.sal+NVL(comm,0)) AS "különbözet" FROM emp, dept, (SELECT deptno, ROUND(AVG(sal+NVL(comm,0)) ) AS részlegátla FROM emp GROUP BY deptno) részleg, (SELECT job FROM emp GROUP BY job HAVING AVG(sal+NVL(comm,0)) < -- munkaköri (SELECT AVG(sal+NVL(comm,0)) -- összes átlag FROM emp)) alulfizetett WHERE emp.job = alulfizetettjob AND emp.deptno = részlegdeptno AND emp.deptno = deptdeptno ORDER BY dept.loc; NÉV JOB LOC különbözet ---------- --------- ------------- ---------JAMES CLERK CHICAGO -983 ALLEN SALESMAN CHICAGO -33 TURNER SALESMAN CHICAGO -433 MARTIN SALESMAN CHICAGO 717 WARD SALESMAN CHICAGO -183 SMITH CLERK DALLAS -1375 ADAMS CLERK DALLAS -1075 MILLER CLERK NEW YORK -1617 8 sor kijelölve. Ellenőrzés: SQL> SELECT deptno, AVG(sal+NVL(comm,0)) AS részlegátlag 2 FROM emp 3 GROUP BY deptno; DEPTNO

RÉSZLEGÁTLAG ---------- -----------10 2916.66667 20 2175 30 1933.33333 SQL> SELECT job, 2 AVG(sal+NVL(comm,0)) munkaköriátlag 3 FROM emp 4 GROUP BY job 5 HAVING AVG(sal+NVL(comm,0)) < -- munkaköriátlag 6 (SELECT AVG(sal+NVL(comm,0)) -- összesátlag 7 FROM emp); JOB MUNKAKÖRIÁTLAG --------- -------------CLERK 1037.5 SALESMAN 1950 (SQL példatár) - 26 - SQL> SELECT AVG(sal+NVL(comm,0)) 2 FROM emp összesátlag ÖSSZESÁTLAG ----------2230.35714 4.C Listázza ki azon dolgozók nevét, munkakörét, jövedelmét, telephelyét, a munkakörük átlagjövedelmét, akiknek jövedelme a munkakörük átlagjövedelménél kisebb. A lista legyen a dolgozók neve szerint rendezve Megoldás SQL> SELECT job, ROUND(AVG(sal+NVL(comm,0))) AS átlagjövedelem 2 FROM emp 3 GROUP BY job; JOB ÁTLAGJÖVEDELEM --------- -------------ANALYST 3000 CLERK 1038 MANAGER 2758 PRESIDENT 5000 SALESMAN 1950 Kapcsoljuk össze a dept-el, hogy a telephelyet is kiírjuk, valamint

rendezzük név szerint: SQL> SELECT e.ename AS név, 2 e.job AS foglalkozás, 3 e.sal+NVL(ecomm,0) AS jövedelem, 4 d.loc AS telephely, 5 belso.átlagjövedelem AS "munkaköri átlagjövedelem" 6 FROM emp e,dept d, 7 (SELECT job, ROUND(AVG(sal+NVL(comm,0))) AS átlagjövedelem 8 FROM emp 9 GROUP BY job) belso 10 WHERE e.sal+NVL(ecomm,0) < belsoátlagjövedelem AND 11 e.job = belsojob AND 12 e.deptno = ddeptno 13 ORDER BY név; NÉV FOGLALKOZ JÖVEDELEM TELEPHELY munkaköri átlagjövedelem ---------- --------- ---------- ------------- -----------------------ALLEN SALESMAN 1900 CHICAGO 1950 CLARK MANAGER 2450 NEW YORK 2758 JAMES CLERK 950 CHICAGO 1038 SMITH CLERK 800 DALLAS 1038 TURNER SALESMAN 1500 CHICAGO 1950 WARD SALESMAN 1750 CHICAGO 1950 6 sor kijelölve. (SQL példatár) - 27 - 4.D Listázza ki mindazon dolgozók nevét, foglalkozását, részlegük nevét, valamint részlegük átlagjövedelme és saját jövedelmük közti különbséget, akiknek a

munkaköri átlagjövedelme kisebb az összes dolgozó átlagjövedelménél. A listát rendezze a részleg neve szerint Megoldás SQL> SELECT emp.ename AS "NÉV", 2 emp.job AS munkakör, 3 dept.dname AS "részleg neve", 4 részlegátlag - emp.sal+NVL(comm,0) AS "különbözet" 5 FROM emp, dept, 6 (SELECT deptno, ROUND(AVG(sal+NVL(comm,0)) ) AS részlegátlag 7 FROM emp 8 GROUP BY deptno) részleg, 9 (SELECT job 10 FROM emp 11 GROUP BY job 12 HAVING AVG(sal+NVL(comm,0)) < -- munkaköri 13 (SELECT AVG(sal+NVL(comm,0)) -- összes átlag 14 FROM emp)) alulfizetett 15 WHERE emp.job = alulfizetettjob AND 16 emp.deptno = részlegdeptno AND 17 emp.deptno = deptdeptno 18 ORDER BY dept.dname; NÉV MUNKAKÖR részleg neve különbözet ---------- --------- -------------- ---------MILLER CLERK ACCOUNTING 1617 SMITH CLERK RESEARCH 1375 ADAMS CLERK RESEARCH 1075 JAMES CLERK SALES 983 ALLEN SALESMAN SALES 33 MARTIN SALESMAN SALES -717 TURNER SALESMAN SALES 433

WARD SALESMAN SALES 183 (SQL példatár) - 28 - 5. TÖBBTÁBLÁS LEKÉRDEZÉSEK ALLEKÉRDEZÉSEKKEL 3 5.A Listázza ki minden dolgozó nevét, munkakörét, telephelyét, fizetését, valamint a saját telephelyén a munkakörének átlagfizetését, és telephelyének átlagfizetését. A listát munkakör és telephely szerint növekvő módon rendezze Megoldás SELECT emp.ename, emp.job, dept.loc, emp.sal, sLocJob.loc job sal, sLoc.loc sal FROM emp, dept, (SELECT deptno, job, ROUND(AVG(sal)) loc job sal FROM emp GROUP BY deptno, job) sLocJob, (SELECT deptno, ROUND(AVG(sal)) loc sal FROM emp GROUP BY deptno) sLoc WHERE emp.deptno=deptdeptno AND emp.deptno=sLocJobdeptno AND emp.deptno=sLocdeptno AND emp.job=sLocJobjob ORDER BY emp.job ASC, dept.loc ASC; ENAME JOB LOC SAL LOC JOB SAL LOC SAL ---------- --------- ------------- ---------- ----------- ---------FORD ANALYST DALLAS 3000 3000 2175 SCOTT ANALYST DALLAS 3000 3000 2175 JAMES CLERK CHICAGO 950 950 1567 SMITH CLERK DALLAS

800 950 2175 ADAMS CLERK DALLAS 1100 950 2175 MILLER CLERK NEW YORK 1300 1300 2917 BLAKE MANAGER CHICAGO 2850 2850 1567 JONES MANAGER DALLAS 2975 2975 2175 CLARK MANAGER NEW YORK 2450 2450 2917 KING PRESIDENT NEW YORK 5000 5000 2917 ALLEN SALESMAN CHICAGO 1600 1400 1567 MARTIN SALESMAN CHICAGO 1250 1400 1567 TURNER SALESMAN CHICAGO 1500 1400 1567 WARD SALESMAN CHICAGO 1250 1400 1567 14 sor kijelölve. 5.B Listázza ki minden dolgozó nevét, munkakörét, telephelyét, fizetését valamint fizetését a saját telephelyén a munkaköre átlagfizetésének százalékában,és a fizetését a saját telephelye átlagfizetésének százalékábana százalékértékeket egészként kiíratva) a munkakör és a telephely szerint növekvő módon rendezve. (SQL példatár) - 29 - Megoldás SELECT emp.ename, emp.job, dept.loc, emp.sal, ROUND((emp.sal/sLocJobloc job sal)*100) relatív fiz1, ROUND((emp.sal/sLocloc sal)*100) relatív fiz2 FROM emp, dept, (SELECT deptno, job,

ROUND(AVG(sal)) loc job sal FROM emp GROUP BY deptno, job) sLocJob, (SELECT deptno, ROUND(AVG(sal)) loc sal FROM emp GROUP BY deptno) sLoc WHERE emp.deptno=deptdeptno AND emp.deptno=sLocJobdeptno AND emp.deptno=sLocdeptno AND emp.job=sLocJobjob ORDER BY emp.job ASC, dept.loc ASC; ENAME JOB LOC SAL RELATÍV FIZ1 RELATÍV FIZ2 ---------- --------- ------------- ---------- ------------ -----------FORD ANALYST DALLAS 3000 100 138 SCOTT ANALYST DALLAS 3000 100 138 JAMES CLERK CHICAGO 950 100 61 SMITH CLERK DALLAS 800 84 37 ADAMS CLERK DALLAS 1100 116 51 MILLER CLERK NEW YORK 1300 100 45 BLAKE MANAGER CHICAGO 2850 100 182 JONES MANAGER DALLAS 2975 100 137 CLARK MANAGER NEW YORK 2450 100 84 KING PRESIDENT NEW YORK 5000 100 171 ALLEN SALESMAN CHICAGO 1600 114 102 MARTIN SALESMAN CHICAGO 1250 89 80 TURNER SALESMAN CHICAGO 1500 107 96 WARD SALESMAN CHICAGO 1250 89 80 14 sor kijelölve. 5.C Listázza munkakörönként azon dolgozókat, akiknek fizetése több a munkakörük átlagfizetésénél.

Megoldás 1. Megoldás FROM-beli allekérdezéssel (inline-nézettel): SELECT ename Név, sal Fizetés, emp.job Munkakör, Átlagfizetés FROM emp, (SELECT job, ROUND(AVG(sal)) Átlagfizetés FROM emp GROUP BY job) ee WHERE (emp.job = eejob) and (SQL példatár) - 30 - (sal > Átlagfizetés) ORDER BY Név; 2. Megoldás WHERE-beli allekérdezéssel: SELECT ename Név, sal Fizetés, job Munkakör FROM emp e WHERE sal > (SELECT ROUND(AVG(sal)) FROM emp GROUP BY job HAVING job = e.job) ORDER BY Név; A lista mindkét megoldás esetén: NÉV FIZETÉS MUNKAKÖR ÁTLAGFIZETÉS ---------- ---------- --------- -----------ADAMS 1100 CLERK 1038 ALLEN 1600 SALESMAN 1400 BLAKE 2850 MANAGER 2758 JONES 2975 MANAGER 2758 MILLER 1300 CLERK 1038 TURNER 1500 SALESMAN 1400 6 sor kijelölve. 5.D Listázza a legkisebb átlagfizetésű részleg dolgozóit, e részleg nevét és átlagfizetését. Megoldás 1. Megoldás FROM-beli allekérdezésekkel: SELECT ename AS név, sal AS fizetés,

dname, minfiz FROM emp, dept, (SELECT deptno, ROUND(AVG(sal)) AS atlagfiz FROM emp GROUP BY deptno) részleg, (SELECT MIN(atlagfiz) minfiz FROM (SELECT deptno, ROUND(AVG(sal)) AS atlagfiz FROM emp GROUP BY deptno)) minrészleg WHERE részleg.atlagfiz = minrészlegminfiz AND dept.deptno = empdeptno AND emp.deptno = részlegdeptno; 2. Megoldás redukált táblával: SELECT ename sal dname AS név, AS fizetés, AS telephely, (SQL példatár) - 31 - részlegátlag.rátlag AS minfiz FROM emp, dept, (SELECT deptno AS részleg, ROUND(AVG(sal)) AS rátlag FROM emp GROUP BY deptno HAVING ROUND(AVG(sal)) = (SELECT MIN(rátlag) AS minátlag FROM (SELECT deptno AS részleg, ROUND(AVG(sal)) AS rátlag FROM emp GROUP BY deptno))) részlegátlag WHERE részleg = emp.deptno AND emp.deptno = deptdeptno; 3. Megoldás nézettáblával: 1.rész (Nézettábla létrehozása) CREATE view részleg AS SELECT deptno, ROUND(AVG(sal)) AS atlagfiz FROM emp GROUP BY deptno; 2.rész (Lekérdezés a

nézettáblára való hivatkozással) SELECT ename AS név, Sal AS fizetés, dname AS telephely, minfiz FROM emp, dept, részleg, (SELECT MIN(atlagfiz) minfiz FROM részleg) minrészleg WHERE részleg.atlagfiz=minrészlegminfiz dept.deptno=empdeptno AND emp.deptno=részlegdeptno; AND A lista mindhárom megoldás esetén: NÉV FIZETÉS TELEPHELY MINFIZ ---------- ---------- -------------- ---------ALLEN 1600 SALES 1567 BLAKE 2850 SALES 1567 MARTIN 1250 SALES 1567 JAMES 950 SALES 1567 TURNER 1500 SALES 1567 WARD 1250 SALES 1567 6 sor kijelölve. A 2. Megoldás gondolatmenete: Listázzuk ki először a legkisebb átlagfizetésű részleg dolgozóit: (SQL példatár) - 32 - 1. részlegátlag: SELECT deptno, ROUND(AVG(sal)) FROM emp GROUP BY deptno; DEPTNO ROUND(AVG(SAL)) ---------- --------------10 2917 20 2175 30 1567 2. minimum átlag SQL> SELECT MIN(rátlag) AS minátlag 2 FROM 3 (SELECT deptno AS részleg, 4 ROUND(AVG(sal)) AS rátlag 5 FROM emp 6 GROUP BY deptno);

MINÁTLAG ---------1567 3. melyik részlegnek az átlaga ez? SQL> SELECT deptno AS részleg, 2 ROUND(AVG(sal)) AS rátlag 3 FROM emp 4 GROUP BY deptno 5 HAVING ROUND(AVG(sal)) = 6 (SELECT MIN(rátlag) AS minátlag 7 FROM 8 (SELECT deptno AS részleg, 9 ROUND(AVG(sal)) AS rátlag 10 FROM emp 11 GROUP BY deptno)); RÉSZLEG RÁTLAG ---------- ---------30 1567 Külső ciklus: beágyazva a SELECT listába: SQL> SELECT ename AS név, 2 sal AS fizetés, 3 dname AS telephely, 4 részlegátlag.rátlag AS minfiz 5 FROM emp, dept, 6 (SELECT deptno AS részleg, 7 ROUND(AVG(sal)) AS rátlag 8 FROM emp 9 GROUP BY deptno 10 HAVING ROUND(AVG(sal)) = 11 (SELECT MIN(rátlag) AS minátlag 12 FROM 13 (SELECT deptno AS részleg, 14 ROUND(AVG(sal)) AS rátlag 15 FROM emp (SQL példatár) - 33 - 16 17 18 GROUP BY deptno))) részlegátlag WHERE részleg = emp.deptno AND emp.deptno = deptdeptno; NÉV FIZETÉS TELEPHELY MINFIZ ---------- ---------- -------------- ---------ALLEN 1600 SALES 1567

BLAKE 2850 SALES 1567 MARTIN 1250 SALES 1567 JAMES 950 SALES 1567 TURNER 1500 SALES 1567 WARD 1250 SALES 1567 6 sor kijelölve. 5.E Listázza a legkisebb létszámú foglalkozási csoport dolgozóinak nevét és foglalkozását. Megoldás 1. Megoldás FROM-beli allekérdezésekkel (lépésenként): a.) A dolgozók száma foglalkozásokként: SELECT COUNT(ename), job FROM emp WHERE job <> UPPER('president') GROUP BY job; COUNT(ENAME) JOB ------------ --------2 ANALYST 4 CLERK 3 MANAGER 1 PRESIDENT 4 SALESMAN b.) A legkisebb létszám: SELECT MIN(Kisebb) AS kicsi létszám FROM (SELECT COUNT(ename) AS Kisebb FROM emp WHERE job <> UPPER('PRESIDENT') GROUP BY job); KICSI LÉTSZÁM ------------2 c.) Legkisebb létszámú foglalkozási csoport foglalkozásának neve: SELECT job FROM emp GROUP BY job HAVING COUNT(job) = (SELECT MIN(KISEBB) AS kicsi létszám FROM (SELECT COUNT(ename) AS KISEBB (SQL példatár) - 34 - FROM emp WHERE job <>

UPPER('president') GROUP BY JOB)); JOB --------ANALYST d.) Azon dolgozók neve és foglalkozása, akik ebben a csoportban dolgoznak: SQL> r 1 SELECT ename, emp.job 2 FROM emp, 3 (SELECT job 4 FROM emp 5 GROUP BY job 6 HAVING COUNT(job) = 7 (SELECT MIN(Kisebb) AS kicsi létszám 8 FROM 9 (SELECT COUNT(ename) AS Kisebb 10 FROM emp 11 WHERE job <> UPPER('president') 12 GROUP BY job))) legkisebb foglalkozás 13* WHERE emp.job = legkisebb foglalkozásjob; ENAME JOB ---------- --------SCOTT ANALYST FORD ANALYST 2 Megoldás NézetTáblával (lépésenként): a.) A legkisebb létszám (a NézetTábla létrehozása): CREATE VIEW legkisebb AS (SELECT MIN(Kisebb) AS kicsi létszám FROM (SELECT COUNT(ename) AS Kisebb FROM emp WHERE job <> UPPER('president') GROUP BY job)); A nézet létrejött. SQL> SELECT * FROM legkisebb; KICSI LÉTSZÁM ------------2 b.) Legkisebb létszámú foglalkozási csoport foglalkozásának neve (A NézetTábla

felhasználása): SELECT job FROM emp GROUP BY job HAVING COUNT(job) = (SELECT * FROM legkisebb); (SQL példatár) - 35 - JOB --------ANALYST c.) Azon dolgozók neve és foglalkozása, akik ebben a csoportban dolgoznak: SQL> r 1 SELECT ename, emp.job 2 FROM emp, 3 (SELECT job 4 FROM emp 5 GROUP BY job 6 HAVING COUNT(job) = 7 (SELECT * FROM legkisebb)) legkisebb létszám 8* WHERE emp.job = legkisebb létszámjob; ENAME JOB ---------- --------SCOTT ANALYST FORD ANALYST (SQL példatár) - 36 - 6. INTERAKTÍV SQL PROGRAMOK 6.A Mit csinál az alábbi Példa.sql script program? set verify off SELECT * FROM &tabla1; SELECT * FROM &&tabla2; ACCEPT tabla3 PROMPT "A tábla neve: " SELECT * FROM &tabla3; DEFINE tabla4="salgrade" SELECT * FROM &tabla4; DEFINE ACCEPT abc PROMPT "Táblanevek törlése: (Üsd le az Enter billentyűt.)" UNDEFINE tabla2 UNDEFINE tabla3 UNDEFINE tabla4 UNDEFINE abc DEFINE set verify on Megoldás A

Példa.sql script program futtatásának eredménye: SQL> @példa Adja meg a(z) tabla1 értékét: dept DEPTNO DNAME LOC ---------- -------------- ------------10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Adja meg a(z) tabla2 értékét: emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 800 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7900 JAMES CLERK 7698 81-DEC-03 950 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7934 MILLER CLERK 7782 82-JAN-23

1300 10 14 sor kijelölve. (SQL példatár) - 37 - A tábla neve: salgrade GRADE LOSAL HISAL ---------- ---------- ---------1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 GRADE LOSAL HISAL ---------- ---------- ---------1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 DEFINE CONNECT IDENTIFIER = "myora" (CHAR) DEFINE SQLPLUS RELEASE = "902000100" (CHAR) DEFINE EDITOR = "Notepad" (CHAR) DEFINE O VERSION = "Personal Oracle9i Release 9.2010 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2010 - Production" (CHAR) DEFINE O RELEASE = "902000100" (CHAR) DEFINE RC = "1" (CHAR) DEFINE TABLA2 = "emp" (CHAR) DEFINE TABLA3 = "salgrade" (CHAR) DEFINE TABLA4 = "salgrade" (CHAR) Táblanevek törlése: (Üsd le az Enter billentyűt.) DEFINE CONNECT IDENTIFIER = "myora" (CHAR) DEFINE SQLPLUS RELEASE = "902000100"

(CHAR) DEFINE EDITOR = "Notepad" (CHAR) DEFINE O VERSION = "Personal Oracle9i Release 9.2010 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2010 - Production" (CHAR) DEFINE O RELEASE = "902000100" (CHAR) DEFINE RC = "1" (CHAR) 6.B Listázza ki rendezve azon foglalkozási csoportok dolgozóinak nevét, amelyekbe a felhasználó által megadott számnál többen tartoznak. A lista fejléce  Foglalkozás, Azonosító, Név, Csoportlétszám  Megoldás SQL> SELECT job, COUNT(ename) AS létszám 2 FROM emp 3 GROUP BY job 4 HAVING COUNT(ename) > &szam; Adja meg a(z) szam értékét: 2 régi 4: HAVING COUNT(ename) > &szam új 4: HAVING COUNT(ename) > 2 JOB LÉTSZÁM --------- ---------CLERK 4 MANAGER 3 SALESMAN 4 (SQL példatár) - 38 - Ha a felhasználó 2-t ad meg, akkor 3 foglalkozási csoport van, amelyiknek a létszáma kettőnél nagyobb. Ez a halmaz jön ki a

nézettáblából Feltétel az, hogy az ebből kijövő job egyenlő legyen a külső select job-jával SQL> r 1 SELECT e.job as FOGLALKOZÁS, 2 e.empno as AZONOSÍTÓ, 3 e.ename as NÉV, 4 al.létszám 5 FROM emp e, 6 (SELECT job, count(ename) as létszám 7 FROM emp 8 GROUP BY job 9 HAVING Count(ename) > &szam) al 10* WHERE e.job = aljob; Adja meg a(z) szam értékét: 2 régi 9: HAVING Count(ename) > &szam) al új 9: HAVING Count(ename) > 2) al FOGLALKOZ AZONOSÍTÓ NÉV LÉTSZÁM --------- ---------- ---------- ---------CLERK 7369 SMITH 4 CLERK 7876 ADAMS 4 CLERK 7934 MILLER 4 CLERK 7900 JAMES 4 MANAGER 7566 JONES 3 MANAGER 7782 CLARK 3 MANAGER 7698 BLAKE 3 SALESMAN 7499 ALLEN 4 SALESMAN 7654 MARTIN 4 SALESMAN 7844 TURNER 4 SALESMAN 7521 WARD 4 11 sor kijelölve. 6.C Listázza ki azon részlegek telephelyét, a részlegek tagjainak nevét és jövedelmüket, ahol az átlagjövedelem meghaladja a felhasználó által megadott értéket. A lista fejléce 

Telephely, Név, Jövedelem . Megoldás Nézzük meg először a részlegenkénti átlagot: SQL> SELECT deptno, AVG(sal+NVL(comm,0)) 2 FROM emp 3 GROUP BY deptno; DEPTNO AVG(SAL+NVL(COMM,0)) ---------- -------------------10 2916.66667 20 2175 30 1933.33333 SQL> SELECT loc, ename, sal+NVL(comm,0) 2 FROM emp, dept, 3 (SELECT deptno, AVG(sal+NVL(comm,0)) (SQL példatár) - 39 - 4 FROM emp 5 GROUP BY deptno 6 HAVING AVG(sal+NVL(comm,0)) > &szam) belso 7 WHERE emp.deptno = belsodeptno AND 8 emp.deptno = deptdeptno; Adja meg a(z) szam értékét: 2000 régi 6: HAVING AVG(sal+NVL(comm,0)) > &szam) belso új 6: HAVING AVG(sal+NVL(comm,0)) > 2000) belso LOC ENAME SAL+NVL(COMM,0) ------------- ---------- --------------NEW YORK CLARK 2450 NEW YORK KING 5000 NEW YORK MILLER 1300 DALLAS SMITH 800 DALLAS ADAMS 1100 DALLAS FORD 3000 DALLAS SCOTT 3000 DALLAS JONES 2975 8 sor kijelölve. (SQL példatár) - 40 - 7. SQL ZÁRTHELYI DOLGOZAT A1. Listázza ki a

Research nevű részleg minden dolgozójának nevét, azonosítóját, jövedelmét és főnökének azonosítóját. A2. Listázza ki a felhasználó által megadott jövedelmi tartományba eső dolgozókat Legyen a lista fejléce TELEPHELY, DOLGOZÓ NÉV, MUNKAKÖR, JÖVEDELEM . A lista legyen a TELEPHELY és a DOLGOZÓ NÉV szerint növekvően rendezett. A3. Listázza ki főnökeik (mgr) szerint csoportosítva a főnökkel rendelkező dolgozókat, valamint a fizetésük és a csoportjuk átlagfizetése közötti különbséget. Legyen a lista fejléce  Főnök Kód, Főnök Név, Telephely, Dolgozó Név, Különbség . A lista a Főnök Név szerint növekvően, a Különbség szerint pedig csökkenően legyen rendezett. B1. Listázza ki a Dallas-i telephely minden dolgozójának nevét, munkakörét, fizetését és részlegének azonosítóját. B2. Listázza ki a felhasználó által megadott azonosítójú főnök (mgr) beosztottjait Legyen a lista fejléce

TELEPHELY, DOLGOZÓ NÉV, JÖVEDELEM, FŐNÖK KÓD. A lista legyen a TELEPHELY és a DOLGOZÓ NÉV szerint növekvően rendezett. B3. Listázza ki a munkakörük szerint csoportosítva a dolgozókat, valamint a csoportjuk legnagyobb jövedelme és a saját jövedelmük közötti különbséget. Legyen a lista fejléce Munkakör, Dolgozó Név, Különbség, Főnök Név, Telephely . A lista a Munkakör szerint növekvően, a Különbség szerint pedig csökkenően legyen rendezett. Megoldások: A1 SQL> SELECT ename, empno, sal+NVL(comm,0), mgr 2 FROM emp, dept 3 WHERE emp.deptno=deptdeptno AND 4 dept.dname=UPPER('Research'); ENAME EMPNO SAL+NVL(COMM,0) MGR ---------- ---------- --------------- ---------SMITH 7369 800 7902 JONES 7566 2975 7839 SCOTT 7788 3000 7566 ADAMS 7876 1100 7788 FORD 7902 3000 7566 A2 SQL> SELECT loc AS TELEPHELY, 2 ename AS DOLGOZÓ NÉV, 3 job AS MUNKAKÖR, 4 sal+NVL(comm,0) AS JÖVEDELEM 5 FROM emp, dept 6 WHERE

emp.deptno=deptdeptno AND 7 sal+NVL(comm,0) BETWEEN &minjöv AND &maxjöv 8 ORDER BY loc, ename; (SQL példatár) - 41 - Adja meg a(z) minjöv értékét: 1000 Adja meg a(z) maxjöv értékét: 2500 régi 7: sal+NVL(comm,0) BETWEEN &minjöv AND &maxjöv új 7: sal+NVL(comm,0) BETWEEN 1000 AND 2500 TELEPHELY DOLGOZÓ NÉ MUNKAKÖR JÖVEDELEM ------------- ---------- --------- ---------CHICAGO ALLEN SALESMAN 1900 CHICAGO TURNER SALESMAN 1500 CHICAGO WARD SALESMAN 1750 DALLAS ADAMS CLERK 1100 NEW YORK CLARK MANAGER 2450 NEW YORK MILLER CLERK 1300 6 sor kijelölve. A3 1. Megoldás FROM-beli allekérdezésekkel: COLUMN „Dolgozó Név” HEADING „Dolgozó Név” FORMAT A12 COLUMN „Telephely” HEADING „Telephely” FORMAT A10 SELECT főnök1.főnök kód AS „Főnök Kód”, főnök2.főnök név AS „Főnök Név”, dept.loc AS „Telephely”, főnök2.dolgozó név AS „Dolgozó Név”, főnök1.főnök átlagfőnök2dolgozó fizetés AS

„Különbség” FROM dept, (SELECT mgr AS főnök kód, ROUND(AVG(sal)) AS főnök átlag FROM emp WHERE mgr IS NOT NULL GROUP BY mgr) főnök1, (SELECT főnök.ename AS főnök név, főnök.empno AS főnök kód, dolgozó.ename AS dolgozó név, dolgozó.sal AS dolgozó fizetés, dolgozó.deptno AS dolgozó részleg FROM emp dolgozó, emp főnök WHERE dolgozó.mgr=főnökempno) főnök2 WHERE dept.deptno=főnök2dolgozó részleg AND főnök1.főnök kód=főnök2főnök kód ORDER BY „Főnök Név”, „Különbség” DESC; CLEAR COLUMNS 2. Megoldás külső SELECT-beli összekapcsolásokkal: SELECT e.mgr AS Főnök kód, f.ename AS Főnök név, d.loc AS Telephely, e.ename AS Dolgozó név, al.átlagfiz-esal AS Különbség FROM emp e, emp f, dept d, (SELECT ROUND(AVG(sal)) AS átlagfiz, (SQL példatár) - 42 - mgr AS Főnök FROM emp GROUP BY mgr) al WHERE f.empno = emgr e.deptno = ddeptno e.mgr = alFőnök e.mgr IS NOT NULL ORDER BY Főnök név ASC, Különbség

DESC; AND AND AND Mindkét megoldás listája: Főnök Kód Főnök Név Telephely Dolgozó Név Különbség ---------- ---------- ---------- ------------ ---------7698 BLAKE CHICAGO JAMES 360 7698 BLAKE CHICAGO WARD 60 7698 BLAKE CHICAGO MARTIN 60 7698 BLAKE CHICAGO TURNER -190 7698 BLAKE CHICAGO ALLEN -290 7782 CLARK NEW YORK MILLER 0 7902 FORD DALLAS SMITH 0 7566 JONES DALLAS SCOTT 0 7566 JONES DALLAS FORD 0 7839 KING NEW YORK CLARK 308 7839 KING CHICAGO BLAKE -92 7839 KING DALLAS JONES -217 7788 SCOTT DALLAS ADAMS 0 13 sor kijelölve. B1 SQL> SELECT ename, job, sal,dept.deptno 2 FROM emp, dept 3 WHERE emp.deptno=deptdeptno AND 4 dept.loc=UPPER('dallas') ENAME JOB SAL DEPTNO ---------- --------- ---------- ---------SMITH CLERK 800 20 JONES MANAGER 2975 20 SCOTT ANALYST 3000 20 ADAMS CLERK 1100 20 FORD ANALYST 3000 20 B2 SQL> SELECT loc AS TELEPHELY, 2 ename AS DOLGOZÓ NÉV, 3 sal+NVL(comm,0) AS JÖVEDELEM, 4 mgr AS FÖNÖK KÓD 5 FROM emp, dept 6 WHERE

emp.deptno=deptdeptno AND 7 mgr = &fönökkód 8 ORDER BY loc, ename Adja meg a(z) fönökkód értékét: 7698 régi 7: mgr = &fönökkód új 7: mgr = 7698 (SQL példatár) - 43 - TELEPHELY DOLGOZÓ NÉ JÖVEDELEM FÖNÖK KÓD ------------- ---------- ---------- ---------CHICAGO ALLEN 1900 7698 CHICAGO JAMES 950 7698 CHICAGO MARTIN 2650 7698 CHICAGO TURNER 1500 7698 CHICAGO WARD 1750 7698 B3 1. Megoldás FROM-beli allekérdezésekkel: COLUMN „Dolgozó Név” HEADING „Dolgozó Név” FORMAT A12 COLUMN „Telephely” HEADING „Telephely” FORMAT A10 SELECT job AS „Munkakör”, főnök1.dolgozó név AS „Dolgozó Név”, munkakör.Legnagyobb Jövedelem – főnök1.dolgozó fizetés AS „Különbség”, főnök1.főnök név AS „Főnök Név”, loc AS „Telephely” FROM dept, (SELECT főnök.ename AS főnök név, főnök.empno AS főnök kód, dolgozó.ename AS dolgozó név, dolgozó.sal AS dolgozó fizetés, dolgozó.deptno AS dolgozó részleg,

dolgozó.job AS dolgozó munkakör FROM emp dolgozó, emp főnök WHERE dolgozó.mgr=főnökempno) főnök1, (SELECT job, MAX(sal+ NVL(Comm,0))AS Legnagyobb Jövedelem FROM emp GROUP BY job) munkakör WHERE dept.deptno = főnök1dolgozó részleg AND munkakör.job = főnök1 dolgozó munkakör ORDER BY „Munkakör”, „Különbség” DESC; 2. Megoldás külső SELECT-beli összekapcsolásokkal: SELECT e.job AS Munkakör, e.ename AS Dolgozó név, al.átlagfiz esal AS Különbség, f.ename AS FŐnök név, d.loc AS Telephely FROM emp e, emp f, dept d, (SELECT MAX(sal+NVL(comm,0) AS átlagfiz, job AS MUnkakör FROM emp GROUP BY job) al WHERE f.empno = emgr AND e.deptno = ddeptno AND e.job = alMunkakör AND e.mgr IS NOT NULL ORDER BY Munkakör, Különbség DESC; (SQL példatár) - 44 - Mindkét megoldás listája: Munkakör Dolgozó Név Különbség Főnök Név Telephely --------- ------------ ---------- ---------- ---------ANALYST SCOTT 0 JONES DALLAS ANALYST FORD 0 JONES

DALLAS CLERK SMITH 500 FORD DALLAS CLERK JAMES 350 BLAKE CHICAGO CLERK ADAMS 200 SCOTT DALLAS CLERK MILLER 0 CLARK NEW YORK MANAGER CLARK 525 KING NEW YORK MANAGER BLAKE 125 KING CHICAGO MANAGER JONES 0 KING DALLAS SALESMAN MARTIN 1400 BLAKE CHICAGO SALESMAN WARD 1400 BLAKE CHICAGO SALESMAN TURNER 1150 BLAKE CHICAGO SALESMAN ALLEN 1050 BLAKE CHICAGO 13 sor kijelölve. CLEAR COLUMNS Ellenőrzés: SQL> select max(sal), job 2 from emp 3 group by job; MAX(SAL) JOB ---------- --------3000 ANALYST 1300 CLERK 2975 MANAGER 5000 PRESIDENT 1600 SALESMAN (SQL példatár) - 45 - 8. AZ SQL DDL-DML UTASÍTÁSAINAK HASZNÁLATA 8.A Hozzon létre az emp táblából egy dolg nevű táblát, és nevezze át annak ename oszlopát azonosító névre. Megoldás (script program) DROP TABLE dolg; CREATE TABLE dolg AS SELECT * FROM emp; DESC dolg; ALTER TABLE dolg RENAME COLUMN ename TO azonosító; DESC dolg; A script program futtatásának eredménye: SQL> DROP TABLE dolg; A tábla eldobva.

SQL> CREATE TABLE dolg AS SELECT * FROM emp; A tábla létrejött. SQL> DESC dolg; Név Üres? Típus ----------------------------- -------- -------------------EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> ALTER TABLE dolg 2 RENAME COLUMN ename TO azonosító; A tábla módosítva. SQL> DESC dolg; Név Üres? Típus ----------------------------- -------- -------------------EMPNO NOT NULL NUMBER(4) AZONOSÍTÓ VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) (SQL példatár) - 46 - 8.B (Feladatsor) B.1) Törölje a dolgozo nevű táblát B.2) Hozzon létre egy dolgozo nevű táblát az emp tábla azon dolgozóiból, akiknek 500 USD-nél több jövedelmük, és 1980. január 1-e után léptek be B.3) Nevezze át a tábla oszlopneveit értelemszerűen a következőkre: AZONOSÍTÓ, NÉV, MUNKAKÖR, FŐNÖK ID,

BELÉPÉS, FIZETÉS, JUTALÉK, RÉSZLEG ID. B.4) Bővítse a dolgozó táblát a LAKHELY oszloppal B.5) Töltse fel a tábla LAKHELY oszlopát a következőképpen: a.) aki New York-ban dolgozik, annak lakhelye is New York, de King Long Beach-en lakik, b.) a Dallas-i clerk munkakörűek Garland-ban, a többiek Dallas-ban laknak, c.) a Chicago-ban dolgozók Boston-ban, és a Boston-ban dolgozók Chicago-ban laknak, kivéve azon Chicago-i dolgozokat, akiknek Blake a főnökük, mert azok Indianapolisban laknak, feltéve, hogy nem clerk munkakörűek, mert akkor sehol sem laknak. B.6) Bővítse a táblát Kováccsal, akinek a foglalkozása nagyfőnök, 9999 az azonosítója, 2001. január 5-én lépett be a céghez, 9999 USD a fizetése, ugyanennyi a jutaléka, a 99-es azonosítójú Nagyfőnökség a munkahelye Budapesten, a King nevű alkalmazott a beosztottja, és Soroksáron lakik. E bővítéshez tegyen mentési pontot B.7) Bővítse a táblát a következő alkalmazottal: Neve:

Kelemen; Azonosítója: 1111; Munkaköre: megegyezik a legkisebb fizetésű dolgozó munkakörével, Főnöke: megegyezik Ford főnökével, Belépése: az aktuális dátum, Fizetése: a legnagyobb munkaköri átlagfizetés, de kisebb mint 5000 USD, Jutaléka: 111 USD, Részlege: megegyezik a budapesti részleg számával, Lakóhelye: Soroksár. A bővitéshez tegyen mentési pontot. B.8) Bővítse a táblát még két dolgozóval, majd érvénytelenítse a bővítéseket, és véglegesítse az adattáblát Megoldás B.1) SQL> DROP TABLE dolgozo; A tábla eldobva. B.2) SQL> CREATE TABLE dolgozo 2 AS 3 SELECT * 4 FROM emp 5 WHERE sal+NVL(comm,0) >500 AND 6 hiredate > '1980. jan 1' (SQL példatár) - 47 - A tábla létrejött. SQL> SELECT * FROM dolgozo; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 800 20 7499 ALLEN SALESMAN 7698 81-FEB-20

1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7900 JAMES CLERK 7698 81-DEC-03 950 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7934 MILLER CLERK 7782 82-JAN-23 1300 10 14 sor kijelölve. B.3) ALTER TABLE RENAME COLUMN .; (Ez csak a 9.2 verziótól kezdve működik) Különben: - új oszlopnevek felvétele és feltöltése, a régiek törlése, - nézettábla létrehozása (más néven), - SQL*Plus utasítás az oszlopnevekre (Column oszlopnév Heading Megadott név), (Ebben az esetben a SELECT-ekben mindig az eredeti oszlopnévre kell hivatkozni, de a megjelenítésnél, mindig a kért oszlopnév lesz. Minden, a tulajdonosnál

lévő tábla ilyen nevű oszlopára vonatkozik.) - Create table, kiírva minden oszlop neve. SQL> DROP TABLE dolgozo; SQL> CREATE TABLE dolgozo 2 AS 3 SELECT empno AS AZONOSÍTÓ, 4 ename AS NÉV, 5 job AS MUNKAKÖR, 6 mgr AS FŐNÖK ID, 7 hiredate AS BELÉPÉS, 8 sal AS FIZETÉS, 9 comm AS JUTALÉK, 10 deptno AS RÉSZLEG ID 11 FROM emp 12 WHERE sal+NVL(comm,0) >500 AND 13 hiredate > '1980. jan 1'; A tábla létrejött. SQL> SELECT * FROM dolgozo; AZONOSÍTÓ NÉV MUNKAKÖR FŐNÖK ID BELÉPÉS FIZETÉS JUTALÉK RÉSZLEG ID ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 800 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 (SQL példatár) - 48 - 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER SALESMAN MANAGER MANAGER ANALYST PRESIDENT

SALESMAN CLERK CLERK ANALYST CLERK 7698 81-SZE-28 7839 81-MÁJ-01 7839 81-JÚN-09 7566 87-ÁPR-19 81-NOV-17 7698 81-SZE-08 7788 87-MÁJ-23 7698 81-DEC-03 7566 81-DEC-03 7782 82-JAN-23 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 1400 0 30 30 10 20 10 30 20 30 20 10 14 sor kijelölve. B.4) SQL> ALTER TABLE dolgozo 2 ADD lakhely VARCHAR2(18); A tábla módosítva. SQL> SELECT * FROM dolgozo; AZONOSÍTÓ NÉV MUNKAKÖR FŐNÖK ID BELÉPÉS FIZETÉS JUTALÉK RÉSZLEG ID LAKHELY ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------7369 SMITH CLERK 7902 80-DEC-17 800 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7844 TURNER

SALESMAN 7698 81-SZE-08 1500 0 30 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7900 JAMES CLERK 7698 81-DEC-03 950 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7934 MILLER CLERK 7782 82-JAN-23 1300 10 14 sor kijelölve. B.5) Töltse fel a tábla LAKHELY oszlopát a következőképpen: a.) aki New York-ban dolgozik, annak lakhelye is New York, de King Long Beach-en lakik, SQL> UPDATE dolgozo 2 SET lakhely = 'NEW YORK' 3 WHERE részleg id IN (SELECT DISTINCT részleg id 4 FROM dolgozo, dept 5 WHERE loc = 'NEW YORK' AND 6 deptno = részleg id) 7 AND 8 név <> 'KING'; 2 sor módosítva. SQL> UPDATE dolgozo 2 SET lakhely = 'LONG BEACH' 3 WHERE név = 'KING'; 1 sor módosítva. b-1.) a Dallas-i clerk munkakörűek Garland-ban, SQL> UPDATE dolgozo 2 SET lakhely ='GARLAND' 3 WHERE munkakör = 'CLERK' AND (SQL példatár) - 49 - 4 5 6 7 részleg id = (SELECT DISTINCT részleg id FROM dolgozo, dept WHERE

loc = 'DALLAS' AND deptno = részleg id); 2 sor módosítva. b-2.) a többiek Dallas-ban laknak, SQL> UPDATE dolgozo 2 SET lakhely ='DALLAS' 3 WHERE munkakör <> 'CLERK' AND 4 részleg id = (SELECT DISTINCT részleg id 5 FROM dolgozo, dept 6 WHERE loc = 'DALLAS' AND 7 deptno = részleg id); 3 sor módosítva. c.) a Chicago-ban dolgozók Boston-ban, és a Boston-ban dolgozók Chicago-ban laknak, kivéve azon Chicago-i dolgozokat, akiknek Blake a főnökük, mert azok Indianapolis-ban laknak, feltéve, hogy nem clerk munkakörűek, mert akkor sehol sem laknak. SQL> UPDATE dolgozo 2 SET lakhely = 'INDIANAPOLIS' 3 WHERE részleg id = (SELECT DISTINCT d1.részleg id 4 FROM dolgozo d1, dept d, dolgozo d2 5 WHERE loc = 'CHICAGO' AND 6 d1.főnök id = d2azonosító AND 7 d1.részleg id = ddeptno AND 8 d2.név = ’BLAKE’) 9 AND 10 munkakör <> 'CLERK' AND 11 név <> 'BLAKE'; 4 sor

módosítva. SQL> UPDATE dolgozo 2 SET lakhely = 'BOSTON' 3 WHERE név = (SELECT DISTINCT d1.név 4 FROM dolgozo d1, dept d, dolgozo d2 5 WHERE loc = 'CHICAGO' AND 6 d1.főnök id = d2azonosító AND 7 d1.részleg id = ddeptno AND 8* d2.név <>'BLAKE') --Nem Blake a főnöke 1 sor módosítva. SQL> SELECT * FROM dolgozo; AZONOSÍTÓ NÉV MUNKAKÖR FŐNÖK ID BELÉPÉS FIZETÉS JUTALÉK RÉSZLEG ID LAKHELY ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------7369 SMITH CLERK 7902 80-DEC-17 800 20 GARLAND 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 INDIANAPOLIS 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 INDIANAPOLIS 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 DALLAS 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 INDIANAPOLIS 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 BOSTON 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 NEW YORK 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 DALLAS 7839 KING PRESIDENT

81-NOV-17 5000 10 LONG BEACH 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 INDIANAPOLIS 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 GARLAND 7900 JAMES CLERK 7698 81-DEC-03 950 30 (SQL példatár) - 50 - 7902 FORD 7934 MILLER ANALYST CLERK 7566 81-DEC-03 7782 82-JAN-23 3000 1300 20 DALLAS 10 NEW YORK 14 sor kijelölve. B.6) Először a dept táblába szúrjuk be az új részleg nevét, a telephelyét, és az azonosítóját: SQL> INSERT INTO dept 2 VALUES(99,'NAGYFŐNÖKSÉG','BUDAPEST'); 1 sor létrejött. SQL> SELECT * FROM dept; DEPTNO DNAME LOC ---------- -------------- ------------10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 99 NAGYFŐNÖKSÉG BUDAPEST Utána szúrjuk be Kovács-ot: SQL> r 1 INSERT INTO dolgozo 2* VALUES(9999,'KOVACS','NAGYFŐNÖK', NULL,TO DATE('2001.január 5.','YYYY month fmDD'),9999,9999, 1 sor létrejött. SQL> r 1 UPDATE dolgozo 2 SET főnök

id = 9999 3* WHERE név = 'KING' 1 sor módosítva. 7839 KING PRESIDENT 9999 81-NOV-17 5000 10 SQL> SELECT * FROM dolgozo; AZONOSÍTÓ NÉV MUNKAKÖR FŐNÖK ID BELÉPÉS FIZETÉS JUTALÉK RÉSZLEG ID LAKHELY ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------7369 SMITH CLERK 7902 80-DEC-17 800 20 GARLAND 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 INDIANAPOLIS 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 INDIANAPOLIS 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 DALLAS 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 INDIANAPOLIS 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 BOSTON 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 NEW YORK 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 DALLAS 7839 KING PRESIDENT 9999 81-NOV-17 5000 10 LONG BEACH 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 INDIANAPOLIS 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 GARLAND 7900 JAMES CLERK 7698 81-DEC-03 950 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20

DALLAS 7934 MILLER CLERK 7782 82-JAN-23 1300 10 NEW YORK 9999 KOVACS NAGYFŐNÖK 01-JAN-05 9999 9999 99 SOROKSÁR 15 sor kijelölve. SQL> SAVEPOINT ELSO; A mentési pont létrejött. (SQL példatár) - 51 - B.7) Az új belépő adatai: név: Kelemen azonosítója: 1111 munkaköre: SELECT MIN(fizetés) AS min, munkakör AS fogl FROM dolgozo, GROUP BY munkakör HAVING MIN(fizetés) BETWEEN 700 AND 1200; MIN ----800 FOGL ------CLERK Ahhoz, hogy ezen a helyen egy és csak egy munkakör értéket kapjunk, egy külső SELECT-ben kell ezt lekérdezni SELECT DISTINCT munkakör FROM dolgozo, (SELECT MIN(fizetés) AS min, munkakör AS fogl FROM dolgozo GROUP BY munkakör HAVING MIN(fizetés) BETWEEN 700 AND 1200) foglalkozás WHERE dolgozo.munkakör = foglalkozásfogl; MUNKAKÖR --------CLERK főnöke : SELECT főnök id FROM dolgozo WHERE név = UPPER('Ford'); FŐNÖK ID ---------7566 belépési dátuma: az aktuális dátum fizetése: A munkaköri fizetések

legnagyobbjával egyenlő, de kisebb mint 5000 USD SELECT MAX(Átlag) FROM (SELECT AVG(fizetés) AS átlag FROM dolgozo GROUP BY munkakör) WHERE átlag < 5000; MAX(ÁTLAG) ---------3000 (SQL példatár) - 52 - jutaléka: 111 USD részlege: Megegyezik a Budapesti részleg számával. A budapesti részleg száma: SELECT DISTINCT dolgozo.részleg id FROM dolgozo, dept WHERE dolgozo.részleg id = deptdeptno loc = UPPER('Budapest'); AND lakóhelye: Soroksár Tehát a beszúrandó sor a következő: INSERT INTO dolgozo VALUES (1111, --azonosító ’KELEMEN’, --név (SELECT DISTINCT munkakör --munkakör FROM dolgozo, (SELECT MIN(fizetés) AS min, munkakör AS fogl FROM dolgozo GROUP BY munkakör HAVING MIN(fizetés) BETWEEN 700 AND 1200) foglalkozás WHERE dolgozo.munkakör = foglalkozásfogl), (SELECT főnök id --főnök id FROM dolgozo WHERE név = UPPER('Ford')), SYSDATE, --belépés (SELECT MAX(Átlag) -- fizetés FROM (SELECT AVG(fizetés) AS átlag

FROM dolgozo GROUP BY munkakör) WHERE átlag <5000), 111, -- jutalék (SELECT DISTINCT dolgozo.részleg id --részleg FROM dolgozo, dept WHERE dolgozo.részleg id = deptdeptno AND loc = UPPER('Budapest')), ’Soroksár’); --lakhely 1 sor létrejött. Select * from dolgozo; . 1111 KELEMEN CLERK 7566 04-MÁR-27 SQL> SAVEPOINT Második; A mentési pont létrejött. B.8) Még két INSERT utasítás ROLLBACK TO Második; A visszaállítás befejeződött. Commit; A jóváhagyás befejeződött. (SQL példatár) - 53 - 3000 111 99 Soroksár 8.C (Feladatsor) C.1 Adja ki a SELECT * FROM emp WHERE ROWNUM = 3; majd a SELECT * FROM emp WHERE ROWNUM <= 3; utasítást. Indokolja meg a tapasztalatait C.2 Mi a kapcsolat az alábbi két utasítás között? SELECT * FROM emp WHERE ROWNUM <= 3; SELECT * FROM emp; és C.3 Hozzon létre az emp tábla 300 USD-nél nagyobb jövedelmű dolgozóiból egy dolgozó táblát. C.4 Listázza ki a dolgozó táblát ename

szerint rendezve C.5 Listázza ki az ename szerint rendezett dolgozó tábla első három sorát C.6 Az előző listához hozza létre a jó dolgozók nézettáblát C.7 Listázza a jó dolgozók nézettáblát C.8 Kérdezze le a megfelelő adatszótárbeli nézetekből (user objects, user catalog, vagy user views). C.9 Bővítse a dolgozó táblát az ABSOLON nevű dolgozóval (A többi érték tetszőleges) C.10 Listázza a jó dolgozók nézettáblát Indokolja az eredményt C.11 Bővítse a jó dolgozók nézettáblát (!) az ABASÁMUEL nevű dolgozóval, majd listázza a dolgozó táblát. Indokolja meg az eredményt C.12 Tiltsa meg a jó dolgozók nézettábla bővítését, majd kíséreljen meg egy bővítést C.13 Most hozzunk létre a dolgozó táblából egy DolgozóNézet nézetet, amely a dolgozó tábla empno, ename, job és deptno oszlopait tartalmazza. A nézet oszlopnevei legyenek: azonosító, név, foglalkozás, részleg. C.14 Bővítsük ezt a DolgozóNézet

nézetet az ABASÁMUEL dolgozóval, majd dobjuk el C.15 Hozzuk létre úgy a DolgozóNézet nézetet, hogy ne lehessen új sort beszúrni Megoldások C.1 SQL> SELECT * FROM emp 2 WHERE ROWNUM = 3; nincsenek kijelölve sorok C.2 SQL> SELECT * FROM emp 2 WHERE ROWNUM <= 3; (SQL példatár) - 54 - EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 800 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 C.3 SQL> DROP TABLE dolgozó; A tábla eldobva. SQL> CREATE TABLE dolgozó 2 AS 3 SELECT * FROM emp; A tábla létrejött. C.4 SQL> SELECT * FROM dolgozó 2 ORDER BY ename; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7698 BLAKE MANAGER 7839

81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7900 JAMES CLERK 7698 81-DEC-03 950 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7934 MILLER CLERK 7782 82-JAN-23 1300 10 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7369 SMITH CLERK 7902 80-DEC-17 800 20 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 14 sor kijelölve. C.5 Lássunk először egy hibás megoldást (miért is rossz ez?): SQL> SELECT * 2 FROM dolgozó 3 WHERE ROWNUM <= 3 4 ORDER BY ename; És most a jó megoldást: SQL> SELECT * 2 FROM (SELECT * FROM dolgozó 3 ORDER BY ename) 4 WHERE ROWNUM <= 3; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7698 BLAKE

MANAGER 7839 81-MÁJ-01 2850 30 (SQL példatár) - 55 - C.6 SQL> CREATE OR REPLACE VIEW jó dolgozók 2 AS 3 SELECT * 4 FROM (SELECT* FROM dolgozó 5 ORDER BY ename) 6* WHERE ROWNUM <= 3; A nézet létrejött. C.7 SQL> SELECT * 2* FROM jó dolgozók; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 C.8 Kérdezzük le az adatszótárbeli nézetekből: SQL> SELECT * FROM user objects; OBJECT NAME OBJECT TYPE CREATED LAST DDL TIMESTAMP STATUS T G S ----------------- ------------------ --------- --------- ------------------- ------- - - BONUS TABLE 02-MÁJ-12 02-MÁJ-12 2002-05-12:20:18:24 VALID N N N DEPT TABLE 02-MÁJ-12 02-MÁJ-12 2002-05-12:20:18:23 VALID N N N EMP TABLE 02-MÁJ-12 02-MÁJ-12 2002-05-12:20:18:24 VALID N N N LEGKISEBB VIEW 04-MÁR-28

04-MÁR-28 2004-03-28:23:58:54 VALID N N N PK DEPT INDEX 02-MÁJ-12 02-MÁJ-12 2002-05-12:20:18:23 VALID N N N PK EMP INDEX 02-MÁJ-12 02-MÁJ-12 2002-05-12:20:18:24 VALID N N N RÉSZLEG VIEW 04-ÁPR-01 04-ÁPR-02 2004-04-01:23:31:08 INVALID N N N SALGRADE TABLE 02-MÁJ-12 02-MÁJ-12 2002-05-12:20:18:24 VALID N N N 8 sor kijelölve. SQL> SELECT * FROM user catalog; TABLE NAME TABLE TYPE ------------------------------ ----------BONUS TABLE DEPT TABLE EMP TABLE LEGKISEBB VIEW RÉSZLEG VIEW SALGRADE TABLE 6 sor kijelölve. C.9 SQL> INSERT INTO dolgozó 2 VALUES(7777,'ABSOLON','DIÁK',8888,'03-OKT-25',555,NULL,10); 1 sor létrejött. (SQL példatár) - 56 - SQL> SELECT * FROM dolgozó; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 800 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30

7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7900 JAMES CLERK 7698 81-DEC-03 950 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7934 MILLER CLERK 7782 82-JAN-23 1300 10 7777 ABSOLON DIÁK 8888 03-OKT-25 555 10 15 sor kijelölve. C.10 A nézettábla minden esetben a hivatkozott táblát kérdezi le. Mivel ezt már bővítettük az ABSOLON nevű dolgozóval, a név szerinti rendezés ezt a legelső helyre tette. SQL> SELECT * FROM jó dolgozók; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7777 ABSOLON DIÁK 8888 03-OKT-25 555 10 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600

300 30 C.11 A jó dolgozók nézettábla nem bővíthető. SQL> INSERT INTO jó dolgozók 2 VALUES(2222,'ABASÁMUEL','SZERELŐ',8888,'03-NOV-2',1500,NULL,10); INSERT INTO jó dolgozók * Hiba a(z) 1. sorban: ORA-01732: adat kezelési műveletek nem megengedettek ezen a nézeten C.12 Mivel a jó dolgozók nézettábla egyáltalán nem bővíthető, az ezen keresztül való bővítést sem érdemes letiltani. SQL> DROP VIEW jó dolgozók; A nézet eldobva. C.13 SQL> CREATE VIEW DolgozóNézet 2 AS (SQL példatár) - 57 - 3 4 SELECT empno AS azon, ename AS név, job AS fogl, deptno AS részleg FROM dolgozó; A nézet létrejött. C.14 SQL> INSERT INTO DolgozóNézet 2 VALUES(2222,'ABASÁMUEL','SZERELŐ',10); 1 sor létrejött. SQL> SELECT * FROM DolgozóNézet; AZON NÉV FOGL RÉSZLEG ---------- ---------- --------- ---------7369 SMITH CLERK 20 7499 ALLEN SALESMAN 30 7521 WARD SALESMAN 30 7566 JONES MANAGER 20

7654 MARTIN SALESMAN 30 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7788 SCOTT ANALYST 20 7839 KING PRESIDENT 10 7844 TURNER SALESMAN 30 7876 ADAMS CLERK 20 7900 JAMES CLERK 30 7902 FORD ANALYST 20 7934 MILLER CLERK 10 7777 ABSOLON DIAK 10 2222 ABASÁMUEL SZERELŐ 10 16 sor kijelölve. SQL> DROP VIEW DolgozóNézet; A nézet eldobva. C.15 Hozzuk létre úgy a DolgozóNézet nézetet, hogy ne lehessen új sort beszúrni! SQL> CREATE VIEW DolgozóNézet 2 AS 3 SELECT ename AS név, job AS foglakozás, sal AS fizetés 4 FROM emp 5 WITH READ ONLY A nézet létrejött. SQL> INSERT INTO DolgozóNézet 2 VALUES('ABA', 'DIÁK',1500)INSERT INTO DolgozóNézet * Hiba a(z) 1. sorban: ORA-01733: virtuális (képzett) oszlop használata itt nem megengedett (SQL példatár) - 58 - 8.D (Összetett Feladatok) D.1 Hozza létre az emp táblával azonos dolgozó táblát Listázza ebben a legkisebb átlagfizetésű részleg dolgozóinak adatait. D.2 Hozza létre az emp

táblával azonos dolgozó táblát Növelje meg 10%-al ebben a legkisebb átlagfizetésű részleg dolgozóinak fizetését, és listázza e részleg nevét, az itt dolgozók nevét, valamint azok régi és új fizetését. D.3 Hozza létre az emp táblával azonos dolgozó táblát Növelje meg 20%-al ebben a három legrosszabbul fizetett dolgozó fizetését az eredeti fizetés oszlopban és listázza ki a dolgozó táblát. Megoldások D.1 Megoldás nézettáblákkal: 1.lépés (a dolgozó tábla létrehozása) SQL> DROP TABLE dolgozó; A tábla eldobva. SQL> CREATE TABLE dolgozó 2 AS 3 SELECT * FROM emp; A tábla létrejött. 2.lépés (a részleg-nézettábla létrehozása) [Részlegenkénti átlagfizetés] SQL> CREATE OR REPLACE VIEW részleg 2 AS 3 SELECT deptno, ROUND(AVG(sal)) AS átlagfiz 4 FROM dolgozó 5 GROUP BY deptno; A nézet létrejött. 3.lépés (a szegénydolgozók-nézettábla létrehozása) [A legkisebb átlagfizetésű részleg dolgozóinak adatai]

SQL> CREATE OR REPLACE VIEW szegénydolgozók 2 AS 3 SELECT dolgozó.ename, 4 dolgozó.sal, 5 minrészleg.minfiz, 6 dolgozó.deptno 7 FROM dolgozó, részleg, 8 (SELECT MIN(átlagfiz) minfiz 9 FROM részleg) minrészleg 10 WHERE 11 részleg.átlagfiz=minrészlegminfiz 12 dolgozó.deptno=részlegdeptno; (SQL példatár) - 59 - AND A nézet létrejött. 4.lépés (a szegénydolgozók tábla listázása) [A legkisebb átlagfizetésű részleg dolgozóinak listázása.] SQL> SELECT * 2 FROM szegénydolgozók 3 ORDER BY ename; ENAME SAL MINFIZ DEPTNO ---------- ---------- ---------- ---------ALLEN 1600 1567 30 BLAKE 2850 1567 30 JAMES 950 1567 30 MARTIN 1250 1567 30 TURNER 1500 1567 30 WARD 1250 1567 30 6 sor kijelölve. Megoldás WHERE-beli allekérdezéssel, ROWNUM-mal: A dolgozó tábla törlése: SQL> DROP TABLE dolgozó; A tábla eldobva. A dolgozó tábla létrehozása az emp táblából: SQL> CREATE TABLE dolgozó AS 2 SELECT * FROM emp; A tábla létrejött. A

legkisebb átlagfizetésű részleg dolgozóinak adatainak kilistázása: SQL> SELECT dolgozó.ename, 2 dolgozó.sal, 3 -részleg.átlag AS minfiz, (*) 4 dolgozó.deptno 5 FROM dolgozó 6 WHERE deptno = 7 (SELECT deptno 8 FROM 9 (SELECT deptno, AVG(sal) AS átlag 10 FROM dolgozó 11 GROUP BY deptno 12 ORDER BY AVG(sal)) részleg 13 WHERE ROWNUM=1); ENAME SAL DEPTNO ---------- ---------- ---------ALLEN 1600 30 WARD 1250 30 MARTIN 1250 30 BLAKE 2850 30 TURNER 1500 30 JAMES 950 30 (SQL példatár) - 60 - 6 sor kijelölve. Megjegyzés A (*)-al jelölt sort azért kellett kihúzni, mivel a WHERE-beli allekérdezésre nem lehet a külső SELECT-listában hivatkozni. D.2 Megoldás nézettáblákkal: 1.lépés (a dolgozó tábla létrehozása) SQL> DROP TABLE dolgozó; A tábla eldobva. SQL> CREATE TABLE dolgozó 2 AS 3 SELECT * FROM emp; A tábla létrejött. 2.lépés (a dolgozó tábla bővítése új oszloppal) SQL> ALTER TABLE dolgozó 2 ADD újfizetés NUMBER(7,2); A

tábla módosítva. 3.lépés (a dolgozó tábla sal oszlopának átnevezése) SQL> ALTER TABLE dolgozó 2 RENAME COLUMN sal TO régifizetés; A tábla módosítva. 4.lépés (a részleg-nézettábla létrehozása) [Részlegenkénti átlagfizetés] SQL> CREATE OR REPLACE VIEW részleg 2 AS 3 SELECT deptno, ROUND(AVG(régifizetés)) AS atlagfiz 4 FROM dolgozó 5 GROUP BY deptno; A nézet létrejött. 5.lépés (a szegénydolgozók-nézettábla létrehozása) [A legkisebb átlagfizetésű részleg dolgozói] SQL> CREATE OR REPLACE VIEW szegénydolgozók 2 AS 3 SELECT empno, régifizetés 4 FROM dolgozó, részleg, 5 (SELECT MIN(atlagfiz) minfiz 6 FROM részleg) minrészleg 7 WHERE 8 részleg.atlagfiz = minrészlegminfiz 9 dolgozó.deptno = részlegdeptno; (SQL példatár) - 61 - AND A nézet létrejött. 6.lépés (fizetések módosítása) [A legkisebb átlagfizetésű részlegen dolgozók fizetésének módosítása] [Az eredeti adattábla (dolgozó) módosítása]

SQL> UPDATE dolgozó 2 SET újfizetés = 3 NVL((SELECT régifizetés * 1.1 4 FROM szegénydolgozók 5 WHERE dolgozó.empno = 6 szegénydolgozók.empno), régifizetés); 14 sor módosítva. SQL> SELECT * FROM dolgozó; EMPNO ENAME JOB MGR HIREDATE RÉGIFIZETÉS COMM DEPTNO ÚJFIZETÉS ------ -------- --------- ------- --------- ----------- ------ -------- ---------7369 SMITH CLERK 7902 80-DEC-17 800 20 800 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 1760 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 1375 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 2975 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 1375 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 3135 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 2450 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 3000 7839 KING PRESIDENT 81-NOV-17 5000 10 5000 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 1650 7876 ADAMS CLERK 7788 87-MÁJ-23 1100 20 1100 7900 JAMES CLERK 7698 81-DEC-03 950 30 1045 7902 FORD ANALYST 7566 81-DEC-03 3000 20 3000 7934

MILLER CLERK 7782 82-JAN-23 1300 10 1300 14 sor kijelölve. 7.lépés (a dolgozó tábla listázása) [A legkisebb átlagfizetésű részleg nevének, az itt dolgozók nevének,] [valamint azok régi és új fizetésének listázása.] SQL> SELECT dolgozó.ename, 2 dolgozó.régifizetés, 3 dolgozó.újfizetés, 4 dept.dname 5 FROM dept, dolgozó, szegénydolgozók 6 WHERE dept.deptno = dolgozódeptno AND 7 dolgozó.empno = szegénydolgozókempno 8 ORDER BY ename; ENAME RÉGIFIZETÉS ÚJFIZETÉS DNAME ---------- ----------- ---------- -------------ALLEN 1600 1760 SALES BLAKE 2850 3135 SALES JAMES 950 1045 SALES MARTIN 1250 1375 SALES TURNER 1500 1650 SALES WARD 1250 1375 SALES 6 sor kijelölve. (SQL példatár) - 62 - D.3 Megoldás nézettáblával, UPDATE - SET-beli allekérdezéssel: 1.lépés (a dolgozó tábla eldobása és létrehozása) SQL> DROP TABLE dolgozó; A tábla eldobva. SQL> CREATE TABLE dolgozó 2 AS 3 SELECT * FROM emp; A tábla létrejött. 2.lépés

(a szegény nézet létrehozása) [A három legszegényebb.] SQL> CREATE OR REPLACE VIEW szegény 2 AS 3 SELECT empno, 4 ROWNUM sorszám 5 FROM 6 (SELECT empno 7 FROM dolgozó 8 ORDER BY sal) 9 WHERE ROWNUM <= 3; A nézet létrejött. SQL> SELECT * FROM szegény; EMPNO SORSZÁM ---------- ---------7369 1 7900 2 7876 3 3.lépés (módosítás) [Növeljük meg ezeknek és csak ezeknek a fizetését.] SELECT sal*1.2 FROM szegény WHERE dolgozó.empno = szegényempno; Ez az allekérdezés a módosított fizetésű dolgozók fizetését adja meg, a többi dolgozóé NULL értékű. A NULL értékű attribútumnak az NVL függvény segítségével adthatunk értéket NVL(oszlopnév, érték) módon. SQL> UPDATE dolgozó 2 SET sal = NVL((SELECT sal*1.2 3 FROM szegény 4 WHERE dolgozó.empno = szegényempno), sal); 14 sor módosítva. (SQL példatár) - 63 - 4.lépés (a dolgozó tábla listázása SQL> SELECT * FROM dolgozó; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 960 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 7876 ADAMS CLERK 7788 87-MÁJ-23 1320 20 7900 JAMES CLERK 7698 81-DEC-03 1140 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7934 MILLER CLERK 7782 82-JAN-23 1300 10 14 sor kijelölve. Megoldás nézettáblával, UPDATE - WHERE-beli allekérdezéssel: 1.lépés (a dolgozó tábla eldobása és létrehozása) SQL> DROP TABLE dolgozó; A tábla eldobva. SQL> CREATE TABLE dolgozó 2 AS 3 SELECT * FROM emp; A tábla létrejött. 2.lépés (a szegény nézet létrehozása) [A

három legszegényebb.] SQL> CREATE OR REPLACE VIEW szegény 2 AS 3 SELECT empno 4 FROM 5 (SELECT empno 6 FROM dolgozó 7 ORDER BY sal) 8 WHERE ROWNUM <= 3; A nézet létrejött. SQL> SELECT * FROM szegény; EMPNO ---------7369 7900 7876 3.lépés (módosítás) [Növeljük meg a szegényeknek és csak ezeknek a fizetését.] SQL> UPDATE dolgozó 2 SET sal = sal*1.2 3 WHERE empno IN 4 (SELECT empno FROM szegény); (SQL példatár) - 64 - 3 sor módosítva. 4.lépés (a dolgozó tábla listázása SQL> SELECT * FROM dolgozó; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 960 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7788

SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 7876 ADAMS CLERK 7788 87-MÁJ-23 1320 20 7900 JAMES CLERK 7698 81-DEC-03 1140 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7934 MILLER CLERK 7782 82-JAN-23 1300 10 14 sor kijelölve. A legegyszerűbb nézettáblás megoldás: 1.lépés (a dolgozó tábla eldobása és létrehozása) SQL> DROP TABLE dolgozó; A tábla eldobva. SQL> CREATE TABLE dolgozó 2 AS 3 SELECT * FROM emp; A tábla létrejött. 2.lépés (a rendezett dolgozó nézet létrehozása) SQL> CREATE OR REPLACE VIEW rendezett dolgozó 2 AS 3 SELECT * 4 FROM dolgozó 5 ORDER BY sal; A nézet létrejött. 3.lépés (módosítás nézettáblán keresztül) SQL> UPDATE rendezett dolgozó 2 SET sal = 1.2*sal 3 WHERE ROWNUM <= 3; 3 sor módosítva. 4.lépés (a dolgozó tábla listázása SQL> SELECT * FROM dolgozó; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----------

--------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 960 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 (SQL példatár) - 65 - 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK 7698 81-FEB-22 7839 81-ÁPR-02 7698 81-SZE-28 7839 81-MÁJ-01 7839 81-JÚN-09 7566 87-ÁPR-19 81-NOV-17 7698 81-SZE-08 7788 87-MÁJ-23 7698 81-DEC-03 7566 81-DEC-03 7782 82-JAN-23 1250 2975 1250 2850 2450 3000 5000 1500 1320 1140 3000 1300 500 1400 0 30 20 30 30 10 20 10 30 20 30 20 10 14 sor kijelölve. Megoldás nézettábla nélkül: 1.lépés (a dolgozó tábla eldobása és létrehozása) SQL> DROP TABLE dolgozó; A tábla eldobva. SQL> CREATE TABLE dolgozó 2 AS 3 SELECT empno AS azonosító, 4 ename AS név, 5 job AS munkakör, 6 mgr AS főnök id, 7 hiredate AS

belépés, 8 sal AS fizetés, 9 comm AS jutalék, 10 deptno AS részleg id 11 FROM emp; A tábla létrejött. 2.lépés (a dolgozó tábla közvetlen módosítása) SQL> UPDATE dolgozó 2 SET fizetés=fizetés*1.2 3 WHERE 4 azonosító IN 5 (SELECT ID 6 FROM 7 (SELECT ROWNUM AS sorszám, 8 al.név, 9 al.fizetés, 10 al.ID 11 FROM 12 (SELECT ename AS név, 13 sal AS fizetés, 14 empno AS ID 15 FROM emp 16 ORDER BY sal) al 17 WHERE ROWNUM <= 3)); 3 sor módosítva. SQL> SELECT * FROM dolgozó; AZONOSÍTO NÉV MUNKAKÖR FŐNÖK ID BELÉPÉS FIZETÉS JUTALÉK RÉSZLEG ID ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 960 20 7499 ALLEN SALESMAN 7698 81-FEB-20 1600 300 30 (SQL példatár) - 66 - 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK

ANALYST CLERK 7698 81-FEB-22 7839 81-ÁPR-02 7698 81-SZE-28 7839 81-MÁJ-01 7839 81-JÚN-09 7566 87-ÁPR-19 81-NOV-17 7698 81-SZE-08 7788 87-MÁJ-23 7698 81-DEC-03 7566 81-DEC-03 7782 82-JAN-23 1250 2975 1250 2850 2450 3000 5000 1500 1320 1140 3000 1300 500 1400 0 30 20 30 30 10 20 10 30 20 30 20 10 14 sor kijelölve. Legegyszerűbb megoldás (nézettábla nélkül): 1.lépés (a dolgozó tábla eldobása és létrehozása) SQL> DROP TABLE dolgozó; A tábla eldobva. SQL> CREATE TABLE dolgozó 2 AS 3 SELECT * FROM emp; A tábla létrejött. 2.lépés (a dolgozó tábla közvetlen módosítása) SQL> UPDATE dolgozó 2 SET sal = 1.2*sal 3 WHERE empno IN 4 (SELECT empno 5 FROM 6 (SELECT empno 7 FROM emp 8 ORDER BY sal) 9 WHERE ROWNUM <= 3); 3 sor módosítva. SQL> SELECT * FROM dolgozó; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 80-DEC-17 960 20 7499 ALLEN

SALESMAN 7698 81-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 81-FEB-22 1250 500 30 7566 JONES MANAGER 7839 81-ÁPR-02 2975 20 7654 MARTIN SALESMAN 7698 81-SZE-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-MÁJ-01 2850 30 7782 CLARK MANAGER 7839 81-JÚN-09 2450 10 7788 SCOTT ANALYST 7566 87-ÁPR-19 3000 20 7839 KING PRESIDENT 81-NOV-17 5000 10 7844 TURNER SALESMAN 7698 81-SZE-08 1500 0 30 7876 ADAMS CLERK 7788 87-MÁJ-23 1320 20 7900 JAMES CLERK 7698 81-DEC-03 1140 30 7902 FORD ANALYST 7566 81-DEC-03 3000 20 7934 MILLER CLERK 7782 82-JAN-23 1300 10 14 sor kijelölve. (SQL példatár) - 67 - 9. SQL-DCL ZÁRTHELYI DOLGOZAT A.1 a.) Hozzon létre egy szerepkört b.) Rendeljen a szerepkörhöz felhasználót c.) Adjon tábla és nézet-létrehozási jogokat a szerepkörnek A.2 Hozzon létre a system felhasználón belül egy saját név felhasználót. Változtassa meg a saját név felhasználó a jelszavát. A.3 A system felhasználó adjon a raktár táblához lekérdezési és

módosítási jogokat minden felhasználónak. B.1 Hozzon létre egy felhasználót a system felhasználón belül. A felhasználó neve a vezetéknevének értelemszerű rövidítése legyen, jelszava pedig „dolgozat” legyen. Változtassa meg a jelszót a saját utónevére. B.2 A system felhasználó adjon önnek olyan jogokat, hogy hozzáférhessen az SQL-hez, az adatbázishoz, tudjon táblát, nézetet, eljárást létrehozni, kezelni. B.3 A system felhasználó adjon jogosultságokat az ön nevével jelzett felhasználónak, olyan jogokat, hogy tudjon táblát módosítani, képes legyen adatokat lekérdezni, módosítani és törölni a „forgalmazó” nevű táblából és legyen továbbadási joga is. Megoldások: A.1 a.) CREATE ROLE oktato IDENTIFIED BY oktato; A szerepkör létrejött. b.) SQL> GRANT oktato 2 TO diak1; TO diak1 * Hiba a(z) 2. sorban: ORA-01917: a(z) 'DIAK1' felhasználó vagy szerepkör nem létezik SQL> CREATE user diak1 2

IDENTIFIED BY diak1; A felhasználó létrejött. SQL> GRANT oktato 2 TO diak1; Az engedélyezés sikeresen befejeződött. c.) SQL> GRANT CREATE SESSION,CREATE TABLE, CREATE VIEW 2 TO oktato; Az engedélyezés sikeresen befejeződött. (SQL példatár) - 68 - A.2 Saját felhasználó és belépési jelszó módosítás. SQL> CREATE USER KM 2 IDENTIFIED BY KM; A felhasználó létrejött. SQL> ALTER USER KM 2 IDENTIFIED BY hallgato; A felhasználó módosítva. A.3 Raktártáblához mindenkinek jog (lekérdezési és módosítási) adása. SQL> CREATE table raktár 2 (cikkszam Number, 3 név Varchar2(20), 4 Mennyiség Number(5)); A tábla létrejött. SQL> GRANT SELECT ON raktár TO PUBLIC; Az engedélyezés sikeresen befejeződött. SQL> r 1* GRANT ALTER, UPDATE ON raktár TO PUBLIC Az engedélyezés sikeresen befejeződött. B.1 Felhasználó definiálás a Scott-ban. SQL> CREATE USER Mari 2 IDENTIFIED BY mari; identified by mari * Hiba a(z) 2.

sorban: ORA-01031: nincs megfelelő jogosultsága Átkapcsolva a System-be SQL> CONNECT system/manager; Kapcsolódva. SQL> CREATE USER Mari 2 IDENTIFIED BY mari; A felhasználó létrejött. Próbálkozás: lehet-e felülről a “Scott”-ba megadni új felhasználót? NEM! (SQL példatár) - 69 - SQL> CREATE USER scott.mari 2 IDENTIFIED BY mari; create user scott.mari * Hiba a(z) 1. sorban: ORA-01936: felhasználó vagy szerepkör létrehozásakor nem adható meg tulajdonos Adatszótárbeli nézettel a System összes felhasználója! SQL> SELECT * FROM all users; USERNAME USER ID CREATED ------------------------------ ---------- --------SYS 0 01-SZE-04 SYSTEM 5 01-SZE-04 OUTLN 11 01-SZE-04 DBSNMP 17 01-SZE-04 ORDSYS 29 01-SZE-04 AURORA$JIS$UTILITY$ 26 01-SZE-04 OSE$HTTP$ADMIN 27 01-SZE-04 AURORA$ORB$UNAUTHENTICATED 28 01-SZE-04 OLAPSVR 39 01-SZE-04 OLAPSYS 37 01-SZE-04 ORDPLUGINS 30 01-SZE-04 MDSYS 31 01-SZE-04 CTXSYS 32 01-SZE-04 WKSYS 34 01-SZE-04 OLAPDBA 40

01-SZE-04 QS CBADM 62 01-SZE-04 QS ADM 57 01-SZE-04 QS 58 01-SZE-04 QS WS 59 01-SZE-04 HR 53 01-SZE-04 OE 54 01-SZE-04 PM 55 01-SZE-04 SH 56 01-SZE-04 QS ES 60 01-SZE-04 QS OS 61 01-SZE-04 RMAN 65 01-SZE-04 QS CB 63 01-SZE-04 QS CS 64 01-SZE-04 SCOTT 66 01-SZE-04 MARI 67 03-NOV-03 30 sor kijelölve. A felhasználó eldobása SQL> DROP USER mari; A felhasználó eldobva. (SQL példatár) - 70 - UTOLSÓ OLDAL (SQL példatár) - 71 -