1 / 44

ADATBÁZISOK users.nik.uni-obuda.hu/szabozs/

ADATBÁZISOK http://users.nik.uni-obuda.hu/szabozs/. ISMÉTLÉS…. SELECT Záradékok sorrendje. INTO FROM WHERE GROUP BY HAVING UNION/MINUS INTERSECT ORDER BY. T ÖBBTÁBLÁS LEKÉRDEZÉSEK. "CROSS-JOIN", "MANUAL JOIN" INNER JOIN / NATURAL JOIN LEFT JOIN, RIGHT JOIN FULL JOIN / UNION JOIN.

heba
Download Presentation

ADATBÁZISOK users.nik.uni-obuda.hu/szabozs/

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADATBÁZISOK http://users.nik.uni-obuda.hu/szabozs/ szabo.zsolt@nik.uni-obuda.hu

  2. ISMÉTLÉS… szabo.zsolt@nik.uni-obuda.hu

  3. SELECTZáradékok sorrendje • INTO • FROM • WHERE • GROUP BY • HAVING • UNION/MINUS • INTERSECT • ORDER BY szabo.zsolt@nik.uni-obuda.hu

  4. TÖBBTÁBLÁS LEKÉRDEZÉSEK • "CROSS-JOIN", "MANUAL JOIN" • INNER JOIN / NATURAL JOIN • LEFT JOIN, RIGHT JOIN • FULL JOIN / UNION JOIN szabo.zsolt@nik.uni-obuda.hu

  5. ALLEKÉRDEZÉSEK szabo.zsolt@nik.uni-obuda.hu

  6. HALMAZMŰVELETEK • [lekérdezés] [halmazművelet] [lekérdezés] • Fontos: a két lekérdezés azonos oszlopszámú legyen! • Műveletek: UNION, INTERSECT, MINUS / EXCEPT • pl: select min(sal) as MIN_AVG_MAX from emp UNION select avg(sal) from emp UNION select max(sal) from emp; • A MySQL BÉNA: http://www.bitbybit.dk/carsten/blog/?p=71 szabo.zsolt@nik.uni-obuda.hu

  7. ALLEKÉRDEZÉS • Alapelv: a fő-lekérdezés bizonyos részeiben (mezőlista, WHERE záradék, FROM záradék) nem egyszerű mezőnév/kifejezés/táblanév található, hanem egy beágyazott, zárójelezett második lekérdezés  al-lekérdezés (sub-query) • A MySQL csak újabb (>4.1) verziókkal támogatja (korlátozásokkal…) szabo.zsolt@nik.uni-obuda.hu

  8. ALLEKÉRDEZÉS A MEZŐLISTÁBAN • Az allekérdezés egy értékkel tér vissza [more than one row / too many values / operand should contain 1 column(s)] • SELECT sal,(select max(sal) from emp) as MAX,(select max(sal) from emp)-sal as DELTA FROM empORDER BY DELTA desc; szabo.zsolt@nik.uni-obuda.hu

  9. ALLEKÉRDEZÉS A FROM-BAN(INLINE NÉZET) • Az allekérdezés tetszőleges táblát ad vissza, amelyet a lekérdezésben statikus táblaként kezelhetünk • select dolgozo.fizu from (select sal as fizu from emp) dolgozo order by fizu desc; • select * from (select a.empno, a.ename, a.mgr, b.empno, b.ename from emp a, emp b where a.mgr=b.empno) order by ename asc; -- Aliast kell használni!!! szabo.zsolt@nik.uni-obuda.hu

  10. ALLEKÉRDEZÉS A FROM-BAN SELECT bosses.Dolg_Nev as Worker, bosses.Fonok_Nev as Boss FROM ( select a.empno as Dolg_ID, a.ename as Dolg_Nev, a.mgr as Dolg_Fonok, b.empno as Fonok_ID, b.ename as Fonok_Nev from emp a, emp b where a.mgr=b.empno) bosses ORDER BY Worker asc; szabo.zsolt@nik.uni-obuda.hu

  11. ALLEKÉRDEZÉS A FROM-BAN SELECT emp.deptno, min, ename FROM ( Select deptno, min(sal) as min From emp Group by deptno ) minimumok, empWHERE emp.sal=minimumok.min and emp.deptno=minimumok.deptno; szabo.zsolt@nik.uni-obuda.hu

  12. ALLEKÉRDEZÉS A WHERE-BEN • Az allekérdezés egy értékkel tér vissza [more than one row / too many values / operand should contain 1 column(s)] • select ename, sal from emp where sal>(select avg(sal) from emp); • select ename, sal from emp where sal=(select min(sal) from emp); szabo.zsolt@nik.uni-obuda.hu

  13. ALLEKÉRDEZÉS A WHERE-BEN • Az allekérdezés egy oszloppal tér vissza • Használható operátorok: [NOT] IN, ANY, ALL • Példa: select sal, mod(round(sal/1000), 2) from emp; select sal from emp where mod(round(sal/1000), 2)=0; select sal from emp where deptno = 10; szabo.zsolt@nik.uni-obuda.hu

  14. ALLEKÉRDEZÉS A WHERE-BEN • select ename, sal from emp where sal IN (select sal from emp where deptno = 10); • select ename, sal from emp where sal NOT IN (select sal from emp where deptno = 10); szabo.zsolt@nik.uni-obuda.hu

  15. ALLEKÉRDEZÉS A WHERE-BEN • select ename, sal from emp where sal> ANY (select sal from emp where deptno = 10); • select ename, sal from emp where sal>(select min(sal) from emp where deptno = 10);  Ugyanaz az eredmény  Oracle az ANY-nél rendez!!! szabo.zsolt@nik.uni-obuda.hu

  16. ALLEKÉRDEZÉS A WHERE-BEN • select ename, sal from emp where sal> ALL (select sal from emp where deptno = 10); • select ename, sal from emp where sal> ALL (select sal from emp where deptno = 30); • select ename, sal from emp where sal>(select max(sal) from emp where deptno = 30);  Ugyanaz az eredmény  Itt az Oracle sem rendez…  Mindig kell ORDER BY, ha rendezni akarunk szabo.zsolt@nik.uni-obuda.hu

  17. ALLEKÉRDEZÉSEK • UNION, INTERSECT, MINUS • Allekérdezés a • Mezőlistában: 1 érték • From-ban: 1 tábla (aliast használva) • Where-ben: 1 érték • Where-ben: 1 oszlop – IN, ANY, ALL szabo.zsolt@nik.uni-obuda.hu

  18. GYAKORLÁS szabo.zsolt@nik.uni-obuda.hu

  19. 1. FELADAT • Listázza ki a dolgozók nevét, fizetését, főnökük nevét, fizetését, részlegének helyét, valamint ezen helyszín összfizetését! szabo.zsolt@nik.uni-obuda.hu

  20. MEZŐK ÉS TÁBLÁK KIVÁLASZTÁSA • Helyszín összfizetése  EMP és DEPT összekapcsolása, fizetések összege • Dolgozó neve, Dolgozó fizetése  EMP D • Főnök neve, Főnök fizetése  EMP D és EMP F összekapcsolása, EMP F • Főnök részlegének helye  EMP F és DEPT összekapcsolása szabo.zsolt@nik.uni-obuda.hu

  21. RÉSZLEG-ÖSSZFIZETÉSEK • Egyszerű többtáblás GROUP BY • EMP_1 <deptno, deptno> DEPT_1, majd helyszínenként csoportosítva a fizetések összege szabo.zsolt@nik.uni-obuda.hu

  22. RÉSZLEG-ÖSSZFIZETÉSEK SELECT sum(emp.sal) as dept_sal, dept.loc as dept_loc FROM emp, dept WHERE emp.deptno=dept.deptno GROUP BY dept.loc; szabo.zsolt@nik.uni-obuda.hu

  23. DOLGOZÓK-FŐNÖKÖK-FŐNÖKADATOK • Dolgozó neve, Dolgozó fizetése  EMP_2 • Főnök neve, Főnök fizetése  EMP_2 és EMP_3 összekapcsolása, EMP_3 • Főnök részlegének helye  A EMP_3 és DEPT_2 összekapcsolása EMP_2 <empno, mgr> EMP_3 EMP_3 <deptno, deptno> DEPT_2 szabo.zsolt@nik.uni-obuda.hu

  24. DOLGOZÓK-FŐNÖKÖK-FŐNÖKADATOK szabo.zsolt@nik.uni-obuda.hu

  25. DOLGOZÓK-FŐNÖKÖK-FŐNÖKADATOK SELECT work.ename as work_name, work.sal as work_sal, boss.ename as boss_name, boss.sal as boss_sal, dept.loc as boss_loc FROM emp work, emp boss, dept WHERE work.mgr=boss.empno and boss.deptno=dept.deptno; szabo.zsolt@nik.uni-obuda.hu

  26. VÉGEREDMÉNY • DOLGOZÓK – FŐNÖKÖK – FŐNÖKADATOK  data1 • RÉSZLEG-ÖSSZFIZETÉSEK  data2 • data1 <boss_loc, dept_loc> data2 szabo.zsolt@nik.uni-obuda.hu

  27. SELECT * from (SELECT work.ename as work_name, work.sal as work_sal, boss.ename as boss_name, boss.sal as boss_sal, dept.loc as boss_loc FROM emp work, emp boss, dept WHERE work.mgr=boss.empno and boss.deptno=dept.deptno) data1, (SELECT sum(emp.sal) as dept_sal, dept.loc as dept_loc FROM emp, dept WHERE emp.deptno=dept.deptno GROUP BY dept.loc) data2 WHERE data1.boss_loc=data2.dept_loc ORDER BY boss_name, work_name; szabo.zsolt@nik.uni-obuda.hu

  28. 2. FELADAT (4.12) Listázza ki az egyes főnökök beosztottainak számát, telephelyét, átlagjövedelmét, a főnök és a beosztottak átlagjövedelme közti különbséget. Akkor most a főnökök telephelye (A) kell, vagy a beosztottaké (B)??? szabo.zsolt@nik.uni-obuda.hu

  29. MEZŐK ÉS TÁBLÁK KIVÁLASZTÁSA • Beosztottak száma, átlagjövedelme  EMP.MGR szerinti GROUP BY • Részleg  EMP és DEPT összekapcsolása szabo.zsolt@nik.uni-obuda.hu

  30. EMP.MGR szerinti GROUP BY • Ezzel ki lehet hozni egy főnökazonosító-átlagjövedelem-beosztottak_száma táblát, amit a többi táblákhoz főnökazonosítóval csatolunk majd • A kapcsolat a feladat értelmezésétől függ szabo.zsolt@nik.uni-obuda.hu

  31. Dolgozók átlagjövedelme és darabszáma SELECT avg(sal+nvl(comm, 0)) as avg_work_sal, count(*) as work_num, mgr as boss_id FROM emp GROUP BY mgr  Inline nézet: Data  Ezután táblák összekapcsolása szabo.zsolt@nik.uni-obuda.hu

  32. TÁBLÁK ÖSSZEKAPCSOLÁSA (A) szabo.zsolt@nik.uni-obuda.hu

  33. TÁBLÁK ÖSSZEKAPCSOLÁSA (B) szabo.zsolt@nik.uni-obuda.hu

  34. TÁBLÁK ÖSSZEKAPCSOLÁSA (A) szabo.zsolt@nik.uni-obuda.hu

  35. SELECT emp.ename as boss_name, emp.sal+nvl(emp.comm, 0) as boss_sal, data.avg_work_sal as boss_worker_sal, data.work_num as boss_worker_num, dept.loc as boss_loc, emp.sal+nvl(emp.comm, 0) - data.avg_work_sal as sal_delta FROM (SELECT avg(sal+nvl(comm, 0)) as avg_work_sal, count(*) as work_num, mgr as boss_id FROM emp GROUP BY mgr ) data, emp, dept WHERE data.boss_id=emp.empno (+) and emp.deptno=dept.deptno (+); szabo.zsolt@nik.uni-obuda.hu

  36. TÁBLÁK ÖSSZEKAPCSOLÁSA (B) szabo.zsolt@nik.uni-obuda.hu

  37. SELECT d.ename as work_name, f.ename as boss_name, f.sal+nvl(f.comm, 0) as boss_sal, data.avg_work_sal as boss_worker_sal, data.work_num as boss_worker_num, dept.loc as worker_loc, f.sal+nvl(f.comm, 0) -data.avg_work_sal as sal_delta FROM (SELECT avg(sal+nvl(comm, 0)) as avg_work_sal, count(*) as work_num, mgr as boss_id FROM emp GROUP BY mgr ) data, emp d, emp f, dept WHERE data.boss_id=f.empno (+) and f.empno=d.mgr (+) and d.deptno=dept.deptno (+); szabo.zsolt@nik.uni-obuda.hu

  38. 3. Feladat Listázza telephelyenként az egyes munkaköröket, valamint az e munkakörökben dolgozók létszámát, legkisebb, legnagyobb és átlagos jövedelmét. A lista elsődlegesen a telephely szerint növekvően, másodlagosan a munkakör szerint csökkenően legyen rendezve, és használjon kifejező másodlagos oszlopneveket. szabo.zsolt@nik.uni-obuda.hu

  39. MUNKAKÖR-ADATOK SELECT count(*) as Work_Num, min(sal+nvl(comm, 0)) as Minimum, max(sal+nvl(comm, 0)) as Maximum, avg(sal+nvl(comm, 0)) as Atlag, job FROM emp GROUP BY job; szabo.zsolt@nik.uni-obuda.hu

  40. TELEPHELYEK ÉS MUNKAKÖRÖK SELECT distinct loc, job FROM emp, dept WHERE emp.deptno=dept.deptno ORDER BY loc; szabo.zsolt@nik.uni-obuda.hu

  41. EREDMÉNY (A) SELECT * FROM (SELECT distinct dept.deptno, loc, job FROM emp, dept WHERE emp.deptno=dept.deptno ORDER BY loc) data2, (SELECT count(*) as Work_Num, min(sal+nvl(comm, 0)) as Minimum, max(sal+nvl(comm, 0)) as Maximum, avg(sal+nvl(comm, 0)) as Atlag, job FROM emp GROUP BY job) data1 WHERE data1.job=data2.job ORDER BY data2.deptno asc, data1.job desc; szabo.zsolt@nik.uni-obuda.hu

  42. EREDMÉNY (B) SELECT count(*) as Work_Num, min(sal+nvl(comm, 0)) as Minimum, max(sal+nvl(comm, 0)) as Maximum, avg(sal+nvl(comm, 0)) as Atlag, job, loc FROM emp, dept WHERE emp.deptno=dept.deptno GROUP BY job, loc ORDER BY loc asc, job desc; szabo.zsolt@nik.uni-obuda.hu

  43. FELADAT • Jelenítsük meg a dolgozók nevét, részlegének nevét, és a főnökük nevét. A listában szerepeljen a dolgozók és a főnökök jövedelembeállási értéke is. • Jövedelembeállási érték: (jövedelem) – (a vele egy évben belépett dolgozók átlagjövedelme) szabo.zsolt@nik.uni-obuda.hu

  44. SELECTZáradékok sorrendje • INTO • FROM • WHERE • GROUP BY • HAVING • UNION/MINUS • INTERSECT • ORDER BY szabo.zsolt@nik.uni-obuda.hu

More Related