Adatbázisok használata 1 (3. gyakorlat)

1. Adatbázisok használata 1(3. gyakorlat) 2012. tavaszi félév Vitéz Gergely

2. A diasor ismerete nem helyettesíti a tankönyvet, és a példatárat. A diasor ismerete szükséges, de nem elégséges feltétele a minimum elégséges érdemjegynek!

3. SQL Plus • Virtuális gép indítása • SQL plus indítása • Felhasználónév: scott • Jelszó: tiger • Mit tegyünk ha nem sikerül belépni • 1: Várjunk egy picit, amíg a szolgáltatás elindul • 2: Szolgáltatás újraindítása • Szerkesztő: edfajlnev • Futtatás: @fajlnev OE-NIK 2012.

4. Táblák összekapcsolása

5. Táblák összekapcsolása 2

6. Táblák összekapcsolása 3. SELECT FROM tábla1, tábla2, és így továbbWHEREGROUP BYHAVINGORDER BY;

7. Táblák összekapcsolásaWHERE EMP és DEPT tábla összekapcsolása:Példa: SELECT a.ename, b.locFROM emp a, dept bWHERE a.deptno = b.deptno; avagy: SELECT ename, locFROM emp, deptWHERE emp.deptno = dept.depno;

8. Táblák összekapcsolásaJOIN • INNER • Az oszlopérték mindegyik táblában létezik és megegyezik • LEFT • A kulcsszótól balra álló tábla sorai mindenképpen szerepelnek a listában • RIGHT • A kulcsszótól jobbra álló tábla sorai mindenképpen látszanak a listában • FULL • Mindkettő oszlop sorai látszanak, még ha hiányosak is

9. Táblák összekapcsolásaJOIN 2 Használata:SELECT [Szelekciók]FROM tábla1 INNER/LEFT/RIGHT/FULL JOIN tábla2 ON összekötőfeltétel USING(oszlop1,oszlop2,stb)…

10. Táblák összekapcsolásaJOIN 3 PÉLDA: SELECT ENAME, LOCFROM EMP INNER JOIN DEPT USING(deptno);

11. Táblák összekapcsolása önmagukkal Példa: Listázzuk az alkalmazottakat, és a főnökeik nevét! SELECT dolgozo.enameas ”Dolgozó neve”,fonok.enameas ” Főnök neve”FROM empdolgozo, emp fonokWHERE dolgozo.mgr = fonok.empno; Hol van KING a listából?

12. LEFT JOIN • KING nincs a listában, mert nincs főnöke (nincs hozzá tartozó sor a főnök táblából) • Hogy előkerüljön az eredményben a főnököket LEFT JOIN segítségével kell kapcsolnunk: • SELECT dolgozo.enameas ”Dolgozó neve”,fonok.enameas ” Főnök neve”FROM empdolgozo, emp fonokWHERE dolgozo.mgr = fonok.empno(+);

13. Allekérdezések

14. Allekérdezések SELECT LISTAFROMWHEREGROUP BYHAVINGORDER BY;

15. AllekérdezésekINLINE nézet Ha a FROM részben szerepel az allekérdezés, akkor azt INLINE nézetnek is nevezzük(Ezt mindig másodlagos táblanévvel kell ellátni, kivéve ha csak 1 van)Mennyi a részlegenkénti maximumfizetések átlaga?

16. Megoldás SELECT AVG(inline.Maxfizu) as "Maximum átlag" FROM (SELECT deptnoasReszlegszam, MAX(sal+nvl(comm,0)) asMaxfizu FROM EMP GROUP BY deptno) inline;

17. AllekérdezésekWHERE • IN/NOT IN • IGAZ, ha szerepel az adott halmazban/ha nem • ANY • Ha a halmaz legalább egy eleme megfelel • ALL • Ha a halmaz összes eleme megfelel • EXISTS / NOT EXISTS • Ha a halmaz legalább egy elemet tartalmaz/Egyet sem

18. AllekérdezésekWHERE 2 • A WHERE-ben szereplő allekérdezéseknél, összehasonlító reláció esetén a bal oldalon mindig csak oszlopkifejezés állhat, jobb oldalon szerepelhet allekérdezés is zárójelek között „(” „)” • Példa: • SELECT * FROM emp • WHERE deptno IN ( SELECT deptno FROM dept • WHERE UPPER(dname) = ‘DALLAS’)

19. Mi a különbség?ALL/ANY Select * from emp where sal > ALL (Select sal from emp where sal between 1200 AND 2900); Select * from emp where sal > ANY (Select sal from emp where sal between 1200 AND 2900);

20. AllekérdezésekSELECT • Egyértékű lehet csak • Még utasításon belül sem lehet hivatkozni • Példa: • SELECT * FROM emp WHERE sal < (SELECT • AVG(sal) FROM emp)

21. Féléves Feladat • Következő, azaz 4. alkalomra hozni kell az adatmodellt normalizálva!

22. Gyakorlás! Példatár 3.3 feladat (67.oldal)Listázza a ’ Clerk’ munkakörű dolgozókat foglalkoztató részlegek azononosítóját, nevét, és telephelyét! Ha készen van valaki: Listázza a New York telephely minden dolgozójának nevét, és a főnökük nevét!

23. Több megoldás létezik! • 1: Allekérdezés a FROM részben • Kell nekünk egy tábla, amiben csak azoknak a részlegeknek az azonosítója van, ahol ‚CLERK’ dolgozik. • SelectdeptnoFromempwherejob = ’CLERK’groupbydeptno;

24. 1 példa megoldás (folyt) • Ha ez megvan, nézzük meg, hogy mire van szükségünk: Részleg, Részlegnév és Telephely SELECT dept.deptnoas ”Részleg”,dept.dnameas ”Részlegnév”, dept.locas ”Telephely” FROM dept, (SelectdeptnoFromempwherejob = ’CLERK’groupbydeptno) allerkerdezesunk

25. 1. Példa folytatás SELECT dept.deptnoas ”Részleg”,dept.dnameas ”Részlegnév”, dept.locas ”Telephely” FROM dept, (SelectdeptnoFromempwherejob = ’CLERK’groupbydeptno) allekerdezesunk WHERE allekerdezesunk.deptno = dept.deptno;

26. 1. PéldaAllekérdezés WHERE ben. • Selectdeptnoas "Részleg", dnameas "Részlegnév", locas "Telephely" • fromdept • WHERE deptnoin (Selectdeptno • Fromemp • wherejob = 'CLERK' • groupbydeptno);

27. 2. példa Selectdolgozo.enameas "Dolgozó neve", fonok.enameas "Főnök neve" FROM empdolgozo, emp fonok WHERE dolgozo.mgr = fonok.empno AND dolgozo.deptno IN (Selectdeptnofromdeptwhereloc = 'NEW YORK'); Miért rossz ez a megoldás?

28. 2. Példa jó megoldás Selectdolgozo.enameas "Dolgozó neve", fonok.enameas "Főnök neve" FROM empdolgozo, emp fonok WHERE dolgozo.mgr = fonok.empno(+) AND dolgozo.deptno IN (Selectdeptnofromdeptwhereloc = 'NEW YORK'); A (+) hatására a rendszer úgy kezeli a főnök táblát, mintha lenne egy sor, ami csupa NULL-ból áll.

29. Komplex példa • Listázza ki azon dolgozók nevét, jövedelmét részlegük nevét, részlegük átlagjövedelmét, melyek a részlegük átlagjövedelménél kevesebbet keresnek! Rendezze az eredményt a részleg szerint!

30. Megoldás • selectee.ename, • ee.sal + nvl(ee.comm, 0) assajat, • dd.dname, • round(aa.atlag, 1) asreszlegatlag • from • empee, • (selectdeptno, avg(sal+nvl(comm,0)) asatlag • fromemp • groupbydeptno) aa, • deptdd • whereee.deptno = dd.deptno • and ee.deptno = aa.deptno • and ee.sal + nvl(ee.comm, 0) < aa.atlag • orderbydname;

31. Köszönöm a figyelmet!