SUBINTEROGARI

Scopul lectiei

In aceasta lectie se vor studia caracteristici mai avansate ale instructiunii SELECT. Se pot scrie subinterogari in clauza WHERE a altei instructiuni SQL in scopul obtinerii de valori bazate pe o valoare necunoscuta a unei conditii. Aceasta lectie acopera atit subinterogarile single-row, cit si cele multiple-row. 

Folosirea unei subinterogari pentru a rezolva o problema

Sa presupunem ca doriti sa scrieti o interogare pentru a afla cine cistiga un salariu mai mare decit salariul lui Jones.

Pentru rezolvarea acestei probleme este nevoie de doua interogari: o interogare pentru a afla ce salariu cistiga Jones si o a doua pentru a determina cine cistiga mai mult decit aceasta suma.

Problema poate fi rezolvata combinind aceste doua interogari, integrind una din cereri in cealalta.

O cerere inclusa sau subinterogare returneaza o valoare folosita de catre interogarea exterioara sau principala. Folosirea unei subinterogari este echivalenta executarii a doua cereri secventiale si folosirii rezultatului primei cereri ca valoare de cautare pentru cea de a doua cerere.

Subinterogari

O subinterogare reprezinta o instructiune SELECT care este inclusa  intr-o clauza apartinind altei instructiuni SELECT. Se pot astfel construi instructiuni mai puternice pornind de la instructiuni simple prin utilizarea subinterogarilor. Acestea pot fi foarte folositoare in cazurile in care se doreste selectarea unor rinduri dintr-un tabel cu o conditie care depinde de datele din tabelul propriu-zis.

Subinterogarile pot fi plasate in urmatoarele clauze SQL:

WHERE;

HAVING;

FROM.

In sintaxa mai sus prezentata:

operator implica unul din urmatorii operatori de comparatie: >, = sau IN.

Nota: Operatorii de comparare se impart in doua clase:

operatori single-row : >, =, >=, <, <>, <=

operatori multiple-row : IN, ANY, ALL.

Subinterogarea este deseori referita ca fiind o instructiune SELECT inclusa, sub-SELECT sau instructiune SELECT interna. In general, subinterogarea se executa prima, iar rezultatul este folosit pentru a finaliza conditia de cerere pentru interogarea principala sau externa.

Utilizarea unei subinterogari

In figura anterioara, cererea interna determina salariul angajatului cu numarul 7566. Cererea externa preia rezultatul cererii interne si il foloseste pentru a afisa toti angajatii care cistiga un salariu mai amre decit aceasta suma.

Orientari in utilzarea subinterogarilor

O subinterogare trebuie sa fie inclusa intre paranteze.

O subinterogare trebuie sa apara in partea dreapta a unui operator de comparare.

Subinterogarile nu pot contine clauza ORDER BY. Pentru o instructiune SELECT poate exista doar o singura clauza ORDER BY, iar daca aceasta clauza este specificata, ea trebuie sa fie ultima clauza din instructiunea SELECT principala.

Subinterogarile folosesc doua clase de operatori de comparare: operatori single-row si operatori multiple-row.

Tipuri de subinterogari

Subinterogari single-row : cereri care returneaza doar un rind din instructiunea SELECT interna

Subinterogari multiple-row : cereri care returneaza mai mult de un rind din instructiunea SELECT interna

Subinterogari multiple-column : cereri care returneaza mai multe coloane din instructiunea SELECT interna.

Subinterogari single-row

O subinterogare single-row este acea subinterogare care returneaza un singur rind din instructiunea SELECT interna. Acest tip de subinterogare foloseste un operator single-row. Figura anterioara prezinta lista operatorilor single-row.

Exemplu:

Afisarea angajatilor care lucreaza pe acelasi post ca si angajatul ce are numarul 7369.

	SQL> SELECT ename, job FROM emp WHERE job= (SELECT job FROM emp WHERE empno=7369);

	ENAME JOB ------------ JAMES FUNCTIONAR (CLERK) SMITH FUNCTIONAR (CLERK) ADAMS FUNCTIONAR (CLERK) MILLER FUNCTIONAR (CLERK)

Executarea unei subinterogari single-row

O instructiune SELECT poate fi considerata ca un bloc de cereri. Exemplul de mai sus afiseaza angajatii al caror post este acelasi cu cel al angajatului cu numarul 7369 si a caror salariu este mai mare decit cel al angajatului 7876.

Exemplul este format din 3 blocuri de cereri: o cerere exterioara si doua cereri interne. Blocurile de cereri interne sint primele executate, producind rezultatele cererii: FUNCTIONAR (CLERK), respectiv 1100. Blocul exterior de cereri este apoi procesat si foloseste valorile returnate de catre cererile interne pentru a finaliza propriile conditii de cautare.

Ambele cereri interne returneaza valori singulare (FUNCTIONAR si 1100), astfel ca aceasta instructiune SQL este denumita o subinterogare single-row.

Nota: Interogarile exterioare si incluse pot prelua datele din tabele  diferite.

Utilizarea functiilor de grup intr-o subinterogare

In interogarea principala pot fi afisate date prin utilizarea unei functii de grup intr-o subinterogare care sa returneze un singur rind. Subinterogarea se va plasa intre paranteze si dupa operatorul de comparare.

Exemplul din figura precedenta afiseaza numele, postul si salariul tuturor angajatilor al caror salariu este egal cu salariul minim. Functia MIN (functie de grup) returneaza o singura valoare (si anume, 800), care este folosita de catre interogarea principala.

Clauza HAVING in subinterogari

Subinterogarile pot fi folosite nu numai in clauza WHERE, dar si in clauza HAVING. Server-ul de Oracle executa subinterogarea, returnind apoi rezultatul catre clauza HAVING a subinterogarii principale.

Instructiunea SQL prezentata in figura de mai sus are ca scop final afisarea tuturor departamentelor la nivelor carora salariul minim are o valoare mai mare decit valoarea salariului minim din cadrul departamentului 20.

	DEPTNO  MIN(SAL) ----- ----- ------ 950

Exemplu: Se cere sa se gaseasca postul avind cel mai scazut salariu mediu:

	SQL> SELECT job, AVG(sal) 2            FROM emp GROUP BY job HAVING AVG(sal) = (SELECT MIN(AVG(sal)) FROM emp GROUP BY job);

Erori ce pot apare la folosirea subinterogarilor

O eroare obisnuita la folosirea subinterogarilor o reprezinta returnarea a mai mult de un rind de catre o subinterogare dorita a fi de tip single-row.

In instructiunea SQL din exemplul anterior, subinterogarea contine o clauza GROUP BY - dupa numarul departamentului (deptno), care implica selectarea mai multor rinduri, cite unul pentru fiecare grup gasit. In acest caz, rezultatul subinterogarii va consta di valorile 800, 1300 si, respectiv 950.

Interogarea externa preia rezultatele subinterogarii (800, 1300, 950) si le foloseste in clauza WHERE. Clauza WHERE contine un operator egal ( = ), operator single-row de comparare, care asteapta o singura valoare de partea sa dreapta. Operatorul ' = ' nu poate accepta mai mult de o valoare primita de la subinterogare si astfel este generata eroarea.

Pentru a corecta eroarea, operatorul egal ( = ) trebuie inlocuit cu operatorul IN.

Probleme ce pot apare la utilizarea subinterogarilor

O problema obisnuita legata de subinterogari o constituie posibilitatea neselectarii nici unui rind de catre interogarea inclusa.

In ceea ce priveste instructiunea SQL de mai sus, subinterogarea contine o clauza WHERE (ename = 'SMYTHE'). Se presupune ca intentia este de a selecta angajatul cu numele Smythe. Instructiunea pare a fi corecta, dar la executie nu se selecteaza nici un rind.

Problema este ortografierea gresita a cuvintului Smythe. Nu exista nici un angajat cu numele de Smythe. Astfel, subinterogarea nu va selecta nici un rind. Interogarea externa preia rezultatul subinterogarii (nul, in acest caz) si foloseste acest rezultat in propria-i clauza WHERE. Interogarea externa nu gaseste nici un angajat avind cimpul referitor la post de valoare nula si astfel nu returneaza nici un rind.

Subinterogari multiple-row

Subinterogarile care returneaza mai mult de un rind se numesc subinterogari multiple-row. In cazul subinterogarilor multiple-row se folosesc operatori multiple-row, in locul celor single-row. Operatorul multiple-row necesita una sau mai multe valori.

	SQL> SELECT ename, sal, deptno FROM emp WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);

Exemplu:

Se cere sa se selecteze angajatii care cistiga un salariu egal cu salariul minim la nivel de departament.

Interogarea interna va fi prima executata producind ca raspuns la cerere trei rinduri: 800, 950, 1300. Blocul cererii externe este apoi procesat si foloseste valorile returnate de catre interogarea inclusa pentru a-si finaliza propria conditie de cautare. De fapt, interogarea principala este privita din perspectiva server-ului Oracle astfel:

	SQL> SELECT ename, sal, deptno FROM emp WHERE sal IN (800, 950, 1300);

Subinterogari multiple-row (continuare)

Operatorul ANY (si operatorul SOME, sinonim acestuia) compara o valoare cu fiecare valoare returnata de subinterogare. Exemplul de mai sus afiseaza angajatii ale caror salarii sint mai mici decit al oricarui functionar (clerk) si care nu sint functionari. Salariul maxim pa care il cistiga un functionar este 1300. Instructiunea SQL afiseaza toti angajatii care nu sint functionari, dar cistiga mai putin de

< ANY  inseamna mai mic decit maxim.

> ANY inseamna mai mare decit minim.

= ANY este echivalent cu operatorul IN.

Subinterogari multiple-row (continuare)

Operatorul ALL compara o valoare cu toate valorile returnate de o subinterogare. Exemplul de mai sus afiseaza toti angajatii ale caror salarii sint mai mari decit salariile medii la nivel de departamente.. Cel mai mare salariu mediu al vreunui departament este $2916.6667, asa ca interogarea va selecta acei angajati ale caror salarii sint mai mari decit $2916.6667.

> ALL  inseamna mai mare decit maxim

< ALL  inseamna mai mic decit minim

Operatorul NOT poate fi folosit cu operatorii IN, ANY si ALL.

Rezumat

O subinterogare este o instructiune SELECT inclusa intr-o clauza a altei instructiuni SQL. Subinterogarile sint folositoare atunci cind interogarea se bazeaza pe criterii necunoscute.

Subinterogarile au urmatoarele caracteristici:

Pot transmite un rind de date instructiunii principale care contine un operator single-row, precum: =, <>, >, >=, < sau <=;

Pot transmite rinduri multiple de date instructiunii  principale care contine un operator multiple-row, precum: IN, ANY sau ALL;

Sint primele procesate de catre server-ul Oracle, iar clauzele WHERE si HAVING folosesc rezultatele;

Pot contine functii de grup.

Privire generala asupra exercitiilor

In aceasta lucrare practica se vor scrie interogari complexe folosind instructiuni SELECT incluse.

Chestionare

Exista posibilitatea de a se dori ca, pentru inceput, sa fie creata  interogarea interna pentru intrebarile urmatoare. Fiti siguri ca ea ruleaza si produce datele anticipate inainte de a codifica interogarea principala.

Exercitii:

Sa se scrie o interogare care sa afiseze numele angajatului si data angajarii pentru toti angajatii din acelasi departament ca Blake. A se exclude Blake.

Sa se creeze o cerere pentru a afisa numarul angajatului si numele sau pemtru toti angajatii care cistiga mai mult decit salariul mediu. Sa se sorteze rezultatele in ordinea descrescatoare a salariului.

Sa se scrie o cerere care va afisa numarul si numele angajatului pentru toti cei care lucreaza intr-un departament care detine cel putin un angajat al carui nume contine litera ' T '. Salvati instructiunea intr-un fisier denumit p6q3.sql.

Afisati numele angajatului, numarul departamentului si postul pe care lucreaza acesta pentru toti angajatii al caror departament este situat in Dallas.

Afisati numele si salariul tuturor angajatilor subordonati lui King.

Afisati numarul departamentului, numele si postul tuturor angajatilor din

departamentul de vinzari (sales).

Modificati p6q3.sql pentru a afisa numarul, numele si salariul tuturor angajatilor care cistiga mai mult decit salariul mediu si, totodata, lucreaza intr-un departament care detine cel putin un angajat ce contine in numele sau litera ' T '. Salvati fisierul ca p6q7.sql. Reexecutati interogarea.