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 ---------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 Típus --------------NUMBER NUMBER 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 ---------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 〉. 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; 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. 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 ---------- ---------7876 ADAMS 7902 FORD 7566 JONES 7788 SCOTT 7369 SMITH 7499 ALLEN 7698 BLAKE 7900 JAMES 7654 MARTIN 7844 TURNER 7521 WARD 11 sor kijelölve. DNAME -------------RESEARCH RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES SALES SALES SALES SALES 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 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 7788 7782 7839 7566 6 sor kijelölve. 950 1100 1300 2450 3000 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 ------------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 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 2 3 4 5 FROM 6 e.job as FOGLALKOZÁS, e.empno as AZONOSÍTÓ, e.ename as NÉV, al.létszám emp e, (SELECT job, count(ename) as létszám 10. oldal, összesen: 20 7 8 9 10* WHERE e.job Adja meg a(z) szam régi 9: új 9: FROM emp GROUP BY job HAVING Count(ename) > &szam) al = al.job; értékét: 2 HAVING Count(ename) > &szam) al 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 ---------- --------JAMES CLERK ALLEN SALESMAN TURNER SALESMAN MARTIN SALESMAN WARD SALESMAN SMITH CLERK ADAMS CLERK MILLER CLERK 8 sor kijelölve. LOC különbözet ------------- ---------CHICAGO -983 CHICAGO -33 CHICAGO -433 CHICAGO 717 CHICAGO -183 DALLAS -1375 DALLAS -1075 NEW YORK -1617 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 ------------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. 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 ---------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 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 JOB --------ANALYST ANALYST CLERK LOC SAL LOC JOB SAL LOC SAL ------------- ---------- ----------- ---------DALLAS 3000 3000 2175 DALLAS 3000 3000 2175 CHICAGO 950 950 1567 14. oldal, összesen: 20 SMITH CLERK ADAMS CLERK MILLER CLERK BLAKE MANAGER JONES MANAGER CLARK MANAGER KING PRESIDENT ALLEN SALESMAN MARTIN SALESMAN TURNER SALESMAN WARD SALESMAN 14 sor kijelölve. DALLAS DALLAS NEW YORK CHICAGO DALLAS NEW YORK NEW YORK CHICAGO CHICAGO

CHICAGO CHICAGO 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 ---------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 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 ---------- ---------ADAMS 1100 ALLEN 1600 BLAKE 2850 JONES 2975 MILLER 1300 TURNER 1500 6 sor kijelölve. MUNKAKÖR ÁTLAGFIZETÉS --------- -----------CLERK 1038 SALESMAN 1400 MANAGER 2758 MANAGER 2758 CLERK 1038 SALESMAN 1400 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) -----------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 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 ---------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); 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 20. oldal, összesen: 20