Subinterogarile (subqueries)

O subinterogare este o interogare ale carei rezultate sunt transmise ca argumente unei alte interogari.

Utilizarea unei subinterogari va permite rezolvarea unei probleme, ca de exemplu: 'Cine are salariul mai mare decat salariul lui Bob'. Pentru rezolvarea acestei probleme este nevoie de doua interogari, una pentru a gasi salariul lui Bob si a doua pentru a gasi cine are salariul mai mare decat salariul lui Bob. Putem rezolva aceasta problema combinand doua interogari, plasand o interogare in interiorul alteia. Interogarea din interior numita si subinterogare returneaza o valoare care este utilizata de interogarea din exterior.

Explicat mai simplu, o subinterogare va permite sa transmitem setul de rezultate al unei interogari la alta. Sintaxa generala este urmatoarea:

SELECT coloane

FROM tabela

WHERE expr operator

(SELECT coloane

FROM tabela);

unde:

operator: include operatorii de comparatie.

Operatorii de comparatie sunt de doua tipuri:

operatori care returneaza un singur rand (single-row): >, >=, <, <=, <>.

Operatori care returneaza mai multe randuri (multiple-row): IN, ANY si ALL.

Cateva observatii referitoare la utilizarea subinterogarilor:

O subinterogare trebuie inclusa intre paranteze.

Scrierea unei subinterogari se va face la dreapta operatorului de comparatie.

Nu putem folosi o clauza ORDER BY intr-o subinterogare.

Doua tipuri de operatori de comparatie pot fi utilizati in subinterogari: operatori care returneaza un singur rand (single-row) si operatori care returneaza mai multe randuri (multiple-row).

Exista 3 tipuri de subinterogari:

Subinterogari care returneaza un singur rand (single-row subqueries).

Subinterogari care returneaza mai multe randuri (multiple-row subqueries).

Subinterogari care returneaza mai multe coloane (multiple-column subqueries).

Putem scrie o subinterogare in clauze SQL cum ar fi:

Clauza WHERE

Clauza HAVING

Clauza FROM

In continuare, vom exemplifica prin cateva exemple aceste tipuri de subinterogari.

Subinterogari care returneaza un singur rand (single-row subqueries).

Sa se afiseze toti angajati care au aceeasi functie (job) ca si angajatul cu numele POP.

SELECT ename, job

FROM emp

WHERE job =

(SELECT job

FROM emp

WHERE ename ='POP');

Sa se afiseze departamentele (deptno) care au salariul minim pe departament mai mare decat salariul minim pe departamentul 20.

SELECT deptno, MIN(sal)

FROM emp

GROUP BY deptno

HAVING MIN(sal) >

(SELECT MIN(sal)

FROM emp

WHERE deptno =20);

Subinterogari care returneaza mai multe randuri (multiple-row subqueries)

Acestea utilizeaza operatorii multiple-row: IN, ANY si ALL

IN - verifica daca valoarea exista in lista rezultata din subinterogare.

ANY - verifica daca conditia este indeplinita pentru cel putin una din elementele listei rezultate din subinterogare.

ALL - verifica daca conditia este indeplinita pentru toate elementele listei rezultate din subinterogare.

Afisarea tuturor angajatilor care au salariul egal cu salariul minim pe departament, se va realiza in urmatoarea secventa:

SELECT ename, sal, deptno

FROM emp

WHERE sal IN (SELECT MIN(sal)

FROM emp

GROUP BY deptno);

Afisarea tuturor angajatilor care au salariul mai mic decat salariul angajatiolor din departamentul 'Financiar', excluzand angajati din departamentul 'Financiar', se realizeaza prin urmatoarea secventa:

SELECT ename, job, sal

FROM emp

WHERE sal <ANY

(SELECT sal

FROM emp

WHERE job ='Financiar')

AND job<> 'Financiar';

OBS: Operatorul ANY este sinonim cu SOME.

Presupunem ca subinterogarea (subquery) va returna patru valori: 1300, 1000, 800, 950. Query principal va compara salariul cu valoarea maxima returnata de subquery.

< ANY va compara cu valoarea maxima returnata de subinterogare, deci va returna valori mai mici decat valoarea maxima.

>ANY va compara cu valoarea minima returnata de subinterogare, deci va returna valori mai mari decat valoarea minima.

=ANY este echivalent cu IN.

Afisarea tuturor angajatilor care au salariul mai mare decat media aritmetica a salariului pe departamente, se realizeaza prin urmatoarea secventa:

SELECT ename, job, sal

FROM emp

WHERE sal >ALL

(SELECT AVG(sal)

FROM emp

GROUP BY deptno);

Presupunem ca subinterogarea (subquery) va returna trei valori: 2900, 2400, 1740. Query principal va compara salariul cu valoarea maxima returnata de subquery, 2900.

< ALL va compara cu valoarea minima returnata de subinterogare, deci va returna valori mai mici decat valoarea minima.

>ANY va compara cu valoarea maxima returnata de subinterogare, deci va returna valori mai mari decat valoarea maxima.

OBS: Operatorul NOT nu poate fi utilizat cu operatorii IN, ANY si ALL.

Subinterogari care returneaza mai multe coloane (multiple-column subqueries)

Pentru a compara doua sau mai multe coloane trebuie sa compunem doua sau mai multe clauze WHERE folosind operatorii logici. Subinterogarile care returneaza mai multe coloane permit combinarea mai multor clauze WHERE intr-una singura.

Afisarea tuturor angajatilor care au acelasi salariu si comision cu cel al angajatilor din departamentul 30, se realizeaza prin urmatoarea secventa:

SELECT ename, job, sal, comm, deptno

FROM emp

WHERE (sal, NVL(comm, -1)) IN

(SELECT sal, NVL(comm, -1)

FROM emp

WHERE deptno = 30);

Comparatiile intr-o subinterogare care returneaza mai multe coloane, pot fi comparatii pereche sau nonpereche. In exemplu de mai sus, fiecare rand candidat din instructiunea SELECT trebuie sa aibe acelasi salariu si comision (valorile sunt comparate simulatn) ca si un angajat din departamentul 30, deci avem comparatii pereche. Daca dorim sa folosim comparatii nonpereche atunci trebuie sa utilizam clauze WHERE cu multiple conditii.

Rescriem exemplu de mai sus folosind comparatii nonpereche.

SELECT ename, job, sal, comm, deptno

FROM emp

WHERE sal IN (SELECT sal

FROM emp

WHERE deptno = 30)

AND NVL(comm, -1) IN

(SELECT NVL(comm, -1)

FROM emp

WHERE deptno = 30);

OBS: In acest exemplu nu sunt comparate ambele valori (sal, comm) simultan, ci se verifica sal, dupa care se verifica comm. Deci, pot exista persoane care au acelasi salariu cu a unei persoane din departamenul 30 dar comisionul diferit, dar egal cu o altei persoane din departamentul 30.

Tema practica

Folosind tabelele create in lucrarea 1 si anume DEPT, EMP si SALGRADE sa se scrie comenzile de interogare SELECT pentru urmatoarele cerinte:

a)     Afisarea tuturor angajatilor care nu au subordonati.

b)     Afisarea tuturor angajatilor care au salariul egal cu salariul minim pe departamentul lor.

c)     Afisarea tuturor angajatilor care au salariul egal cu salariul maxim pe departamentul lor.

d)     Afisarea tuturor angajatilor care au subordonati.

e)     Sa se afiseze numarul de angajati din fiecare departament ( denumire departament, nr. angajati)

f)      Sa se afiseze departamentul cu cel mai mare numar de angajati.