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

1. Adatbázisok használata 1(2. gyakorlat) 2012. tavaszi félévVéső Tamás

2. Hallgatói Tájékoztató A jelen bemutatóban található adatok, tudnivalók és információk a számonkérendő anyag vázlatát képezik. Ismeretük szükséges, de nem elégséges feltétele a sikeres zárthelyi(k)nek. Sikeres zárthelyihez, illetve vizsgához a jelen bemutató tartalmán felül a kötelező irodalomként megjelölt anyag, a gyakorlatokon szóban, illetve a táblán átadott tudnivalók ismerete, valamint a gyakorlatokon megoldott példák és az otthoni feldolgozás céljából kiadott feladatok önálló megoldásának képessége is szükséges. Véső Tamás OE­NIK 2012. 2 / 29

3. WHERE • A segítségével szűrhetjük az eredményhalmazunkat • Operátorok: = <> < <= > >= • Például: sal > 1000 • ‘!=‘ ? ‘<>’ Véső Tamás OE­NIK 2012. 3 / 29

4. WHERE • Logikai operátorok: • AND: • A segítségével több feltételt is megszabhatunk egyszerre, melynek mind teljesülnie kell • OR: • Ha AND helyett „OR”-t használunk, akkor elég az egyik feltételnek teljesülnie • Keresés zárt intervallumban: • BETWEEN 100 AND 1500 Véső Tamás OE­NIK 2012. 4 / 29

5. Gyakorlás • Listázza ki azon dolgozók összes adatát, akiknek a fizetésük kevesebb, mint 1230$! • Listázza ki azon dolgozók nevét és fizetését, akik fizetése 1500$ vagy 1600$! • Listázza ki azon dolgozók nevét és fizetését, akik fizetése 1000$ és 1700$ között van! Véső Tamás OE­NIK 2012. 5 / 29

6. Oszlopfejlécek használata Szeretnénk egy kicsit szépíteni lekérdezésünk megjelenő eredményén: SELECT ename AS ”Név”, sal AS ”fizetés” FROM emp ORDER BY ename; • Név fizetés • ---------- ---------- • ADAMS 1100 • ALLEN 1600 • BLAKE 2850 • ……… ….. Véső Tamás OE­NIK 2012. 6 / 29

7. Gyakorlás Írassuk ki azon dolgozók nevét, munkakörét és fizetését, akiknek a fizetése 1500 USD alatt van.A lista fejléce legyen „Név”, „Munkakör”, „Fizetés”, rendezzen a dolgozók neve szerint. Véső Tamás OE­NIK 2012. 7 / 29

8. Gyakorlás SELECT ename AS ”Név”, job AS ”Munkakör”, sal AS ”Fizetés” FROM emp WHERE sal < 1500 ORDER BY ename; Véső Tamás OE­NIK 2012. 8 / 29

9. További feltételes kifejezések • További kifejezések: • Alsztringvizsgálat • Oszlopkifejezés LIKE ’%alsztring%’ • Pl.: SELECT * FROM emp WHERE ename LIKE UPPER(’%ar%’); • Allekérdezésre vonatkozó halmazvizsgálat • Oszlopkifejezés [NOT] IN | ANY | ALL | EXSIST allekérdezés • NULL értékre vonatkozó vizsgálat (következő dia) • Oszlopkifejezés IS NULL | IS NOT NULL Véső Tamás OE­NIK 2012. 9 / 29

10. További feltételes kifejezések • Ki kap jutalékot? Vigyázat, aki nem kap, annál az érték nem 0, hanem nincs is ott semmi! • A …WHERE comm=0; nem fog működni. SELECT ename, comm FROM emp WHERE commIS NOT NULL; Véső Tamás OE­NIK 2012. 10 / 29

11. További feltételes kifejezések Írassuk ki az 1200 és 2900 USD között kereső dolgozók minden adatát úgy, hogy a fizetés, majd a nevük szerint legyen rendezve. Írassuk ki az 1200 és 2900 USD között NEMkereső dolgozók nevét, keresetét, jutalékát úgy, hogy a fizetés, majd a nevük szerint legyen rendezve és beszédesek legyenek az oszlopnevek. Véső Tamás OE­NIK 2012. 11 / 29

12. További feltételes kifejezések Írassuk ki az 1200 és 2900 USD között kereső dolgozók minden adatát úgy, hogy a fizetés, majd a nevük szerint legyen rendezve. SELECT emp.* FROM emp WHERE sal BETWEEN 1200 AND 2900 ORDER BY sal, ename; Véső Tamás OE­NIK 2012. 12 / 29

13. További feltételes kifejezések Írassuk ki az 1200 és 2900 USD között NEM kereső dolgozók nevét, keresetét, jutalékát úgy, hogy a fizetés, majd a nevük szerint legyen rendezve és beszédesek legyenek az oszlopnevek. SELECT emp.ename AS Név, emp.sal AS Fizetés, emp.comm AS Jutalék FROM emp WHERE sal NOT BETWEEN 1200 AND 2900 ORDER BY sal, ename; Véső Tamás OE­NIK 2012. 13 / 29

14. További feltételes kifejezések Írassuk ki a „clerk” munkakörű dolgozók nevét, munkakörét, fizetését. A lista fejléce legyen „Név”, „Munkakör”, „Fizetés”, rendezzen a dolgozók neve szerint. SELECT ename, job, sal FROM emp WHERE job LIKE ’%clerk%’; Mi történt? Miért nem jelent meg a clerk? Véső Tamás OE­NIK 2012. 14 / 29

15. Megoldás Kis és nagybetűk számítanak! SELECT ename, job, sal FROM empWHERE job LIKE ’%CLERK%’; vagy: SELECT ename, job, sal FROM emp WHERE job LIKE UPPER(’Clerk’); ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300 Véső Tamás OE­NIK 2012. 15 / 29

16. További feltételes kifejezések Ha több munkakörre is kíváncsiak vagyunk? Például: „sealsman” és „clerk”. SELECT ename, job, sal FROM emp WHERE UPPER(job) IN UPPER(’SALESMAN’,’CLERK’); Megjegyzés: lehet így is … LOWER(job) IN (’salesman’, ’clerk’); Véső Tamás OE­NIK 2012. 16 / 29

17. Dátum használata Műveletek dátumokkal: SELECT ename AS Név, hiredate AS Dátum, EXTRACT(YEAR FROM hiredate) AS BeÉv, EXTRACT(MONTH FROM hiredate) AS BeHó, EXTRACT(DAY FROM hiredate) AS BeNap FROM emp WHERE hiredate > TO_DATE('1981.máj.05'); Dátumformátum probléma: „TO_DATE('1981.máj.05');” TO_DATE('1981.05.05','YYYY.MM.DD'); Véső Tamás OE­NIK 2012. 17 / 29

18. Speciális függvények Számoljuk ki a dolgozóknak a jövedelmét (sal+comm) és rendezzük növekvő rendbe. SELECT emp.*, sal+comm FROM emp; Nézzük meg, hogy mi történt.Miért történhetett ez? Véső Tamás OE­NIK 2012. 18 / 29

19. Speciális függvények Ilyen estekben használjuk az NVL függvényt.Lényege, ahol nincs érték megadva azt kitölti az általunk megadottal. Nézzük meg az előző példát a függvény segítségével. Véső Tamás OE­NIK 2012. 19 / 29

20. Speciális függvények SELECT ename AS Név, sal AS Fizetés, sal+NVL(comm,0) AS Jövedelem FROM emp ORDER BY Jövedelem DESC; Véső Tamás OE­NIK 2012. 20 / 29

21. Speciális függvények • Milyen munkakörök léteznek ennél a cégnél? SELECT job FROM emp ORDER BY jobasc; • El lehet tűntetni a többször szereplő sorokat? Véső Tamás OE­NIK 2012. 21 / 29

22. A megoldás: • SELECT DISTINCT job FROM emp ORDER BY jobasc; Véső Tamás OE­NIK 2012. 22 / 29

23. Csoportfüggvények • A leggyakrabban használt függvények: • AVG() • Kiszámolja a csoport(ok) átlagát. • SUM() • A csoportban összeget számol. • MIN() • A legkisebb értéket adja meg a csoportban. • MAX() • A legnagyobb értéked adja meg a csoportban. • COUNT() • Megszámolja a csoport elemeinek számát. • Ezeket a függvényeket mindig GROUP BY-al használjuk. • ROUND() //nem csoportfüggvény, de használnunk kell/ • Kerekítés • Pl.: ROUND(AVG(sal),2) – 2 tizedes pontosan kerekít Véső Tamás OE­NIK 2012. 23 / 29

24. GROUP BY (csoportosítás) Példa: legnagyobb fizetés részlegenként SELECT deptno, MAX(sal) FROM emp GROUP BY deptno; Eredmény: DEPTNO MAX(SAL) ---------- ---------- 30 2850 20 3000 10 5000 • A csoportfüggvénybe is lehet kifejezést írni. • . Véső Tamás OE­NIK 2012. 24 / 29

25. Önálló feladat • A főnök szeretné látni részlegenként: • A legalacsonyabb fizetést; • A legmagasabb fizetést; • Az átlagos fizetést; • A létszámot. Véső Tamás OE­NIK 2012. 25 / 29

26. Szűrés csoportokra • Listázzuk főnökönként (mgr) a jutalékban nem részesülő dolgozóinak átlagfizetését csökkenő sorrendben, feltéve, hogy ez az érték 1000 USD-nál több. SELECT AVG(sal) AS "Átlagfizetés", mgr AS "Fonök" FROM emp WHERE comm IS NULL AND mgr IS NOT NULL GROUP BY mgr HAVING AVG(sal) > 1000 ORDER BY "Átlagfizetés" DESC; • having – a már csoportosított eredményre vonatkozó feltétel. Véső Tamás OE­NIK 2012. 26 / 29

27. Önálló munka • 2000 USD-nál nagyobb átlagjövedelmű részlegek, a jövedelem szerint rendezve? Véső Tamás OE­NIK 2012. 27 / 29

28. Köszönöm a Figyelmet! Jövő héten gépes ZH: dátumok, csoportosítás, Csoportfüggvények,szűrés,speciális függvények lehetnek benne. veso.tamas@gorextar.hu