Baze de Date An II SEM II Lect. Univ. Dr. Gabriela Mihai

1

LABORATOR 5 SQL

Clauza WITH. Subcereri. Operatori. Cereri cu sincronizare (corelate). Cereri ierarhice

OPERATORUL BOOLEAN EXISTS

Operatorul boolean EXISTS aplicat unei subcereri ntoarce valoarea true dac subcererea ntoarce cel

puin o linie rezultat i valoarea false n caz contrar.

SELECT t1.coloana1, t1.coloana2, ...

FROM tabel1 t1

WHERE [NOT] EXISTS (SELECT 'X'

FROM tabel2 [t2]

WHERE [t2.]expresie = t1.expresie);

Avantajul utilizrii operatorului EXISTS este c odat ce subcererea ntoarce o linie rezultat,

evaluarea acesteia este oprit. Deci, operatorul EXISTS este utilizat atunci cnd ne intereseaz numai

existena unor linii corespondente n subcerere.

1. Determinai numele i codul angajailor care ctig mai mult dect angajatul avnd codul 200.

Varianta 1 - Forma relaional

SELECT a.employee_id, a.last_name

FROM employees a, employees b

WHERE a.salary > b.salary

AND b.employee_id = 200;

Varianta 2 - Forma procedural

SELECT employee_id, last_name

FROM employees e

WHERE EXISTS (SELECT 1

FROM employees

WHERE employee_id = 200

AND e.salary >salary);

2. Dai o alt metod de rezolvare pentru problema anterioar, utiliznd subcereri i operatorul >.

3. Folosind operatorul EXISTS determinai numele departamentelor n care lucreaz cel puin un angajat.

SELECT department_id, department_name

FROM departments d

WHERE EXISTS (SELECT 'x'

FROM employees

WHERE department_id = d.department_id);

Baze de Date An II SEM II Lect. Univ. Dr. Gabriela Mihai

2

4. Dai o alt metod de rezolvare pentru problema anterioar, utiliznd subcereri i operatorul IN.

5. Folosind operatorul EXISTS determinai codul i numele departamentelor n care nu lucreaz nimeni.

6. Afiai codul locaiei i oraul n care nu funcioneaz departamente, utiliznd:

a) NOT IN;

b) MINUS;

c) NOT EXISTS;

d) Outer Join.

7. Determinai numele angajailor care au lucrat cel puin la aceleai proiecte ca i angajatul avnd codul

202 (au lucrat la toate proiectele la care a lucrat angajatul 202 i eventual la alte proiecte).

Observaie: A B A \ B =

8. Determinai numele angajailor care au lucrat cel mult la aceleai proiecte ca i angajatul avnd codul

202.

9. Determinai numele angajailor care au lucrat exact la aceleai proiecte ca i angajatul avnd codul 202.

Observaie: A = B A \ B = i B \ A =

OPERATORUL DIVISION

10. Afiai codul i numele proiectelor la care au lucrat toi angajaii din departamentul 20.

Varianta 1

SELECT p.project_id, project_name

FROM projects p, work w

WHERE p.project_id=w.project_id

AND employee_id IN (SELECT employee_id

FROM employees

WHERE department_id =20)

GROUP BY p.project_id, project_name

HAVING COUNT(*)=(SELECT COUNT(*)

FROM employees

WHERE department_id =20);

Varianta 2

SELECT DISTINCT p.project_id, project_name

FROM projects p, work w

WHERE p.project_id=w.project_id

AND NOT EXISTS (SELECT 'X'

FROM employees e

Baze de Date An II SEM II Lect. Univ. Dr. Gabriela Mihai

3

WHERE department_id=20

AND NOT EXISTS (SELECT 'X'

FROM work w1

WHERE e.employee_id=w1.employee_id

AND w.project_id=w1.project_id));

11. Afiai codul angajailor care au lucrat la toate proiectele care au nceput n anul 1999.

CERERI IERARHICE

Clauzele START WITH i CONNECT BY se utilizeaz n formularea cererilor ierarhice.

START WITH specific o condiie care identific liniile ce urmeaz s fie considerate ca

rdcini ale cererii ierarhice respective. Dac se omite aceast clauz, sistemul Oracle

utilizeaz toate liniile din tabel drept linii rdcin.

Clauza CONNECT BY specific o condiie care identific relaia dintre liniile printe i

copil ale ierarhiei. Condiia trebuie s conin operatorul PRIOR pentru a face referin la

linia printe.

Interogrile ierarhice permit regsirea datelor pe baza unei relaii ierarhice care exist ntre liniile

tabelului. Relaia printe-copil a unei structuri arborescente permite controlul direciei n care este

parcurs ierarhia i stabilirea rdcinii ierarhiei.

Pseudocoloana LEVEL poate fi util ntr-o cerere ierarhic. Aceasta determin lungimea drumului de

la rdcin la un nod.

Operatorul PRIOR face referin la linia printe. Plasarea acestui operator determin direcia

interogrii, dinspre printe spre copil (top-down) sau invers (bottom-up)

CONNECT BY PRIOR cheie_parinte = cheie_copil (top-down);

CONNECT BY cheie_copil = PRIOR cheie_parinte (bottom-up);

Liniile printe ale interogrii sunt identificate prin clauza START WITH. Pentru a gsi liniile

copil, server-ul evalueaz expresia din dreptul operatorului PRIOR pentru linia printe, i cealalt

expresie pentru fiecare linie a tabelului. nregistrrile pentru care condiia este adevrat vor fi liniile

copil. Spre deosebire de START WITH, n clauza CONNECT BY nu pot fi utilizate subcereri.

n tabelul employees, se poate imagina o structur arborescent pe baza managerilor (coloana

manager_id). Un angajat are un manager, la rndul su managerul are i el un manager etc.

12. Obinei ierarhia subaltern-ef. Se vor afia codul i numele angajatului nsoit de codul managerului su.

De asemenea, se va afia i nivelul din ierarhie.

SELECT LEVEL, employee_id, last_name, manager_id

FROM employees

CONNECT BY PRIOR manager_id = employee_id;

Baze de Date An II SEM II Lect. Univ. Dr. Gabriela Mihai

4

LEVEL EMPLOYEE_ID LAST_NAME MANAGER_ID

---------------------------------------------------

1 100 King

---------------------------------------------------

1 101 Kochhar 100

2 100 King

---------------------------------------------------

1 102 De Haan 100

2 100 King

---------------------------------------------------

1 103 Hunold 102

2 102 De Haan 100

3 100 King

---------------------------------------------------

....

13. Obinei ierarhia subaltern-ef, considernd ca rdcin angajatul avnd codul 103. Se vor afia codul i

numele angajatului nsoit de codul managerului su. De asemenea, se va afia i nivelul din ierarhie.

Observaie: n relaia ierarhic printe este coloana manager_id.

SELECT LEVEL, employee_id, last_name, manager_id

FROM employees

START WITH employee_id =103

CONNECT BY PRIOR manager_id = employee_id;

LEVEL EMPLOYEE_ID LAST_NAME MANAGER_ID

---------------------------------------------------

1 103 Hunold 102

2 102 De Haan 100

3 100 King

14. Obinei ierarhia ef-subaltern, considernd ca rdcin angajatul avnd codul 103. Se vor afia codul i

numele angajatului nsoit de codul managerului su.

Observaie: n relaia ierarhic printe este coloana employee_id.

15. Obinei ierarhia subaltern-ef, considernd ca rdcin angajatul al crui salariu este minim. Se vor afia

codul i numele angajatului nsoit de codul managerului su.

16. Afiai ierarhia subaltern-ef, considernd ca rdcin angajatul al crui cod este 206. S se afieze

codul, numele i salariul angajatului nsoit de codul managerului su, pentru angajaii al cror salariu

este mai mare dect 15000.

Verificai i comentai rezultatul obinut n cazul n care:

a) condiia salary > 15000 apare n clauza WHERE;

b) condiia salary > 15000 apare n clauza CONNECT BY.

Verificai i comentai rezultatul obinut n cazul n care condiia impus ar fi fost salary < 15000.

Baze de Date An II SEM II Lect. Univ. Dr. Gabriela Mihai

5

17. Afiai codul, numele, data angajrii, salariul i managerul pentru:

a) subalternii direci ai lui De Haan;

b) eful direct al lui De Haan;

c) ierarhia ef-subaltern care ncepe de la De Haan;

d) angajaii condui de subalternii lui De Haan;

e) ierarhia subaltern-ef pentru Hunold;

f) superiorul efului direct al lui Hunold.

18. Ci efi pe linie ierarhic are angajatul 107?

19. Obinei ierarhia subaltern-ef pentru toi managerii de departamente.

20. Pentru fiecare angajat determinai nivelul su ierarhic n companie.

21. Obinei nivelul ierarhic n companie al efilor de departamente.

22. Pentru angajatul avnd codul 206, afiai o structur arborescent n care va aprea angajatul, eful su,

superiorul efului su etc. Coloanele afiate vor fi: codul angajatului, codul managerului, nivelul n

ierarhie (LEVEL) i numele angajatului.

SET LINESIZE 100

COLUMN nume FORMAT a25;

SELECT employee_id, manager_id, LEVEL, last_name,

LPAD(last_name, length(last_name)+level*2-2, '_') nume

FROM employees

START WITH employee_id=206

CONNECT BY employee_id=PRIOR manager_id;

23. Creai un raport din care s reias structura ierarhic ef-subordonat.

SET LINESIZE 100

SET PAGESIZE 100

COLUMN nume FORMAT a25;

SELECT LPAD(' ', 3*LEVEL-3)||last_name nume, LEVEL,

employee_id, manager_id, department_id

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id= manager_id;

24. a) Modificai cererea anterioar astfel nct din rezultat s fie exclus angajatul De Haan, dar nu i

subordonaii si.

Observaie: Pentru aceasta se include condiia ntr-o clauz WHERE.

Baze de Date An II SEM II Lect. Univ. Dr. Gabriela Mihai

6

b) Modificai cererea anterioar astfel nct din rezultat s fie exclus angajatul De Haan mpreun cu

subordonaii si.

Observaie: Pentru aceasta se include condiia n clauza CONNECT BY.

25. Afiai ierarhia ef-subaltern: codul, prenumele i numele (pe aceeai coloan), codul job-ului i data

angajrii, pornind de la subordonaii direci ai lui Steven King care au cea mai mare vechime. Rezultatul

nu va conine angajaii n anul 1970.

WITH emp_sk AS

(SELECT employee_id, hire_date

FROM employees

WHERE manager_id = (SELECT employee_id

FROM employees

WHERE INITCAP(last_name) = 'King'

AND INITCAP(first_name) = 'Steven'))

SELECT employee_id, INITCAP(first_name) || ||UPPER(last_name),

job_id, hire_date,manager_id

FROM employees

WHERE TO_CHAR(hire_date, 'yyyy') != 1970

START WITH employee_id IN

(SELECT employee_id

FROM emp_sk

WHERE hire_date = (SELECT MIN(hire_date)

FROM emp_sk))

CONNECT BY PRIOR employee_id = manager_id;

26. Afiai textul true dac n tabelul job_grades minimul marginii superioare a grilelor de salarizare este

mai mic dect 10000. Este necesar utilizarea clauzei GROUP BY?

SELECT 'true'

FROM job_grades

HAVING MIN(highest_sal)