Laborator SQL 5 - partea 2.pdf
-
Upload
bogdan-mihai-tabacu -
Category
Documents
-
view
33 -
download
1
Transcript of Laborator SQL 5 - partea 2.pdf
-
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)