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 www.jerry.gorextar.hu/ab1 www.gorex.gorextar.hu/ab1 www.gazdinfo.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 é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 • edazenlekerdezesem • 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.

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

17. 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.

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

19. 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 salNOT BETWEEN 1200 AND 2900 ORDER BY sal, ename;

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

21. 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’);

22. További feltételes kifejezések • Mi van akkor ha több munkakörre is kíváncsiak vagyunk? Mondjuk „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’);

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

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

25. 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;

26. 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. Miért? • Ha lekérdezzük az emp táblát látjuk, hogy akik nem kapnak jutalmat oda nincs semmi se írva, ez nem egyenlő a 0-val!!! • Ezért így kérdezzük le:SELECT ename, comm FROM empWHERE comm IS NULL;

27. 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’);

28. Még egy pár apróság… • Eltelt idő meghatározása • Napokban SELECT ROUND(sysdate - TO_DATE(20031219,'YYYYMMDD')) FROMdual; • HónapokbanSELECT ROUND(MONTHS_BETWEEN(sysdate, TO_DATE(20031219,’YYYYMMDD’))) FROM dual; • ÉvekbenSELECT ROUND(MONTHS_BETWEEN(sysdate, TO_DATE(20031219,’YYYYMMDD’))/12) FROM dual;

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. • MAX() • A legnagyobb értéked adja meg a csoportban. • COUNT() • Megszámolja a csoport elemeinek számát. • ROUND() • Kerekítés • Pl.: ROUND(AVG(sal),2) – 2 tizedes pontosan kerekít • Ezeket a függvényeket mindig GROUP BY-al használjuk.

30. 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. • MAX() • A legnagyobb értéked adja meg a csoportban. • COUNT() • Megszámolja a csoport elemeinek számát. • ROUND() • Kerekítés • Pl.: ROUND(AVG(sal),2) – 2 tizedes pontosan kerekít • Ezeket a függvényeket mindig GROUP BY-al használjuk.

31. 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 fizetés és ezt rendezzük növekvő sorrendbe. SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

32. Önálló feladat • A főnök szeretné tudni a részlegeinek: • A legalacsonyabb fizetést • A legmagasabb fizetést • Az átlagos fizetést • A létszámot

33. 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;

34. 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(*)),2) FROM emp GROUP BY deptno;

35. 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;

36. 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.

37. 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;

38. Házi feladat 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.

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

40. Házi feladat (2) 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 deptnoAS 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;