Sql Subcereri, Structuri Ierarhice

Limbajul SQL-Oracle Interogarea datelor. Subcereri. Subcerere (cerere imbricată) – comandă SELECT inclusă într-o altă comandă SQL, care poate returna una sau mai multe linii.

Subcererile sunt utilizate pentru:  a obţine rezultate pe baza unor criterii, care apoi să fie utilizate pentru identificarea altor răspunsuri  a crea tabele pe baza unora deja existente  a manipula seturi mari de date, prin includerea în comenzi INSERT, UPDATE sau DELETE  a crea tabele virtuale (vederi, views)  a defini vederi dinamice (subcereri incluse în clauza FROM)

 a defini expresii calculate dinamic prin subcereri scalare 1

Limbajul SQL-Oracle Interogarea datelor. Subcereri. Tipuri de subcereri: Subcereri

Descriere

 subcereri single-row

returnează o singură linie (conţinând valori pentru una sau mai multe coloane)

 subcereri multiple-row

returnează 0, 1 sau mai multe linii

 subcereri multiple-column

returnează mai mult de o coloană ca rezultat al unei subcereri single-row sau multiple-row

 subcereri corelate

subcereri de oricare dintre tipurile anterioare, care asigură legătura dintre tabele, prin referirea de coloane ale cererii părinte

 subcereri scalare

returnează o singură coloană rezultat; pot fi utilizate ca orice expresie care apare într-o comandă SQL 2

Limbajul SQL-Oracle Interogarea datelor. Subcereri. SELECT id_angajat, nume FROM angajati WHERE id_functie = (SELECT id_functie FROM angajati WHERE UPPER(nume)='ABEL') AND UPPER(nume)!='ABEL';

3

Limbajul SQL-Oracle Interogarea datelor. Subcereri. SELECT id_angajat, nume FROM angajati WHERE id_functie = (SELECT id_functie FROM angajati WHERE UPPER(nume)='KING') AND UPPER(nume)!='KING'; ORA-01427: single-row subquery returns more than one row

SELECT id_angajat, nume FROM angajati WHERE id_functie IN (SELECT id_functie FROM angajati WHERE UPPER(nume)='KING') AND UPPER(nume)!='KING'; 4

Limbajul SQL-Oracle Interogarea datelor. Subcereri. Operatori de comparaţie utilizaţi în cadrul subcererilor multiple-row: Operatori

Descriere

IN

• compară cu o listă de valori • verifică dacă valoarea căutată se regăseşte în listă

NOT

• utilizat cu operatorul IN

ANY

• utilizat în combinaţie cu operatorii de comparaţie (=, <) • verifică dacă valoarea căutată îndeplineşte condiţia de comparaţie cu oricare dintre liniile returnate de subcerere

SOME

• la fel ca operatorul ANY

ALL

• utilizat în combinaţie cu operatorii de comparaţie (=, <) • verifică dacă valoarea căutată îndeplineşte condiţia de comparaţie cu toate liniile returnate de subcerere 5

Limbajul SQL-Oracle Interogarea datelor. Subcereri. UPDATE angajati SET salariul=1.1*salariul WHERE (id_functie, id_departament) = (SELECT id_functie, id_departament FROM angajati WHERE UPPER(nume)='ABEL');

6

Limbajul SQL-Oracle Interogarea datelor. Subcereri. SELECT (SELECT COUNT(id_client) FROM clienti) "Toti clientii", (SELECT COUNT(id_client) FROM comenzi) "Clientii cu comenzi“ FROM DUAL;

Toti clientii Clientii cu comenzi ------------- ------------------319

105

7

Limbajul SQL-Oracle Interogarea datelor. Subcereri. UPDATE angajati SET salariul=1.1 * salariul WHERE id_angajat IN (SELECT id_angajat FROM comenzi); UPDATE angajati a SET a.salariul=1.1 * a.salariul WHERE EXISTS (SELECT * FROM comenzi WHERE id_angajat=a.id_angajat); 8

Limbajul SQL-Oracle Interogarea datelor. Subcereri. SELECT c.nume_client FROM clienti c WHERE EXISTS (SELECT * FROM comenzi co WHERE co.id_client=c.id_client);

SEMI-JOIN

SELECT c.nume_client FROM clienti c WHERE NOT EXISTS (SELECT * FROM comenzi co WHERE co.id_client=c.id_client); 9

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice.

ALTER TABLE angajati ADD CONSTRAINT ang_manager_fk FOREIGN KEY (id_manager) REFERENCES angajati (id_angajat); 10

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice. Nod rădăcină

Nivel 1

Nivel 2

Nivel 3

Nivel 4

Nod

Nod frunză 11

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice. Clauze în SELECT

Descriere

START WITH

identifică nodul rădăcină

CONNECT BY

defineşte legăturile de tip self-join, precum şi direcţia în care va fi traversată ierarhia

ORDER SIBLINGS BY

sortează liniile din cadrul fiecărui nivel ierarhic

Alte concepte utilizate: PRIOR - utilizat în cadrul CONNECT BY pentru stabilirea direcţiei în care va fi traversată ierarhia LEVEL - pseudo-coloană care identifică valoarea corespunzătoare nivelului din ierarhie (LEVEL 1 corespunde nodului rădăcină, LEVEL 2 indică nivelul copil relativ la rădăcină etc.) SYS_CONNECT_BY_PATH – funcţie care afişează calea către orice nod, pornind de ka nodul rădăcină CONNECT_BY_ROOT – operator care referă datele din nodul rădăcină

12

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice. SELECT LEVEL, id_angajat, nume FROM angajati START WITH id_angajat=100 CONNECT BY id_manager=PRIOR id_angajat;

LEVEL ID_ANGAJAT NUME ---------- ---------- --------1 100 King 2 101 Kochhar 3 108 Greenberg 4 109 Faviet 4 110 Chen 4 111 Sciarra 4 112 Urman 4 113 Popp 3 200 Whalen 3 203 Mavris 3 204 Baer 3 205 Higgins 4 206 Gietz 2 102 De Haan 3 103 Hunold 4 104 Ernst 4 105 Austin 4 106 Pataballa 4 107 Lorentz

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice. LEVEL ID_ANGAJAT ANGAJAT ---------- ---------- ------------------SELECT LEVEL, id_angajat, 1 100 King 2 101 Kochhar LPAD(' ',LEVEL)||nume angajat 3 108 Greenberg 4 109 Faviet FROM angajati 4 110 Chen START WITH id_angajat=100 4 111 Sciarra 4 112 Urman CONNECT BY 4 113 Popp 3 200 Whalen id_manager=PRIOR id_angajat; 3 203 Mavris 3 204 Baer 3 205 Higgins 4 206 Gietz 2 102 De Haan 3 103 Hunold 4 104 Ernst 4 105 Austin 4 106 Pataballa 4 107 Lorentz

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice. SELECT LEVEL, id_angajat, LPAD(' ',LEVEL)||nume angajat FROM angajati START WITH id_angajat=110 CONNECT BY PRIOR id_manager = id_angajat; LEVEL ID_ANGAJAT ANGAJAT ---------- ---------- ---------1 110 Chen 2 108 Greenberg 3 101 Kochhar 4 100 King

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice. LEVEL ID_ANGAJAT ANGAJAT ---------- ---------- ---------------SELECT LEVEL, id_angajat, 1 100 King 2 148 Cambrault LPAD(' ',LEVEL)||nume angajat 3 172 Bates 3 169 Bloom FROM angajati 3 170 Fox START WITH id_angajat=100 3 173 Kumar 3 168 Ozer CONNECT BY 3 171 Smith 2 102 De Haan id_manager=PRIOR id_angajat 3 103 Hunold ORDER SIBLINGS BY nume; 4 105 Austin 4 104 Ernst 4 107 Lorentz 4 106 Pataballa 2 147 Errazuriz 3 166 Ande 3 167 Banda 3 163 Greene 3 165 Lee 3 164 Marvins

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice.

LEVEL ID_ANGAJAT ANGAJAT ------ ---------- -------------------------------1 100 /King SELECT LEVEL, id_angajat, 2 101 /King/Kochhar SYS_CONNECT_BY_PATH(nume,'/') 3 108 /King/Kochhar/Greenberg 4 109 /King/Kochhar/Greenberg/Faviet angajat 4 110 /King/Kochhar/Greenberg/Chen 4 111 /King/Kochhar/Greenberg/Sciarra FROM angajati 4 112 /King/Kochhar/Greenberg/Urman START WITH id_angajat=100 4 113 /King/Kochhar/Greenberg/Popp 3 200 /King/Kochhar/Whalen CONNECT BY 3 203 /King/Kochhar/Mavris 204 /King/Kochhar/Baer id_manager=PRIOR id_angajat; 3 3 205 /King/Kochhar/Higgins 4 206 /King/Kochhar/Higgins/Gietz 2 102 /King/De Haan 3 103 /King/De Haan/Hunold 4 104 /King/De Haan/Hunold/Ernst 4 105 /King/De Haan/Hunold/Austin 4 106 /King/De Haan/Hunold/Pataballa 4 107 /King/De Haan/Hunold/Lorentz

Limbajul SQL-Oracle Interogarea datelor. Cereri ierarhice. LEVEL ID_ANGAJAT ANGAJAT ---------- ----------------100 King 102 De Haan 103 Hunold 104 Ernst 105 Austin 106 Pataballa 107 Lorentz 114 Raphaely 115 Khoo 116 Baida 3 117 Tobias 3 118 Himuro 3 119 Colmenares

SELECT LEVEL, id_angajat, ---------1 LPAD(' ',LEVEL)||nume angajat 2 FROM angajati 3 4 START WITH id_angajat=100 4 4 CONNECT BY 4 id_manager=PRIOR id_angajat 2 3 AND id_angajat!=101; 3

Rezolvaţi! Care dintre următoarele opţiuni indică o listă a clauzelor comenzii SELECT într-o ordine de apariţie corectă: a) b) c) d) e)

SELECT, FROM, START WITH, CONNECT BY, WHERE SELECT, FROM, WHERE, CONNECT BY, START WITH SELECT, FROM, START WITH, WHERE, CONNECT BY SELECT, FROM, WHERE, START WITH, CONNECT BY nicio variantă corectă

19

Rezolvaţi! Care clauză a comenzii SELECT este utilizată pentru a exclude o ramură întreagă dintr-o structură ierarhică: a) b) c) d) e)

START WITH CONNECT BY WHERE HAVING nicio variantă corectă

20

Limbajul SQL-Oracle Actualizarea datelor Comandă

Descriere

INSERT

adaugă o înregistrare nouă într-o tabelă

UPDATE

modifică valori asociate coloanelor unei tabele

MERGE

realizează fie modificări ale datelor, fie adăugări de înregistrări dintr-o altă tabelă, în funcţie de o condiţie de potrivire

DELETE

şterge înregistrări dintr-o tabelă

21

Limbajul SQL-Oracle Actualizarea datelor. Comanda MERGE SELECT id_angajat, nume, salariul, id_functie FROM angajati_noi ORDER BY id_angajat; ID_ANGAJAT ---------99 103 104 105 106 120 121 122 123 124

NUME SALARIUL ID_FUNCTIE ------------------------- ---------- ---------Loren 4000,00 AD_PRES Hunold 9500,00 AD_PRES Ernst 6000,00 IT_PROG Austin 7500,00 IT_PROG Pataballa 4800,00 IT_PROG Weiss 8000,00 ST_MAN Fripp 8200,00 ST_MAN Kaufling 7900,00 ST_MAN Vollman 6500,00 ST_MAN Mourgos 5800,00 ST_MAN

22

Limbajul SQL-Oracle Actualizarea datelor. Comanda MERGE MERGE INTO angajati a USING angajati_noi b ON (a.id_angajat=b.id_angajat) WHEN MATCHED THEN UPDATE SET a.salariul=b.salariul, a.id_functie=b.id_functie WHEN NOT MATCHED THEN INSERT (a.id_angajat,a.prenume,a.nume,a.email,a.data_angajare, a.id_functie,a.salariul,a.id_departament) VALUES (b.id_angajat,b.prenume,b.nume,b.email,b.data_angajare, 23 b.id_functie,b.salariul,b.id_departament);

Limbajul SQL-Oracle Actualizarea datelor. Comanda MERGE SELECT id_angajat, nume, salariul, id_functie FROM angajati ORDER BY id_angajat; ID_ANGAJAT ---------99 100 101 102 103 104 105 106 107 108

NUME SALARIUL ID_FUNCTIE ------------------------- ---------- ---------Loren 4000,00 AD_PRES King 24000,00 AD_PRES Kochhar 17000,00 AD_VP De Haan 17000,00 AD_VP Hunold 9500,00 AD_PRES Ernst 6000,00 IT_PROG Austin 7500,00 IT_PROG Pataballa 4800,00 IT_PROG Lorentz 4200,00 IT_PROG Greenberg 12000,00 FI_MGR

24

CURSUL 11... 

Gestiunea altor obiecte ale bazei de date