Adatbázis használat I.

1. Adatbázis használat I. 3. 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 ftp://analog.nik.uni-obuda.hu http://analog.nik.uni-obuda.hu:65111 Hallgato / Adatbazis2013

4. Többtáblás lekérdezések

5. Többtáblás lekérdezések • Táblákat összekapcsolhatunk FROM és WHERE utasításrészben vagy • FROM utasításrészben a JOIN segítségével

6. JOIN • Fajtái • Inner • Azokat a sorokat (rekordokat) listázza ki, ahol az összekapcsoló oszlopnak mindkét táblában van értéke. • Left • Minden sort felsorol a bal oldali táblából, annak ellenére, hogy a jobb oldali táblában nincsen minden esetben megfelelő érték.

7. JOIN • Fajtái • Right • A jobboldali rekordok közül mindet kilistázza még akkor is, ha a baloldalon nincs megfelelő elem. • Full • Mindent kilistázz a bal és a jobb oldalról is

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

9. Táblák összekapcsolása WHERE-el • A tábláknak is adhatunk másodlagos neveket • …FROM emp dolgozó …; • Gyakran szükség van rá, ha össze akarunk kapcsolni táblákat a WHERE-ben • Például: kérdezzük le a főnökökhöz tartozó beosztottak nevét

10. Táblák összekapcsolása WHERE-el SELECT FROM tábla1, tábla2, és így továbbWHEREGROUP BYHAVINGORDER BY;

11. Táblák összekapcsolása WHERE-el EMP és DEPT tábla összekapcsolása:Példa: SELECT a.ename, b.locFROM emp a, dept bWHERE a.deptno = b.deptno; vagy: SELECT ename, locFROM emp, deptWHERE emp.deptno = dept.depno; A táblának így adunk álnevet

12. Hierarchialekérdezés (CONNET BY) • Segítségével olyan oszlopot hozhatunk létre, amelyből látszik a rekordok egymás alá való rendeltsége. • Általános alakja:SELECT … FROM tábla[WHERE …]CONNECT BY { PRIOR kifejezés = kifejezés vagy kifejezés = PRIOR kifejezés }[START WITH kifejezés = kifejezés][OREDER BY];

13. Hierarchialekérdezés (CONNET BY) • Kulcsszavak jelentése: • LEVEL megadja, hogy az aktuálisan kilistázott elem a hierarchia mely szintjén található. • START WITH megadhatjuk hol honnét kezdjük a hierarchikus szerkezet feldolgozása • WHERE utasításnál nem tudunk szűrni elődöt és utódot csak konkrét sorokat • CONNECT BY utasításrészben megadott feltételt használjuk a sorok és leszármazottaik szűrésére • PIOR a bejárás szerinti szülőcsúcsot határozza meg

14. Hierarchialekérdezés (CONNET BY) Példák • Listázzuk ki a dolgozók hierarchiáját, nevét, részlegét. • Nézzük meg SMITH, hogy neki mennyit kell még előre lépni a létrán, hogy elnök legyen.

15. Allekérdezés • Azokat a lekérdezéseket nevezzük allekérdezésnek, amelyeket nem jelenítünk meg, de valamilyen eredmény(eket) ad át egy külső utasítás számára. • A feldolgozás ilyenkor mindig belülről kifelé halad, azaz a legbelső SELECT értékelődik ki először. • Használható a FROM, WHERE, HAVING utasításrészekben.

16. Allekérdezés (FROM-ban) • A FROM-on belüli allekérdezéstinline nézetnek is nevezzük. • Ilyenkor a FROM-ban lévő SELECT-et másodlagos táblanévvel kell ellátni

17. Allekérdezés (FROM-ban) • Példa: Mennyi a részlegenkénti maximumfizetések átlaga? Select AVG(inlinepelda.Maxfizu) as "Maximum átlag" from (SELECT deptnoasReszlegszam, MAX(sal+nvl(comm,0)) asMaxfizu fromemp groupbydeptno) inlinepelda;

18. Allekérdezés (WHERBEN) • 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

19. Allekérdezés (WHERBEN) • 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 „(” „)” Persze ezeknek összhangban kell lenniük.

20. 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);

21. Házi feladat Kérdezze le minden dolgozó nevét, jövedelmét, részlegének nevétés a részlegében dolgozók legkisebb és legnagyobb jövedelmét, végül pedig a dolgozó jövedelembeállási értékét (vagyis a jövedelmének és az iménti legkisebb értéknek különbségét). A lista legyen a jövedelembeállás alapján rendezve, és használjon másodlagos oszlopneveket.

22. Házi feladat1. lépés feladat értelmezése Keressük meg a lekérdezéseket (általában valamilyen kötőszóval vannak összekapcsolva a feladatban is) Kérdezze le minden dolgozó nevét, jövedelmét, részlegének nevét (dept táblában a dname)és arészlegében dolgozók legkisebb jövedelmét, végül pedig a dolgozó jövedelembeállási értékét (jövedelmének és a legkisebb jövedelem különbségét). A lista legyen a név alapján rendezve, és használjon másodlagos oszlopneveket.

23. Házi feladat2. lépés allekérdezés • A kidolgozás során mindig az allekérdezésselkezdjünk. • Arészlegében dolgozók legkisebb és legnagyobb jövedelmét SELECT MIN(sal+NVL(comm,0)) AS minimumfizetés,deptno AS részleg FROM empGROUP BY deptno;

24. Házi feladat3. lépés a külső lekérdezés • Kérdezze le minden dolgozó nevét, jövedelmét, részlegének nevét és arészlegében dolgozók legkisebb jövedelmét végül pedig a dolgozó jövedelembeállási értékét. • Vegyük észre, hogy bele kell vennünk az allekérdezést a külső lekérdezésbe.

25. Házi feladat3. lépés a külső lekérdezés • Mi kell a külső lekérdezésünkbe • Dolgozó neve • Jövedelme • Részlegének neve • A vele egy csoportban dolgozók legkisebb jövedelme • A jövedelembeállás

26. Házi feladat3. lépés a külső lekérdezés SELECT dolgozó.ename, dolgozó.sal + NVL(dolgozó.comm,0) as fizu, telephely.dname, allek.minimumfizetés,/* elhagyható az allek, ha egyedi */ /*az allekérdezésben a másodlagos oszlpnév*/ dolgozó.sal+ NVL(dolgozó.comm,0) - minimumfizetésas beáll FROM emp dolgozó, dept telephely, (SELECT MIN(sal+NVL(comm,0)) AS minimumfizetés, deptno AS részleg FROM empGROUP BY deptno)allek/* itt nem hagyjuk el sose a másodlagos */ WHERE telephely.deptno = allek.részleg AND /*táblanevet*/ telephely.deptno = dolgozó.deptno ORDER BY dolgozó.ename;

27. Amire figyeljünk • Az allekérdezést mindig zárójelbe kell, hogy tegyük a másodlagos táblanév adása miatt. • PL.: (SELECT MIN(sal+NVL(comm,0)) AS minimumfizetés,deptno AS részleg FROM emp GROUP BY deptno)allek • Megjegyzés: Másodlagos táblanévadáskor NEM használunk AS kulcsszót!!!

28. Amire figyeljünk • Az allekérdezésben használt másodlagos oszlopneveket használjuk a külső SELECT-ben, mintha csak az emp táblából kérdeznénk oszlopokat. • Ha nem egyedi az oszlopnevünk akkor a táblanév.oszlop hivatkozással pontosan meg kell adjuk melyik tábláról van szó. Pl.: telephely.deptno = dolgozó.deptno

29. Amire figyeljünk • A táblák felsorolásánál is ,-t használunk kivéve az utolsó táblát (allekérdezést). Pl.: … emp dolgozó,dept telephely, (SELECT MIN(sal+NVL(comm,0)) AS minimumfizetés, deptno AS részleg FROM emp GROUP BY deptno) allek … Itt már nincs vessző

30. Amire figyeljünk • A WHERE-ben ne felejtsük összekapcsolni a táblákat. • Ki lehet próbálni mi történik, ha elhagyjuk a WHERE részt. • A WHERE-ben az a fontos, hogy minden tábla össze legyen kapcsolva a megfelelő oszlop(okk)al. • WHERE telephely.deptno = allek.részleg AND telephely.deptno= dolgozó.deptno • WHERE dolgozó.deptno= allek.részleg AND telephely.deptno = dolgozó.deptno • Ugyanazt az eredményt adja

31. Házi feladat (2) Írja ki minden dolgozó nevét, jövedelmét, munkakörének nevét, továbbá a munkakörében dolgozók összjövedelmét, végül pedig a dolgozó jövedelembeállási értékét(mekkora az összjövedelem és a dolgozó jövedelmének különbsége), úgy hogy a jövedelem szerint rendezzünk növekvően.

32. Házi feladat (2) SELECT enameAS "Dolgozó neve", sal+NVL(comm,0) AS jövedelem, dolgozó.job AS munkakör, összjövedelem, összjövedelem - sal+NVL(comm,0)AS jövbeáll FROM empdolgozó, (SELECT SUM(sal+NVL(comm,0)) AS összjövedelem, job FROM emp GROUP BY job) allek WHERE dolgozó.job= allek.job ORDER BY jövedelem;