TIPURI DE INTEROGARI(SIMPLE SI MULTIPLE) - ORACLE

TIPURI DE INTEROGARI(SIMPLE SI MULTIPLE) - ORACLE

Interogari simple

Acest capitol realizeaza o introducere in limbajul de interogare utilizat pentru a accesa o baza de date Oracle. Multimea comenzilor SQL sunt conturate ca blocuri de interogare de baza.In particular, noi discutam declaratiile SQL folosite la:

• executia calculelor
• specificarea alternativa a capetelor de coloana
• concatenarea coloanelor
• sortarea rindurilor
• introducerea criteriilor de cautare.

Fiecare din urmatoarele declaratii sunt valide:

SELECT * FROM EMP;

SELECT

*

FROM

EMP

;

SELECT *

FROM EMP;

In acest manual comenzile SQL sunt sparte in clauze pentru claritate.

Blocul de interogare de baza

Declaratia SELECT regaseste informatia din baza de date implementand toti operatorii din algebra relationala .

In cele mai simple forme trebuie sa contina:

1. O clauza SELECT ,care listeaza coloanele pentru afisare astfel incat este esentiala o Proiectie.
2. O clauza FROM care specifica tabela implicata.

Pentru a lista toate numerele departamentelor,numele angajatilor si

numarul managerilor in tabela EMP introduceti urmatoarele:

SELECT DEPTNO,ENAME,MGR

FROM EMP;

DEPTNO ENAME MGR

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

20 SMITH 7902

30 ALLEN 7698

30 WARD 7698

20 JONES 7839

30 MARTIN 7698

30 BLAKE 7839

10 CLARK 7839

20 SCOTT 7566

10 KING

30 TURNER 7698

20 ADAMS 7788

30 JAMES 7698

20 FORD 7566

10 MILLER 7782

De remarcat ca numele coloanelor sunt separate prin spatiu.

Este posibil sa selectam toate coloanele din tabela prin specificarea

unui asterix ('*') dupa cuvantul SELECT .

SELECT *

FROM EMP;

Alte elemente in clauza SELECT

Este posibil sa se includa si alte elemente in clauza SELECT.

• Expresii aritmetice
• Alias-uri de coloane
• Coloane concatenate
• Literali

Toate aceste optiuni ajuta utilizatorul sa ceara date si sa le manipuleze in functie de scopurile interogarii ; de exemplu,executia calculelor,legarea coloanelor impreuna,sau afisarea sirurilor de litere din text.

Expresii aritmetice

O expresie este o combinatie de una sau mai multe valori,operatori si functii care sa evalueaza la o valoare.

Expresiile aritmetice pot contine nume de coloane ,valori numerice constante si operatori aritmetici:

Operatori Descriere

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

+ adunare

- scadere

* inmultire

/ impartire

SELECT ENAME, SAL*12, COMM

FROM EMP;

Daca expresia aritmetica contine mai mult decat un operator, prioritatile sunt *,/,la inceput,apoi +,- (de la stanga la dreapta pentru operatorii de aceeasi prioritate).

In urmatorul exemplu,inmultirea (250*12) este evaluata prima;apoi valoarea salariului este adunata la rezultatul multiplicarii(3000). Astfel pentru randul lui SMITH avem :800+3000=3800.

SELECT ENAME,SAL + 250 * 12

FROM EMP;

Parantezele pot fi utilizate pantru specificarea ordinii de executie a operatorilor ,daca,de exemplu ,adunarea e dorita inainte de inmultire:

SELECT ENAME,(SAL + 250) * 12

FROM EMP;

Aliasuri de coloana

Cand se afiseaza rezultatul unei interogari,SQL*Plus in mod normal utilizeaza numele coloanelor selectate ca titlu.In multe cazuri acest nume poate fi criptic sau fara inteles.

Puteti schimba un titlu de coloana utilizand un 'ALIAS'.

Un alias de coloana da unei coloane un nume de titlu alternativ la iesire.

Specificati aliasul dupa coloana in lista selectata.Prin default, titlurile alias vor fi fortate la litere mari si nu pot contine blankuri,numai daca aliasul este inclus intre ghilimele(' ').

Pentru a afisa titlul de coloana ANNSAL pentru salariul anual insemnand SAL*12,utilizati un alias de coloana:

SELECT ENAME,SAL*12 ANNSAL,COMM

FROM EMP;

Odata definit ,un alias poate fi utilizat de comenzile SQL care sunt tratate in capitolele 10 si 11.

Nota:

Intr-o declaratie SQL ,un alis de coloana poate fi utilizat numai in clauza SELECT.

Operatorul de concatenare

Operatorul de concatenare (||) permite coloanelor sa fie legate cu alte coloane,expresiilor aritmetice sau valorilor constante sa creeze o expresie de caractere.

Coloanele din cealalta parte a operatorului sunt combinate pentru a obtine o singura coloana.

Pentru a combina EMPNO si ENAME si sa se dea aliasul EMPLOYEE expresiei, introduceti:

SELECT EMPNO||ENAME EMPLOYEE

FROM EMP;

EMPLOYEE

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

7369SMITH

7499ALLEN

7521WARD

7566JONES

7654MARTIN

7698BLAKE

7782CLARK

7788SCOTT

7839KING

7844TURNER

7876ADAMS

7900JAMES

7902FORD

7934MILLER

Literali

Un literal este orice caracter ,expresie ,numar inclus in lista lui SELECT care nu este un nume de coloana sau un alias de coloana.

Un literal in lista lui SELECT este reprezentat de fiecare rand returnat la iesire.Sirurile de literali dintr-un text cu un format oarecare pot fi incluse in rezultatul interogarii si sunt tratate ca o coloana lista selectata.

Literalii de tip data calendaristica si caracter pot fi inchisi intre ghilimele simple(');literlalii de tip numar nu au nevoie de ghilimele simple(').

Urmatoarea declaratie contine literali selectati prin concatenare si printr-un alias de coloana:

SELECT EMPNO||'-'||ENAME EMPLOYEE,

'WORKS IN DEPARTMENT',

DEPTNO

FROM EMP;

EMPLOYEE 'WORKS IN DEPARTMENT' DEPTNO

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

7369-SMITH WORKS IN DEPARTMENT 20

7499-ALLEN WORKS IN DEPARTMENT 30

7521-WARD WORKS IN DEPARTMENT 30

7566-JONES WORKS IN DEPARTMENT 20

7654-MARTIN WORKS IN DEPARTMENT 30

7698-BLAKE WORKS IN DEPARTMENT 30

7782-CLARK WORKS IN DEPARTMENT 10

7788-SCOTT WORKS IN DEPARTMENT 20

7839-KING WORKS IN DEPARTMENT 10

7844-TURNER WORKS IN DEPARTMENT 30

7876-ADAMS WORKS IN DEPARTMENT 20

7900-JAMES WORKS IN DEPARTMENT 30

7902-FORD WORKS IN DEPARTMENT 20

7934-MILLER WORKS IN DEPARTMENT 10

Tratarea valorilor nule

Daca unui rand ii lipseste o valoare pentru o anumita coloana ,despre acea valoare se spune ca este nula.

O valoare nula este o valoare care este sau incorecta,sau necunoscuta,sau inaplicabila.O valoare nula nu este la fel ca 'zero'.Zero este un numar.Valoarea nula ocupa un octet in reprezentarea interna.

Valoarea nula este tratata corect de catre SQL.

Daca orice valoare de coloana intr-o expresie este nula atunci rezultatul este nul.In urmatoarea declaratie numai Salesman are un rezultat al salariului:

SELECT ENAME,SAL*12 + COMM ANNUAL_SAL

FROM EMP;

ENAME ANNUAL_SAL

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

SMITH

ALLEN 19500

WARD 15500

JONES

MARTIN 16400

BLAKE

CLARK

SCOTT

KING

TURNER 18000

ADAMS

JAMES

FORD

MILLER

Daca dorim sa obtinem un rezultat pentru toti angajatii,este necesar sa convertim valoarea nula la un numar . Noi folosim functia NVL pentru a converti o valoare nula la o valoare nenula.

Folositi functia NVL pentru a converti valoarea nula de la declaratia precedenta la 0.

SELECT ENAME, SAL*12+NVL(COMM,0) ANNUAL_SAL

FROM EMP;

ENAME ANNUAL_SAL

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

SMITH 9600

ALLEN 19500

WARD 15500

JONES 35700

MARTIN 16400

BLAKE 34200

CLARK 29400

SCOTT 36000

KING 60000

TURNER 18000

ADAMS 13200

JAMES 11400

FORD 36000

MILLER 15600

NVL asteapta doua argumente:

1. o expresie
2. o valoare nenula

De notat ca puteti folosi functia NVL pentru a converti un numar nul , data calendaristica sau sir de caractere la un alt numar , data calendaristica sau sir de caractere de acceasi lungime si de acelasi tip de date asteptate.

NVL(DATECOLUMN,'01-JAN-88')

NVL(NUMBERCOLUMN,9)

NVL(CHARCOLUMN,'STRING')

Prevenirea selectiei rindurilor duplicate

Daca nu se indica altfel, SQL*Plus afiseaza rezultatul unei interogari fara eliminarea intrarilor duplicate .

Pentru a lista toate numerele de departament din tabela EMP, introduceti:

SELECT DEPTNO

FROM EMP;

DEPTNO

------

20

30

30

20

30

30

10

20

10

30

20

30

20

10

Clauza DISTINCT

Pentru eliminarea valorilor duplicate din rezultat, includeti restrictia DISTINCT in comanda SELECT.

Pentru a elimina valorile duplicate afisate in exemplul urmator introduceti:

SELECT DISTINCT DEPTNO

FROM EMP;

DEPTNO

------

10

20

30

Coloane multiple pot fi specificate dupa restrictia DISTINCT si restrictia DISTINCt afecteaza toate coloanele selectate.

Pentru a afisa valorile distincte ale lui DEPTNO si JOB,introduceti:

SELECT DISTINCT DEPTNO,JOB

FROM EMP;

DEPTNO JOB

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

10 CLERK

10 MANAGER

10 PRESIDENT

20 ANALYST

20 CLERK

20 MANAGER

30 CLERK

30 MANAGER

30 SALESMAN

Aceasta afiseaza o lista a tuturor combinatiilor diferite de ocupatie si numere de departamente.

De notat ca restrictia DISTINCT poate sa fie referita numai o singura data si trebuie sa urmeze imediat dupa cuvantul de comanda SELECT.

Clauza WHERE

Clauza WHERE corespunde operatorului 'Restrictie' din algebra relationala.

Contine o conditie pe care rindurile trebuie sa o indeplineasca in ordinea afisarii lor.

Clauza WHERE ,daca este folosita , trebuie sa urmeze clauzei FROM :

SELECT coloane

FROM tabela

WHERE anumite conditii sunt intilnite

Clauza WHERE poate compara valori in coloana ,valori literale,expresii aritmetice sau functii.

Clauza WHERE asteapta trei elemente:

1. Un nume de coloana
2. Un operator de comparatie
3. Un nume de coloana, constanta sau lista de valori.

Operatorii de comparatie sunt utilizati in clauza WHERE si pot fi impartiti in doua categorii: logici si SQL.

Sirurile de caractere si datele calendaristice in clauza WHERE

Coloanele din ORACLE pot avea urmatoarele tipuri:caracter,numar sau data calendaristica.

Sirurile de caractere si datele calendaristice din clauza WHERE trebuie sa fie inchise in ghilimele simple('). Sirurile de caractere trebuie sa se supra- puna cu valoarea coloanei daca nu, trebuie modificate de o functie

Compararea unei coloane cu alta in cadrul aceluiasi rand

Puteti compara o coloana cu o alta coloana in acelasi rand,la fel ca si cu o valoare constanta.

De exemplu ,presupunem ca dorim sa obtinem acei angajati al caror comision esta mai mare decat salariul lor:

SELECT ENAME, SAL, COMM

FROM EMP

WHERE COMM> SAL;

ENAME SAL COMM

MARTIN 1,250.00 1,400.00

Nota:

Daca o valoare NULL este utilizata intr-o comparatie ,atunci operatorul de comparatie trebuie sa fie IS sau IS NOT NULL. Daca acesti operatori nu sunt uti lizati si valoarea NULL este comparata,atunci rezultatul este intotdeauna FALSE

De exemplu, COMM!=NULL este intotdeauna falsa.Rezultatul este fals deoarece o valoare NULL poate sa nu fie egala sau diferita cu orice alta valoare alta decat NULL.

De notat ca o astfel de eroare nu este semnalata,rezultatul fiind intotdea- una fals.

Interogarea datelor cu conditii multiple

Operatorii AND sau OR pot fi utilizati pentru a compune expresii logice.

Predicatul AND este adevarat numai daca ambele conditii sunt 'adevarate'; predicatul OR este adevarat daca cel putin una din conditii este 'adevarata'.

In urmatoarele doua exemple,conditiile sunt aceleasi,dar predicatele difera Priviti cum rezultatul este dramatic modificat.

Pentru a gasi toti functionarii care castiga intre

1000 si 2000,introduceti:

SELECT EMPNO,ENAME,JOB,SAL

FROM EMP

WHERE SAL BETWEEN 1000 AND 2000

AND JOB = 'CLERK';

EMPNO ENAME JOB SAL

7876 ADAMS CLERK 1,100.00

7934 MILLER CLERK 1,300.00

Pentru a afla toti angajatii care sunt si functionari si/sau

functionari care castiga intre 1000 si 2000,introduceti:

SELECT EMPNO,ENAME,JOB,SAL

FROM EMP

WHERE SAL BETWEEN 1000 AND 2000

OR JOB = 'CLERK';

EMPNO ENAME JOB SAL

7369 SMITH CLERK 800.00

7499 ALLEN SALESMAN 1,600.00

7521 WARD SALESMAN 1,250.00

7654 MARTIN SALESMAN 1,250.00

7844 TURNER SALESMAN 1,500.00

7876 ADAMS CLERK 1,100.00

7900 JAMES CLERK 950.00

7934 MILLER CLERK 1,300.00

Puteti combina AND sau OR in acceasi expresie logica. Cand AND sau OR apar in aceeasi clauza WHERE, toti operatorii AND sunt evaluati mai intai si apoi toti operatorii OR. Vom spune ca operatorii AND au o precedenta mai mare decat OR.

Deoarece AND are o precedenta mai mare decat OR urmatoarea declaratie SQL intoarce toti managerii cu salarii peste 1500$ si toti vanzatorii.

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO

FROM EMP

WHERE SAL> 1500

AND JOB = 'MANAGER'

OR JOB = 'SALESMAN';

EMPNO ENAME JOB SAL DEPTNO

7499 ALLEN SALESMAN 1,600.00 30

7521 WARD SALESMAN 1,250.00 30

7566 JONES MANAGER 2,975.00 20

7654 MARTIN SALESMAN 1,250.00 30

7698 BLAKE MANAGER 2,850.00 30

7782 CLARK MANAGER 2,450.00 10

7844 TURNER SALESMAN 1,500.00 30

Daca doriti sa selectati toti managerii si vanzatorii

cu salarii peste 1500$ puteti introduce:

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO

FROM EMP

WHERE SAL >1500

AND (JOB = 'MANAGER'

OR JOB = 'SALESMAN');

EMPNO ENAME JOB SAL DEPTNO

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

7499 ALLEN SALESMAN 1,600.00 30

7566 JONES MANAGER 2,975.00 20

7698 BLAKE MANAGER 2,850.00 30

7782 CLARK MANAGER 2,450.00 10

Parantezele specifica ordinea in care operatorii vor fi evaluati. In al doilea exemplu operatorul OR este evaluat inaintea operatorului AND.

Precedenta operatorilor

Toti operatorii sunt aranjati intr-o ierarhie ceea ce le determina precedenta .Intr-o expresie operatiile sunt executate in ordinea precedentei lor de la mare la mica.

Cand operatorii au precedenta egala atunci ei se evalueaza de la stanga la dreapta.

1. Toti operatorii de comparatie si SQL au precedenta egala: =,!=,<,>,<=,>=,BETWEENAND,IN,LIKE,IS NULL.
2. NOT(pentru a inversa rezultatul unei expresii logice.De ex: WHERE not(sal>2000))
3. AND
4. OR.

De fiecare data cand sunteti in dubiu despre care dintre doua operatii vor fi executate mai intai cand o expresie este evaluata, sunteti liberi sa utilizati parantezele pentru a clarifica semnificatia dorita si pentru a va asigura ca SQL*Plus face ceea ce doriti.

Sa presupunem ca doriti sa gasiti toti managerii, din orice departament,si toti functionarii din departamentul 10:

SELECT *

FROM EMP

WHERE JOB='MANAGER' OR (JOB = 'CLERK' AND DEPTNO = 10);

Parantezele de deasupra sunt necesare, AND are o precedenta mai mare decat OR ,dar ele clarifica semnificatia expresiei.

INTEROGARI MULTIPLE

SUBCERERI

In acest capitol se vor prezenta alte caracteristici ale declaratiei SELECT, cum ar fi cereri continute in clauza WHERE sau HAVING ale altei decla- ratii SQL.

Subcereri ascunse

O subcerere este o declaratie SELECT care este ascunsa in interiorul altei declaratii SELECT si care intoarce rezultate intermediare.

De exemplu:

SELECT column1, column2,

FROM table

WHERE column =

(SELECT column

FROM table

WHERE condition)

Subcererea este adesea referita ca un subselect sau ca un SELECT interior; in general, subcererea se executa prima si rezultatul sau este folosit pentru a completa conditia cererii principale sau a cererii externe.Folosirea sub- cererilor permite consruirea de comenzi puternice pornind de la unele simple. Cererea ascunsa poate fi foarte folositoare cind este nevoie sa fie selectate linii dintr-o tabela printr-o conditie ce depinde de date din propria tabela.

Subcereri ce intorc o linie

Pentru a gasi angajatul care cistiga salariul minim din companie (salariul minim este o cantitate necunoscuta), trebuie parcursi doi pasi:

1. Gasirea salariului minim:

SELECT MIN(SAL)

FROM EMP;

MIN(SAL)

800

9. Gasirea angajatului care cistiga salariul minim:

SELECT ENAME, JOB, SAL

FROM EMP

WHERE SAL = (cel mai mic salariu care este cunoscut)

Putem combina cele doua cereri ca o subcerere ascunsa:

SELECT ENAME, JOB, SAL

FROM EMP

WHERE SAL = (SELECT MIN(SAL)

FROM EMP);

ENAME JOB SAL

SMITH CLERK 800

Cum sint prelucrate subcererile ascunse ?

O declarartie SELECT poate fi considerata ca un bloc de cerere.Exemplul de mai sus consta din doua blocuri de cerere - cererea principala si cererea in- terioara.

Declaratia SELECT interioara sau blocul de cerere este executata prima, producind un rezultat al cererii: 800.Blocul de cerere principal este apoi prelucrat si foloseste valoarea intoarsa de cererea interioara pentru a com- pleta conditia de cautare.Deci, cererea principala in final va arata in felul urmator:

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL = 800;

In exemplul de mai sus, 800 este o valoare unica.Subcererea care a intors valoarea 800 se numeste subcerere ce intoarce o singura linie.Cind o subcerere intoarce doar o linie, va fi folosit un operator logic sau un operator de com- paratie.De exemplu: =, <, >, <=, etc.

Pentru a gasi toti angajatii ce au aceeasi functie ca BLAKE, vom introduce:

SELECT ENAME, JOB

FROM EMP

WHERE JOB = (SELECT JOB

FROM EMP

WHERE ENAME = 'BLAKE');

ENAME JOB

JONES MANAGER

BLAKE MANAGER

CLARK MANAGER

Cererea interioara intoarce functia lui BLAKE, care este folosita in partea dreapta a clauzei WHERE di cererea principala (dupa operatorul de comparatie).

Subcereri ce intorc mai mult de o linie

Urmatoarea cerere gaseste angajatii care au salariul egal cu salariul minim din fiecare departament:

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL IN

(SELECT MIN(SAL)

FROM EMP

GROUP BY DEPTNO);

ENAME SAL DEPTNO

SMITH 800 20

JAMES 950 30

MILLER 1300 10

Observati ca, cererea interioara are clauza GROUP BY.Aceasta inseamna ca va intoarce mai mult decit o valoare.Deci este nevoie sa folosim un operator de comparatie multi linie.In acest caz, operatorul IN trebuie sa fie folosit, deoarece rezulta o lista de valori.

Rezultatul obtinut nu arata si departamentul in care lucreaza angajatul.
Deoarece se compara doar valorile salariilor, cererea interioara poate intoar- ce o valoare simpla, pentru ca el cauta cel mai mic salariu pentru unul din departamente, nu in mod necesar departamentul propriu al angajatului.Prin urmare, cererea poate fi rescrisa pentru a gasi combinatia salariul angaja- tului si numarul departamentului cu salariul minim si numarul departamentului:

Compararea a mai multor valori

Urmatoarea cerere ggaseste angajatii care cistiga salariul cel mai mic din departamentul lor:

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE (SAL, DEPTNO) IN

(SELECT MIN(SAL), DEPTNO

FROM EMP

GROUP BY DEPTNO);

ENAME SAL DEPTNO

SMITH 800 20

JAMES 950 30

MILLER 1300 10

Cererea de mai sus compara o pereche de coloane.

Observatie : coloanele din partea stinga a conditiei de cautare sint in pa- ranteze si fiecare coloana este separata printr-o virggula.

Coloanele listate in clauza SELECT a subcererii trebuie sa fie in aceeasi ordine ca si lista coloanelor dintre paranteze din clauza WHERE a cererii externe.

Coloanele intoarse de cererea interioara trebuie, de asemenea sa se potri- veasca in numar si tip de date cu coloanele cu care ele sint comparate in cererea externa.

De exemplu :

WHERE (numcolumn, charcolumn) =

(SELECT datecolumn, numcolumn, charcolumn

nu este permis.

Erori intilnite

Cind o subcerere intoarce mai mult decit o linie si este folosit un opera- tor de comparatie pt o singura linie, SQL*Plus da urmatorul mesaj de eroare:

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL = (SELECT MIN(SAL)

FROM EMP

GROUP BY DEPTNO);

Eroare : subcerere ce trebuie sa intoarca o singura linie intoarce mai mult decit o linie.

Nu este selectata nici o inregistrare.

Daca cererea interioara nu intoarce nici o linie, va fi dat urmatorul mesaj de eroare :

SELECT ENAME, JOB

FROM EMP

WHERE JOB = (SELECT JOB

FROM EMP

WHERE ENAME = 'SMITHE');

Eroare : subcerere ce trebuie sa intoarca o singura linie nu intoarce nici o linie.

Nu este selectata nici o inregistrare.

Operatorii SOME/ANY, ALL

Operatorii ANY sau ALL pot fi folositi pentru subcererile care intorc mai mult de o linie.Ei sint folositi in clauzele WHERE sau HAVING in legatura cu operatorii logici (=, !=, <, >, >=, <+).
ANY (sau sinonimul sau SOME) compara o valoare cu fiecare valoare intoarsa de o subcerere.
Pentru a afisa angajatii care cistiga mai mult decit cel mai mic salariu din departamentul 30, introducem :

SELECT ENAME, SAL, JOB, DEPTNO

FROM EMP

WHERE SAL > SOME (SELECT DISTINCT SAL

FROM EMP

WHERE DEPTNO = 30)

ORDER BY SAL DESC;

ENAME SAL JOB DEPTNO

KING 5000 PRESIDENT 10

SCOTT 3000 ANALYST 20

FORD 3000 ANALYST 20

JONES 2975 MANAGER 20

BLAKE 2850 MANAGER 30

CLARK 2450 MANAGER 10

ALLEN 1600 SALESMAN 30

MILLER 1300 CLERK 10

WARD 1250 SALESMAN 30

ADAMS 1100 CLERK 20

Cel mai mic salariu din departamentul 30 este 950$ (a lui James).Cererea principala intoarce angajatii care cistiga un salariu mai mare ca salariul minim din departamentul 30.Asa ca '> ANY' inseamna mai mare ca minim.'=ANY' este echivalent cu IN.

Cind se foloseste SOME/ANY, DISTINCT este frecvent folosit pentru a impie- dica sa se selecteze linniile de mai multe ori.
ALL compara o valoare cu fiecare valoare intoarsa de o subcerere.
Urmatoarea cerere gaseste angajatii care cistiga mai mult ca fiecare angajat din departamentul 30 :

SELECT ENAME, SAL, JOB, DEPTNO

FROM EMP

WHERE SAL > ALL (SELECT DISTINCT SAL

FROM EMP

WHERE DEPTNO = 30)

ORDER BY SAL DESC;

ENAME SAL JOB DEPTNO

KING 5000 PRESIDENT 10

SCOTT 3000 ANALYST 20

FORD 3000 ANALYST 20

JONES 2975 MANAGER 20

Cel mai mare salariu din departamentul 30 este 250$ (a lui Blake), asa ca cererea intoarce acei angajati ai caror salariu este mai mare ca salariul maxim din departamentul 30, prin urmare mai mare ca fiecare salariu din de- partament.

Operatorul NOT poate fi folosit cu IN, ANY sau ALL.

Clauza HAVING cu subcereri ascunse

Subcererile ascunse pot fi folosite de asemenea in clauza HAVING.

(Observatie : clauza WHERE se refera la o singura linie si clauza HAVING la grupuri de linii specificate in clauza GROUP BY.)

De exemplu, pentru a afisa departamentul(ele) care au un salariu mediu mai mare ca departamentul 30, introducem :

SELECT DEPTNO, AVG(SAL)

FROM EMP

HAVING AVG(SAL) > (SELECT AVG(SAL)

FROM EMP

WHERE DEPTNO = 30)

GROUP BY DEPTNO;

DEPTNO AVG(SAL)

Pentru a construi o cerere care gaseste functia cu cel mai mare salariu mediu, introducem :

SELECT JOB, AVG(SAL)

FROM EMP

GROUP BY JOB

HAVING AVG(SAL) = (SELECT MAX(AVG(SAL))

FROM EMP

GROUP BY JOB);

JOB AVG(SAL)

PRESIDENT 5000

Mai intii cererea interioara gaseste salariul mediu pentru fiecare grup de functii diferit si functia MAX alege cel mai mare salariu mediu.Acea valoare (5000) este folosita in clauza HAVING.Clauza GROUP BY din cererea principala este necesara pentru ca lista ce urmeaza dupa SELECT-ul din cererea principala contine atit o coloana agregat cit si o coloana non-agragat.

Ordonarea datelor cu subcereri

Nu poate exista o clauza ORDER BY intr-o subcerere.

Regula este ca poate exista doar o singura clauza ORDER BY pentru o decla- ratie SELECT si, daca este specificata, trebuie sa fie ultima clauza din co- manda SELECT.

Subcereri ascunse

Subcererile pot fi ascunse (folosite in interiorul unei subcereri) :

Afisati numele, functia si data angajarii pentru angajatii al caror salariu este mai mare ca cel mai mare salariu din orice departament de vinzari.

SELECT ENAME, JOB, HIREDATE, SAL

FROM EMP

WHERE SAL > (SELECT MAX(SAL)

FROM EMP

WHERE DEPTNO = (SELECT DEPTNO

FROM DEPT

WHERE DNAME = 'SALES'));

ENAME JOB HIREDATE SAL

JONES MANAGER 02-APR-81 2975

SCOTT ANALYST 09-DEC-82 3000

KING PRESIDENT 17-NOV-81 5000

FORD ANALYST 03-DEC-81 3000

Limitele de imbricare

Limita nivelelor de imbricare pentru o subcerere este 255.

Reguli de scriere a cererilor

• cererea interioara trebuie sa fie inclusa intre paranteze si trebuie sa fie in partea dreapta a conditiei.
• subcererile nu pot avea clauza ORDER BY.
• clauza ORDER BY apare la sfirsitul declaratiei SELECT principale.
• coloanele multiple din lista din SELECT a cererii interioare trebuie sa fie in aceeasi ordine ca si coloanele ce apar in conditia din clauza cere- rii principale.De asemenea mai trebuie sa corespunda si tipul si numarul coloanelor listate.
• subcererile sint intotdeauna executate de la cea mai adinca imbricare pina la nivelul principal de imbricare, daca nu sint subcereri corelate (acestea vor fi discutate mai tirziu).
• pot fi folositi operatorii logici si SQL la fel de bine ca si ANY si ALL.
• subcererile pot
• intoarce una sau mai multe linii;
• intoarce una sau mai multe coloane;
• folosi GROUP BY sau functii de grup;
• fi folosite inlantuite cu predicate multiple AND sau OR in aceesi cerere externa.
• uni tabele.
• recupera dintr-o tabela diferita de cea a cererii exterioare.
• apare in declaratii SELECT, UPDATE, DELETE, INSERT, CREATE TABLE.
• fi corelate cu o cerere exterioara.
• folosi operatori de multimi.

Subcereri corelate

O subcerere corelata este o subcerere care este executata o data pentru fiecare linie candidat considerata de cererea principala si care la executie foloseste o valoare dintr-o coloana din cererea exterioara.Aceasta determina ca subcererea corelata sa fie prelucrata intr-un mod diferit de subcererea ascunsa obisnuita.

O subcerere corelata este identificata prin folosirea unei coloane a cererii exterioare in clauza predicatului cererii interioare.

Cu o subcerere ascunsa obisnuita, SELECT-ul interior ruleaza primul si se executa o data, intorcind valori ce vor fi folosite de cererea principala.Pe de alta parte, o subcerere corelata se executa o data pentru fiecare linie candidat considerata de cererea externa.Cererea interioara este dirijata de cererea externa.

Pasii de executie ai unei subcereri corelate :

1. Se obtine linia candidat. (obtinuta de cererea exterioara)
2. Se executa cererea interioara folosind valoarea liniei candidat.
3. Se folosesc valorile rezultate din cererea interioara pentru a pastra sau pentru a inlatura linia candidat.
4. Se repeta pina nu mai ramine nici o linie candidat.

Desi subcererea corelata se executa repetat, o data pentru fiecare linie in cererea principala, aceasta nu inseamna ca subcererile corelate sint mai putin eficiente ca subcererile necorelate obisnuite.Se va vorbi despre eficienta mai tirziu in acest capitol.

Putem folosi o subcerere corelata pentru a gasi angajatii care cistiga un salariu mai mare ca salariul mediu al departamentului lor :

SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP E

WHERE SAL > (SELECT AVG(SAL)

FROM EMP

WHERE DEPTNO = E.DEPTNO)

ORDER BY DEPTNO;

EMPNO ENAME SAL DEPTNO

7839 KING 5000 10

7566 JONES 2975 20

7788 SCOTT 3000 20

7902 FORD 3000 20

7499 ALLEN 1600 30

7698 BLAKE 2850 30

Putem observa imediat ca este o cerere corelata pentru ca am folosit o co- loana din SELECT-ul extern in clauza WHERE din SELECT-ul interior.

Observati ca alias-ul este necesar doar pentru a indeparta ambiguitatea pentru numele coloanelor.

Sa analizam exemplul de mai sus folosind tabela EMP :

Cererea principala

1. Se selecteaza prima linie candidat - Smith din departamentul 20 cistiga 800 .
2. EMP in clauza FROM are alias-ul E care obtine coloana DEPTNO referita in clauza WHERE a cererii interioare.
3. Clauza WHERE compara 800 cu valoarea intoarsa de cererea interioara.
   
   
   Cererea interioara
4. Calculeaza AVG(SAL) pentru departamentul angajatului.
5. Valoarea departamentului din clauza WHERE este departamentul candidatului (E.DEPTNO), valoare transmisa cererii interioare din coloana DEPTNO a ce- rerii exterioare.
6. AVG(SAL) pentru departamentul lui Smith - 20 - este 2175 .
7. Linia candidat nu indeplineste conditia, asa ca este indepartata.
8. Se repeta de la pasul 1 pentru urmatoarea linie candidat; ALLEN din de- partamentul 30 cistiga 1600 .

Selectia liniilor candidat continua cu verificarea conditiei ce apare in re- zultatul cererii.

Observatie : o subcerere corelata este semnalata de un nume de coloana, un nume de tabela sau un alias de tabela in clauza WHERE, care se refera la va- loarea unei coloane in fiecare linie candidat din SELECT-ul exterior.De aseme- nea subcererea corelata se executa repetat pentru fiecare linie candidat in cererea principala.

Comenzile UPDATE pot contine subcereri corelate :

UPDATE EMP E

SET (SAL, COMM) = (SELECT AVG(SAL) * 1.1, AVG(COMM)

FROM EMP

WHERE DEPTNO = E.DEPTNO)

HIREDATE = '11-JUN-85';

Operatori

Cind se folosesc declaratii SELECT ascunse, operatorii logici, precum si ANY si ALL sint toti valizi.In plus poate fi folosit operatorul EXISTS.

Operatorul EXISTS

Operatorul EXISTS este frecvent folosit cu subcererile corelate.El testeaza daca o valoare exista (NOT EXISTS specifica daca ceva nu exista).Daca valoarea exista se intoarce TRUE; daca valoarea nu exista se intoarce FALSE.

Pentru a gasi angajatii ce au cel putin un subordonat, introducem :

SELECT EMPNO, ENAME, JOB, DEPTNO

FROM EMP E

WHERE EXISTS (SELECT EMPNO

FROM EMP

WHERE EMP.MGR = E.EMPNO)

ORDER BY EMPNO;

EMPNO ENAME JOB DEPTNO

7566 JONES MANAGER 20

7698 BLAKE MANAGER 30

7782 CLARK MANAGER 10

7788 SCOTT ANALYST 20

7839 KING PRESIDENT 10

7902 FORD ANALYST 20

Sa gasim toti angajatii al caror departament nu este in tabela DEPT :

SELECT EMPNO, ENAME, DEPTNO

FROM EMP

WHERE NOT EXISTS (SELECT DEPTNO

FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO);

Nu va fi selectata nici o inregistrare.

Alt mod de a gasi departamentul care nu are nici un angajat este :

SELECT DEPTNO, DNAME

FROM DEPT D

WHERE NOT EXISTS (SELECT 1

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO);

DEPTNO DNAME

40 OPERATIONS

Observati ca SELECT-ul interior nu este necesar sa intoarca o valoare spe- cifica, asa ca poate fi selectata o cifra.

De ce sa folosim o subcerere corelata ?

Subcererea corelata este un mod de a 'citi' fiecare linie din tabela si de a compara valorile din fiecare linie cu datele inrudite.Aceasta este folosita oricind o subcerere trebuie sa intoarca un rezultat diferit sau o multime de rezultate pentru fiecare linie candidat considerata de cererea principala.Cu alte cuvinte, o subcerere corelata este folosita pentru a raspunde la intre- bari cu mai multe subpuncte al caror raspuns depinde de valoarea din fiecare linie din cererea parinte.

SELECT-ul interior este executat normal o data pentru fiecare linie candi- dat.

Considerente de eficienta

Vom examina cele doua tipuri de subcereri.Trebuie mentionat ca subcererea corelata (cu EXISTS) poate fi cel mai performant mod pentru unele cereri.

Performanta va depinde de folosirea indexarilor, de numarul liniilor in- toarse de cereri, de dimensiunea tabelelor si daca sint necesare tabelele temporare pentru a evalua rezultatele temporare.Tabelele temporare generate de ORACLE nu sint indexate si acest lucru conduce la degradarea performantelor pentru subcererile ce folosesc IN, ANY si ALL.

Cele de mai sus sint puncte de vedere generale.Performantele sint discutate mai in detaliu in alte cursuri.

NOT EXISTS contra NOT IN

Desi intr-o subcerere o operatie NOT IN poate fi la fel de eficienta ca si NOT EXISTS, NOT EXISTS este mult mai sigur daca subcererea intoarce niste valori NULL, fata de de NOT IN pentru care conditia se evalueaza la FALSE cind in lista de comparatii sint incluse valori NULL.

Considerind urmatoarea cerere care gaseste angajatii ce nu au nici un su- bordonat :

SELECT ENAME, JOB

FROM EMP

WHERE EMPNO NOT IN (SELECT MGR

FROM EMP);

Nici o linie nu va fi intoarsa de cererea de mai sus, deoarece coloana MGR contine o valoare NULL.

Cererea corecta este :

SELECT ENAME, JOB

FROM EMP E

WHERE NOT EXISTS (SELECT MGR

FROM EMP

WHERE MGR = E.EMPNO);

ENAME JOB

SMITH CLERK

ADAMS CLERK

ALLEN SALESMAN

WARD SALESMAN

MARTIN SALESMAN

TURNER SALESMAN

JAMES CLERK

MILLER CLERK