Adatbázis használat I.

1. Adatbázis használat I. 2. gyakorlat

2. Figyelem!!! 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. Weboldalak www.orakulum.com http://analog.nik.uni-obuda.hu:65111 Hallgato / Adatbazis2013 Ha nem működik ftp://analog.nik.uni-obuda.hu

4. Egyszerű lekérdezések • Kérdezzük le az emptábla tartalmát! SELECT * FROM emp;

5. Egyszerű lekérdezések • Kérdezzük le az depttábla tartalmát! SELECT * FROM dept;

6. Egyszerű lekérdezések • Ha nem vagyunk kíváncsiak az egész táblára lekérdezhetünk oszlopokat is belőle. • Kérdezzük le az emptáblából a dolgozók nevét és fizetését. SELECT ename, sal FROM emp;

7. Egyszerű lekérdezések • Kérdezzük le az emptáblából a dolgozók nevét,munkakörétés részlegazonosítóját. SELECT ename, job, deptno FROM emp; Megjegyzés: amikor több oszlopot kérdezünk le vesszőkkel választjuk el őket, de az utolsó oszlopnév után nem rakunk vesszőt.

8. Egyszerű lekérdezések • Kérdezzük le az azonosítót és a nevet, de beszédes oszlopneveket szeretnénk látni, ezért nevezzük át az oszlopokat. SELECT empno AS Azonosító, ename AS Név FROM emp; Megjegyzés: összetett oszlopneveket is tudunk kezelni, ekkor azt az oszlopnevet " " [shift + 2] közé tesszük (pl.: név helyett dolgozó neve).

9. Egyszerű lekérdezések • Szeretnénk megtudni, hogy ki keres sokat (több, mint 2000 $) és mi a beosztása. SELECT ename, sal, job FROM emp WHERE sal > 2000;

10. Egyszerű lekérdezések • Ki kap jutalékot és mennyit? SELECT ename, comm FROM emp WHERE comm > 0;

11. Lekérdezések • Rendezzük a táblázatunkat dolgozó azonosító szerinti növekvő sorrendbe! SELECT * FROM emp ORDER BY empno; • Most ugyanezt csökkenő sorrendbe SELECT * FROM emp ORDER BY empno DESC;

12. Lekérdezések • Rendezzük a táblázatunkat fizetés szerinti növekvő sorrendbe, majd csökkenő sorrendbe! SELECT * FROM emp ORDER BY sal ASC/DESC; • Rendezzünk most fizetés szerint növekvő és jutalék szerint csökkenő sorrendbe SELECT * FROM emp ORDER BY sal, commdesc;

13. Előre megírt lekérdezés(ek) futtatása • ed azenlekerdezesem • Megnyit egy jegyzettömböt, amelybe írhatjuk a lekérdezést. • Fontos, hogy mindig mentsük a munkánkat benne!!! • @azenlekerdezesem • Ezzel futtatjuk az előre megírt lekérdezésünket. • Holt találjuk meg ezt a fájlt? • Nézzük meg a kereső segítségével. • Írjuk be, hogy azenlekerdezesem.sql

14. 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. • Mindezt jegyzettömbben készítsük el.

15. Gyakorlás SELECT ename AS ”Név”, job AS ”Munkakör”, sal AS ”Fizetés” FROM emp WHERE sal < 1500 ORDER BY ename;

16. További feltételes kifejezések • További kifejezések: • Logikai műveletek • AND, OR, NOT • Hasonlító műveletek • >, >=, <, <=, =, <>, != • Intervallum • Oszlopkifejezés BETWEEN AlsóHatár AND FelsőHatár

17. 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 | EXISTS allekérdezés • NULL értékre vonatkozó vizsgálat • Oszlopkifejezés IS NULL | IS NOT NULL

18. 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ükszerint legyen rendezve. • Írassuk ki az 1200 és 2900 USD közöttNEM 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.

19. További feltételes kifejezések SELECT emp.* FROM emp WHERE sal BETWEEN 1200 AND 2900 ORDER BY sal, ename;

20. További feltételes kifejezések 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;

21. 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?

22. Megoldás • Kis és nagybetűk számítanak! SELECT ename, job, sal FROM empWHERE job LIKE ’%CLERK%’; Elég a pl.: a ’%CLE%’ is vagy: SELECT ename, job, sal FROM emp WHERE job LIKE UPPER(’Clerk’);

23. További feltételes kifejezések • Mi van akkor ha több munkakörre is kíváncsiak vagyunk? Mondjuk „salesman” és „clerk”. SELECT ename, job, sal FROM emp WHERE UPPER(job) IN (’SALESMAN’,’CLERK’); Megjegyzés: lehet így is … LOWER(job) IN (’salesman’, ’clerk’);

24. 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.*, emp.sal+emp.comm AS Jövedelem FROM emp ORDER BY Jövedelem ASC; • Nézzük meg, hogy mi történt.Miért történhetett ez?

25. 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. SELECT emp.*, emp.sal+NVL(emp.comm,0) AS Jövedelem FROM emp ORDER BY Jövedelem ASC;

26. Még egy pár apróság… • Milyen munkakörök léteznek ennél a cégnél? SELECT job FROM emp; • De nekünk csak egyszer van szükségünk minden munkakörre. SELECT DISTINCT job FROM emp;

27. Még egy pár apróság… • Emlékezzünk a jutalékos példára ott arra voltunk kíváncsiak ki kap jutalmat, most nézük meg ki nem kap jutalmat. • A …Wherecomm=0; nem működik jól. Miért? • Ezért így kérdezzük le:SELECT ename, comm FROM empWHERE comm IS NULL OR comm = 0;

28. Még egy pár apróság… • Művelet dátumokkal SELECT ename AS Név, hiredate AS Dátum, EXTRACT(YEAR FROM hiredate) AS BeÉv, EXTRACT(MONTH FROM hiredate) AS BeHónap, EXTRACT(DAY FROM hiredate) AS BeNap FROM emp WHERE hiredate > TO_DATE(’1981.máj.05’);

29. Csoportosító lekérdezések • 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.

30. Csoportosító lekérdezések • 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.

31. Kerekítő függvény • A kerekítésre a ROUND függvényt használhatjuk, amely így néz ki: ROUND(érték,tizedes) • Pl.: select AVG(sal) AS ”Átlagfizetés”, ROUND(AVG(sal),2) AS ”Átlagfizetés kerekítéssel” • FROM emp;

32. Csoportosító lekérdezések • Nézzünk az előbbiekre példát • Nézzük meg mi a legnagyobb fizetés részlegenként. SELECT deptno, MAX(sal) FROM emp GROUP BY deptno; • Mennyi a részlegenkénti átlagfizetés és ezt rendezzük növekvő sorrendbe. SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

33. Önálló feladat • A főnök szeretné tudni a részlegeinek: • A legalacsonyabb fizetését • A legmagasabb fizetését • Az átlagos fizetését • A létszámát

34. A megoldás SELECT deptno AS Részleg, MIN(sal) AS Legkisebb, MAX(sal) AS Legnagyobb, AVG(sal) AS Átlag, COUNT(*) AS Létszám FROM emp GROUP BY deptno;

35. Bonyolítsuk a dolgokat… Listázzuk ki a legkisebb, a legnagyobb és az átlagos részlegenkénti létszámokat: SELECT MIN(COUNT(*)), MAX(COUNT(*)), ROUND(AVG(COUNT(*)),0) FROM emp GROUP BY deptno;

36. Szűrés csoportokra (A HAVING használata) • Listázzuk ki azokat a részlegeket amelyek 2000 USD-nél nagyobb átlagjövedelműek a jövedelem szerint rendezve. SELECT deptno, ROUND(AVG(sal+NVL(comm,0))) AS átlagjövedelem FROM emp GROUP BY deptno HAVING ROUND(AVG(sal+NVL(comm,0))) > 2000 ORDER BY átlagjövedelem;

37. Szűrés csoportokra (A HAVING használata) • 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.

38. Megoldás SELECT AVG(sal) AS ”Átlagfizetés”, mgr AS ”Főnök” FROM emp WHERE comm IS NULL AND mgr IS NOT NULL GROUP BY mgr HAVING AVG(sal) > 1000 ORDER BY ”Átlagfizetés” DESC;

39. Több oszlop szerinti csoportosítás Lehetőség van több oszlop szerint is csoportosítani. Pl.: Selectcount(*), avg(sal), deptno, mgr Fromemp Group bydeptno, mgr; Generáljunk groupby hibát: Selectcount(*), avg(sal), deptno, mgr, ename Fromemp Group bydeptno, mgr;

40. Házi feladat Listázza ki a legfeljebb 2200 USD átlagjövedelmű részlegeknek az átlagjövedelmét és ezt rendezzük átlagjövedelem szerint növekvő sorrendbe majd csökkenő sorrendbe és használjon beszédes oszlopneveket.

41. Megoldás SELECT deptno AS Részleg, AVG(sal+nvl(comm,0)) AS Átlag FROM emp GROUP BY deptno HAVING AVG(sal+nvl(comm,0)) <= 2200 ORDER BY Átlag asc/desc;

42. Házi feladat (2) Listázza ki részlegenként és minden részlegben munkakörönként a 2000 USD-nál nagyobb jövedelmű dolgozók átlagjövedelmét, mégpedig úgy, hogy a lista legyen az utóbbi szerint növekvő és használjon beszédes oszlopneveket.

43. Megoldás SELECT deptno AS Részleg, job AS Munkakör, avg(sal+NVL(comm,0)) AS Átlag FROM emp WHERE (sal + NVL(comm,0)) > 2000 GROUP BY deptno, job ORDER BY Átlag;

44. Források: Nagy Gabriella diasorozata Példatár