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 ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER JOB MGR HIREDATE SAL COMM DEPTNO

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

-------DEPTNO NOT NULL DNAME LOC Típus --------------NUMBER(2) VARCHAR2(14) 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? ----------------------------------------- -------GRADE LOSAL HISAL (SQL példatár) -4- Típus --------------NUMBER NUMBER NUMBER 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 ---------SMITH JONES SCOTT ADAMS FORD JOB SAL DEPTNO --------- ---------- ---------CLERK 800 20 MANAGER 2975 20 ANALYST 3000 20 CLERK 1100 20 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 ---------SMITH ADAMS JONES SCOTT FORD Részleg neve FIZETÉS -------------- ---------RESEARCH 800 RESEARCH 1100 RESEARCH 2975 RESEARCH 3000 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; FŐNÖK ---------7839 7698 7782 7788 --csak ellenőrzésképp NÉV FIZETÉS MAX Ez a maximum ---------- ---------- -----------JONES 2975 2975 ALLEN 1600 1600 MILLER 1300 1300 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Ó ---------7369 7876 7902 7788 7566 7499 7698 7654 7900 7844 7521 NÉV ---------SMITH ADAMS FORD SCOTT JONES ALLEN BLAKE MARTIN JAMES TURNER 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Ó ---------7876 7902 7566 7788 7369 7499 7698 7900 7654 7844 7521 NÉV ---------ADAMS FORD JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD DNAME -------------RESEARCH RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES SALES SALES SALES 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 ----------7788 7782 7839 7566 7566 DOLGOZÓ NE FIZETÉS ---------- ---------ADAMS 1100 MILLER 1300 CLARK 2450 SCOTT 3000 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 ------------NEW YORK NEW YORK DALLAS DALLAS DALLAS DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO VEZETŐ NEV VEZETŐ FIZETÉSE DOLGOZÓK ÁTLAGJÖVEDELME ---------- --------------- ----------------------KING 5000 2917 CLARK 2450 2917 FORD 3000 2175 SCOTT 3000 2175 JONES 2975 2175 JONES 2975 2175 BLAKE 2850 1933 BLAKE 2850 1933 BLAKE 2850 1933 BLAKE 2850 1933 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 ------------CHICAGO DALLAS DALLAS DALLAS NEW YORK NEW YORK VEZETŐ NEV VEZETŐ FIZETÉSE DOLGOZÓK ÁTLAGJÖVEDELME ---------- --------------- ----------------------BLAKE 2850 1933 JONES 2975 2175 SCOTT 3000 2175 FORD 3000 2175 CLARK 2450 2917 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 ---------JAMES ALLEN TURNER MARTIN WARD SMITH ADAMS MILLER JOB --------CLERK SALESMAN SALESMAN SALESMAN SALESMAN CLERK CLERK CLERK LOC különbözet ------------- ---------CHICAGO -983 CHICAGO -33 CHICAGO -433 CHICAGO 717 CHICAGO -183 DALLAS -1375 DALLAS -1075 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 ---------ALLEN CLARK JAMES SMITH TURNER WARD FOGLALKOZ JÖVEDELEM TELEPHELY munkaköri átlagjövedelem --------- ---------- ------------- -----------------------SALESMAN 1900 CHICAGO 1950 MANAGER 2450 NEW YORK 2758 CLERK 950 CHICAGO 1038 CLERK 800 DALLAS 1038 SALESMAN 1500 CHICAGO 1950 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 ---------MILLER SMITH ADAMS JAMES ALLEN MARTIN TURNER WARD MUNKAKÖR --------CLERK CLERK CLERK CLERK SALESMAN SALESMAN SALESMAN SALESMAN részleg neve különbözet -------------- ---------ACCOUNTING 1617 RESEARCH 1375 RESEARCH 1075 SALES 983 SALES 33 SALES -717 SALES 433 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 ---------FORD SCOTT JAMES SMITH ADAMS MILLER BLAKE JONES CLARK KING ALLEN MARTIN TURNER WARD JOB --------ANALYST ANALYST CLERK CLERK CLERK CLERK MANAGER MANAGER MANAGER PRESIDENT SALESMAN SALESMAN SALESMAN SALESMAN LOC SAL LOC JOB SAL LOC SAL ------------- ---------- ----------- ---------DALLAS 3000 3000 2175 DALLAS 3000 3000 2175 CHICAGO 950 950 1567 DALLAS 800 950

2175 DALLAS 1100 950 2175 NEW YORK 1300 1300 2917 CHICAGO 2850 2850 1567 DALLAS 2975 2975 2175 NEW YORK 2450 2450 2917 NEW YORK 5000 5000 2917 CHICAGO 1600 1400 1567 CHICAGO 1250 1400 1567 CHICAGO 1500 1400 1567 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 ---------FORD SCOTT JAMES SMITH ADAMS MILLER BLAKE JONES CLARK KING ALLEN MARTIN TURNER WARD JOB --------ANALYST ANALYST CLERK CLERK CLERK CLERK MANAGER MANAGER MANAGER PRESIDENT SALESMAN SALESMAN SALESMAN SALESMAN LOC SAL RELATÍV FIZ1 RELATÍV FIZ2 ------------- ---------- ------------ -----------DALLAS 3000 100 138 DALLAS 3000 100 138 CHICAGO 950 100 61 DALLAS 800 84 37 DALLAS 1100 116 51 NEW YORK 1300 100 45 CHICAGO 2850 100 182 DALLAS 2975 100 137 NEW YORK 2450 100 84 NEW YORK 5000 100 171 CHICAGO 1600 114 102 CHICAGO 1250 89 80 CHICAGO 1500 107 96 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) -----------2 4 3 1 4 JOB --------ANALYST CLERK MANAGER PRESIDENT 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 ---------SCOTT FORD JOB --------ANALYST 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 ---------SCOTT FORD JOB --------ANALYST 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 ---------10 20 30 40 DNAME -------------ACCOUNTING RESEARCH SALES OPERATIONS LOC ------------NEW YORK DALLAS CHICAGO

BOSTON Adja meg a(z) tabla2 értékét: emp EMPNO ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 800 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 SALESMAN 7698 81-SZE-28 1250 1400 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST 7566 87-ÁPR-19 3000 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-08 1500 0 30 CLERK 7788 87-MÁJ-23 1100 20 CLERK 7698 81-DEC-03 950 30 ANALYST 7566 81-DEC-03 3000 20 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> 1 2 3 4 5 6 7 8 9 10* Adja régi új r SELECT e.job as FOGLALKOZÁS, e.empno as AZONOSÍTÓ, e.ename as NÉV, al.létszám

FROM emp e, (SELECT job, count(ename) as létszám FROM emp GROUP BY job HAVING Count(ename) > &szam) al WHERE e.job = aljob; meg a(z) szam értékét: 2 9: HAVING Count(ename) > &szam) al 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 ------------NEW YORK NEW YORK NEW YORK DALLAS DALLAS DALLAS DALLAS DALLAS ENAME SAL+NVL(COMM,0) ---------- --------------CLARK 2450 KING 5000 MILLER 1300 SMITH 800 ADAMS 1100 FORD 3000 SCOTT 3000 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 ------------CHICAGO CHICAGO CHICAGO DALLAS NEW YORK NEW YORK DOLGOZÓ NÉ ---------ALLEN TURNER WARD ADAMS CLARK MILLER MUNKAKÖR JÖVEDELEM --------- ---------SALESMAN 1900 SALESMAN 1500 SALESMAN 1750 CLERK 1100 MANAGER 2450 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 ---------SMITH JONES SCOTT ADAMS FORD JOB SAL DEPTNO --------- ---------- ---------CLERK 800 20 MANAGER 2975 20 ANALYST 3000 20 CLERK 1100 20 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 ------------CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO DOLGOZÓ NÉ JÖVEDELEM FÖNÖK KÓD

---------- ---------- ---------ALLEN 1900 7698 JAMES 950 7698 MARTIN 2650 7698 TURNER 1500 7698 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 --------ANALYST ANALYST CLERK CLERK CLERK CLERK MANAGER MANAGER MANAGER SALESMAN SALESMAN SALESMAN SALESMAN Dolgozó Név Különbség Főnök Név ------------ ---------- ---------SCOTT 0 JONES FORD 0 JONES SMITH 500 FORD JAMES 350 BLAKE ADAMS 200 SCOTT MILLER 0 CLARK CLARK 525 KING BLAKE 125 KING JONES 0 KING MARTIN 1400 BLAKE WARD 1400 BLAKE TURNER 1150 BLAKE ALLEN 1050 BLAKE 13 sor kijelölve. CLEAR COLUMNS Ellenőrzés: SQL> select

max(sal), job 2 from emp 3 group by job; MAX(SAL) ---------3000 1300 2975 5000 1600 JOB --------ANALYST CLERK MANAGER PRESIDENT SALESMAN (SQL példatár) - 45 - Telephely ---------DALLAS DALLAS DALLAS CHICAGO DALLAS NEW YORK NEW YORK CHICAGO DALLAS CHICAGO CHICAGO CHICAGO CHICAGO 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? ----------------------------- -------EMPNO NOT NULL ENAME JOB MGR HIREDATE SAL COMM DEPTNO Típus -------------------NUMBER(4) VARCHAR2(10) VARCHAR2(9) NUMBER(4) DATE NUMBER(7,2) NUMBER(7,2)

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? ----------------------------- -------EMPNO NOT NULL AZONOSÍTÓ JOB MGR HIREDATE SAL COMM DEPTNO (SQL példatár) - 46 - Típus -------------------NUMBER(4) VARCHAR2(10) VARCHAR2(9) NUMBER(4) DATE NUMBER(7,2) NUMBER(7,2) NUMBER(2) 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 ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 800 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 SALESMAN 7698 81-SZE-28 1250 1400 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST

7566 87-ÁPR-19 3000 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-08 1500 0 30 CLERK 7788 87-MÁJ-23 1100 20 CLERK 7698 81-DEC-03 950 30 ANALYST 7566 81-DEC-03 3000 20 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Ó ---------7369 7499 7521 7566 NÉV ---------SMITH ALLEN WARD JONES MUNKAKÖR FŐNÖK ID BELÉPÉS FIZETÉS JUTALÉK RÉSZLEG ID --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 800 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 (SQL példatár) - 48 - 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK 7698 7839 7839 7566 7698 7788 7698 7566 7782 81-SZE-28 81-MÁJ-01 81-JÚN-09 87-ÁPR-19 81-NOV-17 81-SZE-08 87-MÁJ-23 81-DEC-03 81-DEC-03 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Ó ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 NÉV ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER MUNKAKÖR FŐNÖK ID BELÉPÉS FIZETÉS JUTALÉK RÉSZLEG ID LAKHELY --------- ---------- --------- ---------- ---------- ---------- -------------CLERK 7902 80-DEC-17 800 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 SALESMAN 7698 81-SZE-28 1250 1400 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST 7566 87-ÁPR-19 3000 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-08 1500 0 30 CLERK 7788 87-MÁJ-23 1100 20 CLERK 7698 81-DEC-03 950 30 ANALYST 7566 81-DEC-03 3000 20 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 ---------10 20 30 40 99 DNAME -------------ACCOUNTING RESEARCH SALES OPERATIONS NAGYFŐNÖKSÉG LOC ------------NEW YORK DALLAS CHICAGO BOSTON 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 ---------7369 7499 7521 ENAME ---------SMITH ALLEN WARD JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 800 20 SALESMAN 7698 81-FEB-20 1600 300 30 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 ---------7876 7499 7698 7782 7902 7900 7566 7839 7654 7934 7788 7369 7844 7521 ENAME ---------ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7788 87-MÁJ-23 1100 20 SALESMAN 7698 81-FEB-20 1600 300 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST 7566 81-DEC-03 3000 20 CLERK 7698 81-DEC-03 950 30 MANAGER 7839 81-ÁPR-02 2975 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-28 1250 1400 30 CLERK 7782 82-JAN-23 1300 10 ANALYST 7566 87-ÁPR-19 3000 20 CLERK 7902 80-DEC-17 800 20 SALESMAN 7698 81-SZE-08 1500 0 30 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 ---------7876 7499 7698 ENAME ---------ADAMS ALLEN BLAKE JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7788 87-MÁJ-23 1100 20 SALESMAN 7698 81-FEB-20 1600 300 30 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 ---------7876 7499 7698 ENAME ---------ADAMS ALLEN BLAKE JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7788 87-MÁJ-23 1100 20 SALESMAN 7698 81-FEB-20 1600 300 30 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 ----------------BONUS DEPT EMP LEGKISEBB PK

DEPT PK EMP RÉSZLEG SALGRADE OBJECT TYPE -----------------TABLE TABLE TABLE VIEW INDEX INDEX VIEW TABLE CREATED --------02-MÁJ-12 02-MÁJ-12 02-MÁJ-12 04-MÁR-28 02-MÁJ-12 02-MÁJ-12 04-ÁPR-01 02-MÁJ-12 LAST DDL --------02-MÁJ-12 02-MÁJ-12 02-MÁJ-12 04-MÁR-28 02-MÁJ-12 02-MÁJ-12 04-ÁPR-02 02-MÁJ-12 TIMESTAMP ------------------2002-05-12:20:18:24 2002-05-12:20:18:23 2002-05-12:20:18:24 2004-03-28:23:58:54 2002-05-12:20:18:23 2002-05-12:20:18:24 2004-04-01:23:31:08 2002-05-12:20:18:24 STATUS ------VALID VALID VALID VALID VALID VALID INVALID VALID 8 sor kijelölve. SQL> SELECT * FROM user catalog; TABLE NAME -----------------------------BONUS DEPT EMP LEGKISEBB RÉSZLEG SALGRADE TABLE TYPE ----------TABLE TABLE TABLE VIEW VIEW 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 - T N N N N N N N N G N N N N N N N N S N N N N N N N N SQL> SELECT *

FROM dolgozó; EMPNO ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7777 ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER ABSOLON JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 800 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 SALESMAN 7698 81-SZE-28 1250 1400 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST 7566 87-ÁPR-19 3000 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-08 1500 0 30 CLERK 7788 87-MÁJ-23 1100 20 CLERK 7698 81-DEC-03 950 30 ANALYST 7566 81-DEC-03 3000 20 CLERK 7782 82-JAN-23 1300 10 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 ---------7777 7876 7499 ENAME ---------ABSOLON ADAMS ALLEN JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------DIÁK 8888 03-OKT-25 555 10 CLERK 7788 87-MÁJ-23 1100 20 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 ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7777 2222 NÉV ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER ABSOLON ABASÁMUEL FOGL RÉSZLEG --------- ---------CLERK 20 SALESMAN 30 SALESMAN 30 MANAGER 20 SALESMAN 30 MANAGER 30 MANAGER 10 ANALYST 20 PRESIDENT 10 SALESMAN 30 CLERK 20 CLERK 30 ANALYST 20 CLERK 10 DIAK 10 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 -----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 ENAME -------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER JOB MGR HIREDATE RÉGIFIZETÉS COMM DEPTNO ÚJFIZETÉS --------- ------- --------- ----------- ------ -------- ---------CLERK 7902 80-DEC-17 800 20 800 SALESMAN 7698 81-FEB-20 1600 300 30 1760 SALESMAN 7698 81-FEB-22 1250 500 30 1375 MANAGER 7839 81-ÁPR-02 2975 20 2975 SALESMAN 7698 81-SZE-28 1250 1400 30 1375 MANAGER 7839 81-MÁJ-01 2850 30 3135 MANAGER

7839 81-JÚN-09 2450 10 2450 ANALYST 7566 87-ÁPR-19 3000 20 3000 PRESIDENT 81-NOV-17 5000 10 5000 SALESMAN 7698 81-SZE-08 1500 0 30 1650 CLERK 7788 87-MÁJ-23 1100 20 1100 CLERK 7698 81-DEC-03 950 30 1045 ANALYST 7566 81-DEC-03 3000 20 3000 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 ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 960 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 SALESMAN 7698 81-SZE-28 1250 1400 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST 7566 87-ÁPR-19 3000 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-08 1500 0 30 CLERK 7788 87-MÁJ-23 1320 20 CLERK 7698 81-DEC-03 1140 30 ANALYST 7566 81-DEC-03 3000 20 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 ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 960 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 SALESMAN 7698 81-SZE-28 1250 1400 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST 7566 87-ÁPR-19 3000 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-08 1500 0 30 CLERK 7788 87-MÁJ-23 1320 20 CLERK 7698 81-DEC-03 1140 30 ANALYST 7566 81-DEC-03 3000 20 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 ---------7369 7499 ENAME ---------SMITH ALLEN JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 960 20 SALESMAN 7698 81-FEB-20 1600 300 30 (SQL példatár) - 65 - 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782 81-FEB-22 81-ÁPR-02 81-SZE-28 81-MÁJ-01 81-JÚN-09 87-ÁPR-19 81-NOV-17 81-SZE-08 87-MÁJ-23 81-DEC-03 81-DEC-03 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 4 ename 5 job 6 mgr 7 hiredate 8 sal 9 comm 10 deptno 11 FROM emp; AS AS AS AS AS AS AS AS azonosító, név, munkakör, főnök id, belépés, fizetés, jutalék, részleg id 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 ---------7369 7499 NÉV ---------SMITH ALLEN MUNKAKÖR FŐNÖK ID BELÉPÉS FIZETÉS JUTALÉK RÉSZLEG ID --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 960 20 SALESMAN 7698 81-FEB-20 1600 300 30

(SQL példatár) - 66 - 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782 81-FEB-22 81-ÁPR-02 81-SZE-28 81-MÁJ-01 81-JÚN-09 87-ÁPR-19 81-NOV-17 81-SZE-08 87-MÁJ-23 81-DEC-03 81-DEC-03 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 ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 ENAME ---------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- ---------- ---------- ---------CLERK 7902 80-DEC-17 960 20 SALESMAN 7698 81-FEB-20 1600 300 30 SALESMAN 7698 81-FEB-22 1250 500 30 MANAGER 7839 81-ÁPR-02 2975 20 SALESMAN 7698 81-SZE-28 1250 1400 30 MANAGER 7839 81-MÁJ-01 2850 30 MANAGER 7839 81-JÚN-09 2450 10 ANALYST 7566 87-ÁPR-19 3000 20 PRESIDENT 81-NOV-17 5000 10 SALESMAN 7698 81-SZE-08 1500 0 30 CLERK 7788 87-MÁJ-23 1320 20 CLERK 7698 81-DEC-03 1140 30 ANALYST 7566 81-DEC-03 3000 20 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> 2 3 4 CREATE table raktár (cikkszam Number, név Varchar2(20), 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 -