Programozás | SQL » SQL gyakorlófeladatok, megoldással

SQL GYAKORLÓFELADATOK 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 1. oldal, összesen: 20 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 USDné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. 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 2A feladatot) 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 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 〉 4.C 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 2. oldal, összesen: 20 4.D 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.E 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.F 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 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. Megoldások: 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 USDné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 3. oldal, összesen: 20 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 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 〉.

4. oldal, összesen: 20 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 7698 ALLEN 1600 WARD 1250 JAMES 950 TURNER 1500 MARTIN 1250 7782 MILLER 1300 7788 ADAMS 1100 7839 JONES 2975 CLARK 2450 BLAKE 2850 7902 SMITH 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. 5. oldal, összesen: 20 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%'); 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 6. oldal, összesen: 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 2A feladatot) Megoldás A szükséges allekérdezés (lásd a 2.A-ban): 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 7. oldal, összesen: 20 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, 6 MIN(sal) AS MinFiz 7 FROM emp 8 WHERE mgr IS NOT NULL 9 GROUP BY mgr 10 HAVING MIN(sal) <= 3000) kisfizetés, 11 emp 12 WHERE kisfizetés.Főnök = empmgr AND 13 kisfizetés.MinFiz = empsal 14 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. 8. oldal, összesen: 20 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) 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. 9. oldal, összesen: 20 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 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 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 10. oldal, összesen: 20 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. 4.C 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 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. 11. oldal, összesen: 20 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> SELECT AVG(sal+NVL(comm,0)) 2 FROM emp összesátlag ÖSSZESÁTLAG ----------2230.35714 4.D 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; 12. oldal, összesen: 20 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. 4.E 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 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-----------------NEW YORK 20 2175 Dallas 30 1933.33333 Chicago SQL> SELECT loc, ename,

sal+NVL(comm,0) 2 FROM emp, dept, 3 (SELECT deptno, AVG(sal+NVL(comm,0)) 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. 4.F 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 13. oldal, összesen: 20 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 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 14. oldal, összesen: 20 SMITH CLERK DALLAS ADAMS CLERK DALLAS MILLER CLERK NEW YORK BLAKE MANAGER CHICAGO JONES MANAGER DALLAS CLARK MANAGER NEW YORK KING PRESIDENT NEW YORK ALLEN SALESMAN CHICAGO MARTIN SALESMAN CHICAGO TURNER SALESMAN

CHICAGO WARD SALESMAN CHICAGO 14 sor kijelölve. 800 1100 1300 2850 2975 2450 5000 1600 1250 1500 1250 950 950 1300 2850 2975 2450 5000 1400 1400 1400 1400 2175 2175 2917 1567 2175 2917 2917 1567 1567 1567 1567 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. 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 15. oldal, összesen: 20 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 (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; 16. oldal, összesen: 20 2. Megoldás redukált táblával: SELECT ename AS név, sal AS fizetés, dname AS telephely, 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: 1. részlegátlag: SELECT deptno, ROUND(AVG(sal)) FROM emp GROUP BY deptno; 17. oldal, összesen: 20 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 16 GROUP BY deptno))) részlegátlag 17 WHERE részleg = emp.deptno AND 18 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 18. oldal, összesen: 20 TURNER WARD 1500 SALES 1250 SALES 1567 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 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 19. oldal, összesen: 20 9 10 11 12 13* (SELECT COUNT(ename) AS Kisebb FROM emp WHERE job <> UPPER('president') GROUP BY job))) legkisebb foglalkozás 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); 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 20. oldal, összesen: 20