Limbaje de interogare comerciale – baze de date

Limbaje de interogare comerciale – baze de date

4.1. Istoric, obiective

SQL (Structured Query Language), a fost conceput initial de firma IBM, pentru produsul dBASE, ca un limbaj standard de descriere a datelor si de acces la informttiile din bazele de date. Limbaj de interogare a bazelor de date relationale, SQL a fost utilizat pe scara larga si pana în prezent au fost dezvoltate sapte versiuni ale standardului SQL, trei dintre ele apartinînd Institutului National American de Standarde (ANSI), celelalte fiind concepute de firme de prestigiu ca IBM, Microsoft si Borland sau de către consortii ca SAG (The SQL Access Group) si X/Open.

Primul standard SQL a fost creat in anul 1989 de către ANSI fiind cunoscut sub numele de ANSI-SQL'89 si a fost revizuit in octombrie 1992 sub noua denumire: ANSI-SQL'92.

In anul 1992, firma Microsoft, in calitate de membru SAG, a lansat pe piata produsul ODBC (Open Database Connectivity), un standard API-SQL care defineste o interfată de programare a aplicatiilor (API) pentru accesul la bazele de date. Pe de alta parte, un alt membru SAG, firma Borland, a pregatit propriul său standard API-SQL denumit IDAPI (Integrated Database Application Programming Interface).

In încercarea de a extinde substantial limbajul SQL, ANSI pregateste noua generatie de standarde SQL, denumită SQL3, care se adreseaza bazelor de date orientate obiect.

Mentionam aici si standardul IBM pentru acces la bazele de date de pe platformele proprii, denumit DRDA (Distributed Relational Database Access). Interfata de programare API oferă o cale de comunicare între programe si bazele de date prin apeluri de functii care substituie instructiunile SQL. Astfel, standardele API se bazează pe conectivitate si anume pe conectarea utilizatorului la baza de date, precum si pe schimbul de date si mesaje SQL. Conceperea de interfete de programare API pentru SQL oferă posibilitatea gestionarii mai multor tipuri de baze de date cu costuri relativ scăzute.

Interfetele de programare API, realizate in cadrul grupului SAG de către firmele Microsoft si Borland, se bazeaza pe un subset al standardului ANSI. Gestiunea bazelor de date de diferite tipuri se poate face usor prin intermediul interfetelor inteligente, numite drivere. Acestea receptionează mesajele de la clienti si le traduc în instructiuni SQL sau API. Deci în loc de a interoga direct baza de date, clientul convesează cu interfata inteligentă, ceea ce asigură independenta fată de implementarea bazelor de date. Aceste interfete conduc la cresterea perform 414b19e antelor ,utilizînd dialectul SQL sau bibliotecile API.

Se pare ca în viitorul apropiat nu se întrevăd schimbări majore în domeniul standardelor SQL.

4.2. Clauze si operatori SQL

4.2.1. Clauzele SELECT, FROM si WHERE

Clauzele SQL SELECT, FROM si WHERE pot fi puse în corespondentă cu operatorii din algebra relatională, dupa cum urmeaza:

clauza SELECT mentioneaza o lista de atribute si corespunde proiectiei din algebra relatională;

clauza FROM mentionează o listă de relatii (tabele) si corespunde produsului cartezian din algebra relationala;

clauza WHERE descrie un predicat de selectie si corespunde selectiei din algebra relatională.

O interogare simpla SQL este de forma:

SELECT A₁, A₂, ..., A_n

FROM R₁, R₂, ..., R_m

WHERE P

Unde: A_i sunt atribute care apar în cel putin una dintre relatiile R_i;

R_i sunt relatii (tabele);

P este un predicat de selectie.

Interogarea este echivalentă cu urmatoarea expresie din algebra relatională:

Intelesul diferit al termenului "select" utilizat în SQL si in algebra relationala este un fapt istoric nefericit. In SQL, "select" desemneaza proiectia iar in algebra relationala acelasi termen desemneaza selectia dupa un predicat de selectie.

Lista de atribute care apare in clauza SELECT din SQL poate fi inlocuita cu simbolul * daca se doreste selectarea tuturor atributelor care apar in relatiile din clauza FROM.

Intotdeauna rezultatul unei interogari SQL este o relatie (o tabela).

Pentru a ilustra cu exemple modul in care se scriu interogarile SQL vom utiliza tabele cu urmatoarea structura:

Tabela FURNIZORI cu schema de relatie (cod_furnizor, nume_furnizor, adresa_furnizor).

Tabela FLORI cu schema de relatie (cod_produs, nume_produs, culoare, inaltime, pret_unitar).

Tabela COMENZI cu schema de relatie (nr_comanda, cod_produs, cod_furnizor, data_comenzii, timp_livrare, cantitate).

Presupunem ca aceste tabele constituie un model foarte simplificat de baza de date legata de evidentele unei florarii, evidente legate de florile pe care floraria le vinde de obicei (tabela FLORI), de comenzile pe care le face floraria (tabela COMENZI) si de furnizorii de la care obisnuieste floraria sa cumpere flori (tabela FURNIZORI). Presupunem de asemenea ca atributul adresa_furnizor din schema de relatie corespunzatoare tabelei FURNIZORI este un atribut compus. El poate fi descompus in: oras, strada, numar.

EXEMPLE: Sa se exprime in SQL urmatoarele inteogari:

"Sa se afiseze toate datele despre toti furnizorii"

SELECT *

FROM furnizori

"Sa se afiseze orasele de resedinta ale tuturor furnizorilor"

SELECT oras

FROM furnizori

Ca rezultat al acestei interogari se va obtine o tabela cu o singura coloana, care contine numele oraselor de resedinta ale furnizorilor. Se va observa ca se repeta numele oraselor, deoarece se vor afisa orasele pentru fiecare furnizor in parte din tabela FURNIZORI.

O interogare SQL are urmatoarea forma generala:

SELECT [DISTINCT/ALL] <lista de atribute>

FROM <lista de relatii>

[WHERE <conditie> / GROUP BY< lista de atribute> /

HAVING <conditie> / ORDER BY <lista de atribute>

[ASC / DESC] / UNION <sub_interogare>]; ... ...

După cum se observă, singurele elemente obligatorii intr-o interogare SQL sunt clauzele SELECT cu lista de atribute ce vor fi extrase si clauza FROM cu relatiile din care fac parte atributele. Asadar o interogare SQL trebuie sa contina cel putin urmatoarele informatii:

SELECT <lista de atribute>

FROM <lista de relatii>

restul clauzelor sunt optionale.

Lista de atribute poate consta din :

o serie de atribute separate prin virgulă care vor apărea în tabela-rezultat în ordinea explicitată în linia de comandă, de la stanga la dreapta;

toate atributele din relatia asupra careia se aplica interogarea, în ordinea în care au fost definite în această relatie (in locul acestei liste se poate utiliza semnul "*");

expresii formate din urmatoarele elemente:

-atribute si operatori aritmetici (de exemplu: cantitate*pret_unitar)

-functii standard (de exemplu CTOD( ));

-constante;

-variabile de memorie.

expresii care contin functii SQL agregat cum ar fi AVG( ), MAX( ), MIN( ), COUNT( ), SUM( ) ...

In exemplele de mai sus, pentru a evita repetarea unor informatii in tabelele rezultat se poate utiliza cuvintul cheie DISTINCT. Optiunea DISTINCT permite eliminarea tuplelor duplicat. In acest mod numai prima aparitie a unui tuplu este afisată în tabela-rezultat.

EXEMPLU:

"Sa se afiseze toate orasele resedinte ale furnizorilor, dar sa apara fiecare oras o singura data in tabela-rezultat"

SELECT DISTINCT oras

FROM furnizori

Aceasta interogare va avea ca rezultat o tabela care contine toate orasele in care isi au resedinta furnizorii din tabela FURNIZORI, dar fiecare oras va fi afisat o singura data.

Optiunea ALL permite dimpotrivă, afisarea tuplelor-duplicat. Această optiune este implicită.

Clauza FROM are forma generala:

FROM <<nume relatie>/ <nume view>[<alias>] ... >

si specifica relatiile (pot fi si nume de view) din care vor fi regăsite datele. In cazul în care se operază cu mai multe tabele, este utilă atribuirea unor prescurtări, (numite alias) numelor de tabele ce vor fi utilizate în interogare.

EXEMPLE:

"Sa se afiseze codurile furnizorilor si numerele de comanda corespunzatoare pentru toti furnizorii care a cel putin o comanda"

SELECT 1.cod_furnizor, B.numar_comanda

FROM furnizori 1, comenzi B

WHERE 1.cod_furnizor=B.cod_furnizor

"Sa se afiseze codurile furnizorilor, numele furnizorilor, cantitatile, si numerele comenzilor pentru toti furnizorii care au cel putin o comanda"

SELECT A.cod_furnizor, nume_furnizor, cantitate, numar_comanda

FROM furnizori A, comenzi B

WHERE A.cod_furnizor=B.cod_furnizor

A se observa ca in al doilea exemplu nu s-a mai utilizat notatia cu alias pentru atributul numar_comanda din tabela comenzi deoarece nu este pericol de confuzie. In schimb s-a utilizat notatia pentru a deosebi atributul cod_furnizor din tabela furnizori de atributul cu acelasi nume din tabela comenzi.

Pentru a restrange tuplele ce apar în tabela-rezultat, se specifică o conditie de căutare prin utilizarea unui predicat de selectie in clauza WHERE.

Clauza WHERE are forma generala:

WHERE <predicat> / <expresie>;

Numai tuplele care satisfac predicatul de selectie vor fi incluse in tabela-rezultat. Predicatul de căutare poate fi specificat printr-o conditie logica in care se utilizeaza urmatoarele elemente:

operatori:

- aritmetici: + - / * ** ^

- relationali: < > <= >= <> != =

logici: NOT AND OR

- operatori SQL: IN, EXISTS, ALL, ANY

sub-interogări (exprimate prin interogari SQL),cu observatia că acestea vor fi primele evaluate si tabela-rezultat trebuie să corespundă operatorilor ce i se aplică în continuare.

Operatorii aritmetici

Acesti operatori sunt binecunoscuti si mentionam aici doar faptul ca si ** si ^ reprezinta ridicarea la putere.

Ca operanzi, se pot utiliza atribute, constante, functii sau expresii algebrice. Expresiile algebrice pot aparea in clauzele SELECT sau WHERE.

EXEMPLU:

"Sa se afiseze codul produsului si valoarea pe care o reprezinta cantitatea de produs comandata la diversi furnizori"

SELECT cod_produs, cantitate*pret_unitar

FROM comenzi

WHERE cantitate<>0

Operatorii relationali

< mai mic

> mai mare

negarea operatorilor <, >, =. Se obtin operatorii: !=(diferit), !<(nu mai mic), !>(nu mai mare).

<= mai mic sau egal

=> mai mare sau egal

<> diferit

Facem observatia ca valorile comparate trebuie sa apartina unor tipuri de date compatibile (care se pot compara intre ele).

EXEMPLU:

"Sa se afiseze codurile plantelor de culoare alba."

SELECT cod-produs

FROM flori

WHERE culoare='alb'

Operatorii logici

Dacă o clauză WHERE contine mai multe conditii formate prin utilizarea aceluiasi tip de oparator logic, evaluarea se va face de la stanga la dreapta. tipul de operator logic este dat de precedenta operatorilor. Operatorul NOT are cea mai mare prioritate, urmat de AND si OR care practic sunt de prioritati egale.

Pentru a schimba ordinea de evaluare a unei expresii se utilizează parantezele rotunde ().

EXEMPLE:

"Sa se afiseze numele plantelor de culoare alba si de inaltime minima 50 cm"

SELECT nume_planta

FROM flori

WHERE culoare='alb' AND inaltime<50

"Sa se afiseze numele, culoarea si inaltimea plantelor care fie au culoarea alba fie sunt de inaltime mai mica de 50 cm"

SELECT nume_planta, culoare, inaltime

FROM flori

WHERE culoare='alb' OR inaltime<50

A se observa ca ultima interogare este echivalenta cu interogarea

SELECT nume_planta, culoare, inaltime

FROM flori

WHERE culoare='alb' OR NOT inaltime>=50

4.2.2. Ordonarea tuplelor (clauza ORDER BY)

În exemplele anterioare, tuplele tabelei-rezultat apar în aceeasi ordine în care au fost introduse. Pentru modificarea ordinii de afisare se utilizează clauza ORDER BY. Forma generala a acestei clauze este:

ORDER BY <(<nume atribut>/<număr întreg>)(ASC/ DESC)>,...

Tuplele sunt ordonate în mod implicit în ordine ascendentă (ASC). Ordinea este: 0,...,9,A,...,Z,a,...,z conform codului ASCII. Afisarea în ordine descrescătoare se poate face prin utilizarea optiunii DESC.

EXEMPLU:

"Sa se afiseze datele despre florile din evidente in ordinea alfabetica a numelor florilor."

SELECT *

FROM flori

ORDER BY nume_planta, ASC

A se observa ca daca ar fi lipsit mentiunea ASC, ordinea tuplelor ar fi fost aceeasi deoarece ordinea ascendenta este implicita.

În loc de precizarea numelui atributului după care se face ordonarea, se poate preciza pozitia atributului în lista de atribute specificate în comanda SELECT.

EXEMPLU:

SELECT oras, cod_furnizor, nume_furnizor

FROM furnizori

ORDER BY 1 DESC, 3 ASC

In urma executiei interogarii se va obtine o tabela cu numele oraselor sortate descrescator si pentru fiecare oras, codurile furnizorilor si apoi numele funizorilor in ordine alfabetica.

Operatorul IN permite simplificarea predicatului de căutare. Predicatul IN testează dacă valoarea unui atribut specificat în lista de atribute din clauza WHERE se potriveste uneia din valorile listei specificate în predicatul IN (testează apartenenta la o multime).

EXEMPLU:

"Sa se afiseze toate datele despre furnizorii care au sediul in Bucuresti sau in Brasov sau in Cluj"

SELECT *

FROM furnizori

WHERE oras IN ('BUCURESTI', 'BRASOV', 'CLUJ')

Functii standard

Functiile standard, cunoscute si sub numele de functii agregat, apar in clauza SELECT si se aplica atributelor din tabelele implicate in interogare. Functii standard sunt:

-valoarea medie - AVG

-valoarea minima - MIN

-valoarea maxima - MAX

-total(sumare) - SUM

-numărătoare - COUNT

NOTA: Nu este permisa utilizarea acestor functii in clauza WHERE deoarece ele actioneaza la nivel de atribut si nu la nivel de tuplu.

EXEMPLE:

"Care este cantitatea minima comandata?"

SELECT MIN(cantitate)

FROM comenzi

Spre exemplu,următoarea interogare are ca rezultat afisarea cantitătii totale si a numărului de produse din fisierul de comenzi.

SELECT SUM(cantitate), COUNT(*)

FROM comenzi

"Care este cantitatea medie comandata?"

SELECT AVG(cantitate)

FROM comenzi

"Care este cantitatea totala comandata din planta cu cod '202'?"

SELECT SUM(cantitate)

FROM comenzi

WHERE cod_produs='202'

"Cate tuple contine tabela de flori?"

SELECT COUNT(*)

FROM flori

"Cate culori de flori sunt inregistrate pentru florile din fisier?"

SELECT COUNT(DISTINCT culoare)

FROM flori

4.2.3. Gruparea rezultatelor (clauza GROUP BY)

În multe cazuri, utilizatorul doreste anumite situatii sintetice, cum ar fi obtinerea de totaluri si subtotaluri. Pentru aceaste operatii, limbajul SQL permite utilizarea clauzelor GROUP BY si HAVING. Aceste clauze organizează tuplele în grupuri asupra cărora se pot realiza anumite operatii, în special prin aplicarea functiilor agregat.

Clauza GROUP BY grupează tuplele din relatie după atributele cu aceeasi valoare care sunt specificate în clauză, si genereză un singur tuplu pentru fiecare grup de tuple cu aceeasi valoare pe atribut.

Atributele care apar în clauza SELECT pot fi de două feluri:

- atribute care alcătuiesc baza pentru grupare (cele care apar în clauza GROUP BY)

- atribute care nu participa la gruparea rezultatelor.

EXEMPLU:

"In ce orase exista furnizori ai florariei?"

SELECT oras

FROM furnizori

GROUP BY oras

In urma executarii interogarii vor apare orasele din fisierul de furnizori listate o singura data.

"Cati furnizori au sediul in fiecare oras?"

SELECT oras, COUNT(*)

FROM furnizori

GROUP BY oras

"In care orase locuiesc cel putin 3 furnizori?"

SELECT oras, COUNT(*)

FROM furnizori

GROUP BY oras

HAVING COUNT(*)>=3

Clauza GROUP BY se poate folosi si cu clauzele ORDER BY si WHERE.

EXEMPLE:

"Sa se afiseze in ordine alfabetica orasele in care exista furnizori ai florariei". (A se observa ca interogarea este asemanatoare cu interogarea 1) din exemplele anterioare. Diferenta consta in faptul ca lista de orase va fi ordonata alfabetic dupa numele oraselor.)

SELECT oras

FROM furnizori

GROUP BY oras

ORDER BY oras

"Care furnizori livreaza in interval de cel mult 17 zile?"

SELECT cod_furnizor

FROM comenzi

WHERE timp_livrare<17

GROUP BY cod_furnizor

4.2.4. Interogari pe mai multe tabele

Una dintre operatiile cele mai frecvente realizate cu mai multe tabele este jonctiunea sau produsul cartezian. Jonctiunea aminteste de operatiile din algebra relationala si chiar este posibil de realizat (urmand anumite structuri ale interogarii SQL) oricare dintre tipurile de jonctiune prezentate teoretic in cadrul algebrei relationale. Se pot de asemenea realiza operatii ca reuniunea, intersectia si diferenta. Sintaxa interogarii SQL difera de la un SGBD la altul dar sub o forma directa sau printr-o constructie sintactica specifica se pot realiza oricare dintre operatiile amintite.

EXEMPLE:

"Sa se afiseze codurile furnizorilor, numele furnizorilor, cantitatile comandate si numerele comenzilor"

SELECT 1.cod_furnizor, nume_furnizor, cantitate, nr_comanda

FROM furnizori 1, comenzi b

WHERE 1.cod_furnizor=b.cod_furnizor

A se observa scrierea cu notarea tuplelor care apartin fiecarei tabele pentru a evita orice confuzie in legatura cu tabela din care se va extrage informatia. Exista doua atribute in tabele diferite care au acelasi nume: atributele cod_furnizor. Modul de notare de mai sus este acceptat de sintaxa SQL si diferentiaza atributul cod_furnizor din tabela furnizori de atributul cod_furnizor din tabela comenzi.

"Sa se afiseze datele de mai sus dar numai pentru furnizorii care au adresa in Brasov"

SELECT 1.cod_furnizor, nume_furnizor, cantitate, nr_comanda

FROM furnizori 1, comenzi b

WHERE 1.cod_furnizor=b.cod_furnizor AND oras='Brasov'

"Ce flori au aceeasi inaltime cu laleaua?" A se observa ca aceasta interogare necesita realizarea produsului cartezian al tabelei FLORI cu ea insasi.

SELECT p1.nume_planta, p2.nume_planta, p1.inaltime, p2.inaltime

FROM flori p1, flori p2

WHERE p1.inaltime=p2.inaltime AND p2.nume_planta='LALEA'

Clauza UNION

Clauza UNION permite realizarea reuniunii de tabele. In cazul cand dorim sa reunim doua sau mai multe tabele, este obligatoriu ca acestea sa fie descrise de scheme de relatie identice (acelasi numar de atribute si corespunzator – de la stanga la dreapta – atributele din tabele au acelasi nume si aceeasi descriere). Aceste conditii sunt impuse tabelelor implicate in operatiile intersectie si minus (diferenta). Operatiile reuniune, intersectie si diferenta de tabele actioneaza analog cu aceleasi operatii aplicate la multimi.

Forma generala a reuniunii de tabele este:

SELECT A1 ,…, Am

FROM

[WHERE …]

UNION

SELECT A1 ,…, Am

FROM …

[WHERE …]

EXEMPLE:

"Sa se afiseze numele plantelor si codurile plantelor care au inaltimea fie mai mica decat 20 cm fie mai mare decat 50 cm."

SELECT nume_planta, cod_produs

FROM flori

WHERE culoare='alb' AND inaltime<20

UNION

(SELECT nume_planta, cod_produs

FROM flori

WHERE culoare='alb' AND inaltime>50)

4.2.5. Subinterogari (clauze SELECT imbricate)

Unul din motivele pentru care SQL este considerat un limbaj puternic de interogare este acela că oferă posibilitatea construirii interogărilor complexe, formate din mai multe subinterogări simple.

Aceste interogări complexe sunt construite prin includerea în clauza WHERE a inca unei clauze SELECT. Forma generala a unei astfel de constructii este:

SELECT < lista atribute1 >

FROM < lista relatii1 >

WHERE < subinterogare >

Se observa ca aceasta constructie a fost deja utilizata in exemplul de mai sus care ilustreaza o clauza UNION.

In constructia de mai sus clauza SELECT interioară generează valorile pentru conditia de căutare a clauzei SELECT exterioare care o contine. Clauza SELECT exterioară generează o relatie pe baza valorilor generate de către clauza interioară. Modul de constuire a interogării exterioare depinde de numărul valorilor returnate de către interogarea interioară .În acest sens, putem distinge:

- subinterogări care returnează o singură valoare

- subinterogări care returnează mai multe valori.

Din punctul de vedere al ordinii de evaluare al interogărilor putem distinge:

subinterogări simple

Interogarea interioară este evaluată prima, independent de interogarea exterioară. Rezultatul evaluării interogării interioare este utilizat de către interogarea exterioară .

subinterogări corelate

Valorile returnate de către interogarea interioară depind de valorile returnate de către interogarea exterioară. Interogarea interioară este evaluată repetat pentru fiecare tuplu cercetat de interogarea exterioara.

Subinterogări simple care returneză o singură valoare

Aceste interogări au următoarea sintaxă:

SELECT < lista atribute >

FROM < lista relatii >

WHERE < atribut > q (< subinterogare >)

unde q este un operator relational: = < > >= <= !=

Vom considera următorul exemplu:

EXEMPLU:

"Să se afiseze numele plantelor care au inaltimea egala cu cea a lalelei".

SELECT nume_planta

FROM flori

WHERE inaltime=

(SELECT inaltime

FROM flori

WHERE nume_planta='LALEA')

Aceeasi interogare poate oferi lista numelor de plante in ordine alfabetica inversa daca se utilizeaza si o clauza ORDER BY.

SELECT nume_planta

FROM flori

WHERE inaltime=

(SELECT inaltime

FROM flori

WHERE nume_planta='LALEA')

ORDER BY nume_planta DESC

Procesul de evaluare a acestei interogări se desfăsoară astfel:

Se evaluează în primul rînd interogarea interioară. Conditia de evaluare a interogării interioare este nume_planta='LALEA'.

Valoarea obtinuta pentru atributul inaltime (sa presupunem ca laleaua are 30 cm) este stocata într-o tabela temporara. Rezultatul evaluării interogării interioare devine conditie de căutare pentru interogarea exterioară, care ar putea fi exprimata in aceasta faza ca:

SELECT nume_planta

FORM flori

WHERE inaltime=30

In urma executarii interogarii exterioare este creată o relatie finală, ce va contine tuplele a căror inaltime este aceeasi cu valoarea stocata în tabela temporară.

Interogarea interioară poate contine în clauza WHERE si conditii complexe, formate prin utilizarea operatorilor logici (NOT, AND, OR) si a functiilor agregat (AVG, MAX, …).

EXEMPLU:

"Sa se afiseze numele plantelor care au inaltimea minima"

SELECT nume_planta

FORM flori

WHERE inaltime=

(SELECT MIN(inaltime)

FROM flori)

Subinterogări simple care returneză mai multe valori

Principiul de construire a acestui tip de interogare imbricată utilizează în clauza WHERE conditii, care evaluate, generează o multime de valori. In această categorie intră operatorii (NOT) IN, (NOT) ANY, (NOT) ALL, (NOT) EXISTS.

EXEMPLE:

"Care furnizori mai au inca de executat livrari?"

SELECT nume_furnizor

FROM furnizori

WHERE cod_furnizor IN

(SELECT cod_furnizor

FROM comenzi

GROUP BY cod_furnizor)

"Care furnizori, dintre furnizorii obisnuiti ai florariei, nu mai au nimic de livrat?"

SELECT nume_furnizor

FROM furnizori

WHERE cod_furnizor NOT IN

(SELECT cod_furnizor

FROM comenzi

GROUP BY cod_furnizor)

A se observa ca cele doua interogari difera doar prin operatorul logic NOT. De asemenea se poate remarca faptul ca prima interogare aminteste de apartenenta din teoria multimilor iar a doua interogare corespunde operatiei minus (diferenta). Daca versiunea SQL nu are un cuvant rezervat pentru operatia diferenta intre tabele, se poate constru aceasta operatie cu ajutorul preicatului NOT IN ca in exemplul de mai sus.

"Care furnizori au numarul maxim de comenzi de flori?"

SELECT nume_furnizor

FROM furnizori

WHERE cod_furnizor IN

(SELECT cod_furnizor

FROM comenzi

GROUP BY cod_furnizor

HAVING COUNT(*)=

(SELECT MAX(COUNT(cod_furnizor))

FROM comenzi

GROUP BY cod_furnizor))

Subinterogări corelate

În exemplele de pană acum, interogarea interioară era evaluată prima, după care valoarea sau valorile rezultate erau utilizate de către clauza WHERE din interogarea exterioară.

Exista si o alt forma de subinterogare si anume subinterogarea corelată, caz în care interogarea exterioară transmite repetat cîte o valoare pentru interogarea interioară.

De fiecare dată cînd este transmisă o valoare, este evaluată interogarea interioară. Dacă ambele interogări accesează acelasi tabel, trebuie asigurate alias-uri pentru fiecare referintă la tabelul respectiv. Ambele interogări acceseză tuple diferite din acelasi tabel în acelasi moment.

EXEMPLU:

"Sa se afiseze culoarea, inaltimea si numele plantelor pentru plantele cu inaltimea maxima, ordonate dupa culoare"

SELECT culoare, inaltime, nume_planta

FROM flori f

WHERE inaltime=

(SELECT MAX(inaltime)

FROM flori

WHERE culoare=f.culoare)

ORDER BY culoare

A se observa ca si interogarea anterioara se poate incadra in cazul ilustrat de exemplul de mai sus.

4.2.6. Operatorii ANY si ALL

Operatorul ANY poate fi utilizat în combinatie cu oricare dintre operatorii relationali (= < > >= <= !=) pentru a se verifica dacă valoarea unui atribut este egala, mai mica, mai mare, etc…decat oricare dintre valorile mentionate odata cu operatorul. Următoarele predicate sunt echivalente :

= ANY si IN

!= ANY si NOT IN

Operatorul ALL returnează tuplele pentru care valorile atributului din clauza WHERE sunt mai mici, mai mari, mai mici sau egale, etc. … decat toate valorile generate de interogarea interioară. Să facem observatia că acest operator nu poate fi utilizat cu operatorul relational =, ceea ce ar corespunde cazului banal în care toate valorile din listă sunt identice:

Pentru a stabili mai exact modul in care se selecteaza valorile cu operatorii ANY si ALL dam mai jos o serie de cazuri concrete.

Sa presupunem ca interogarea este de forma:

SELECT …

FROM …

WHERE x < ALL (1, 2, 3, 4)

Sa observam ca daca inlocuim operatorul ALL cu expresia verbala toate putem citi "Valorile lui x trebuie sa fie mai mici decat toate valorile din paranteza ce urmeaza dupa ALL."

Atunci vom lua in considerare urmatoarele situatii:

Daca vom studia o interogare de forma:

SELECT …

FROM …

WHERE x < ANY (1, 2, 3, 4)

Sa observam ca daca inlocuim operatorul ANY cu expresia verbala oricare putem citi "Valorile lui x trebuie sa fie mai mici decat oricare dintre valorile din paranteza ce urmeaza dupa ALL."

Vom lua in considerare urmatorul tabel:

EXEMPLE:

"Sa se afiseze numele plantelor, pretul unitar si culoarea pentru plantele care au minim 50 cm inaltime si pretul minim din grupul de plante de inaltime sub 50 cm"

SELECT nume_planta, pret_unitar, culoare

FROM flori

WHERE pret_unitar<ALL

(SELECT pret_unitar

FROM flori

WHERE inaltime>=50)

"Sa se afiseze numele plantelor si pretul unitar pentru plante mai mari de 50 cm, din care se exclude cea mai scumpa planta"

SELECT nume_planta, pret_unitar

FROM flori

WHERE inaltime>=50 AND pret_unitar<ANY

(SELECT pret_unitar

FROM flori

WHERE inaltime>=50)

"Sa se afiseze numele plantei si pretul unitar pentru planta cea mai scumpa cu inaltimea mai mare sau egala cu 50 cm"

SELECT nume_planta, pret_unitar

FROM flori

WHERE inaltime>=50 AND NOT pret_unitar<ANY

(SELECT pret_unitar

FROM flori

WHERE inaltime>=50)

4.2.7. Operatorul EXISTS

Operatorul EXISTS verifică dacă pentru fiecare tuplu al relatiei există tuple care satisfac conditia interogării interioare. In cazul în care există asemenea tuple, EXISTS ia valoarea de adevăr TRUE. Astfel, operatorul EXISTS permite specificarea mai multor atribute în interogarea interioară. Acest lucru este posibil deoarece nu se verifică valoarea unui anumit atribut ca în cazurile anterioare, ci se generează o valoare de adevăr (TRUE sau FALSE), după cum există sau nu există o anumită valoare într-o relatie diferită de cea utilizată în interogarea exterioară.

Ca si operatorii ANY si ALL, operatorul EXISTS apare in clauza WHERE.

EXEMPLU: "Care plante au un pret unitar egal sau mai mic cu cea mai ieftina planta de culoare alba?"

SELECT nume_planta, pret_unitar, culoare

FROM flori f

WHERE NOT EXISTS

(SELECT *

FROM flori

WHERE culoare='alb' AND pret_unitar>f.pret_unitar)

Interogarea SELECT interioară defineste o tabela care contine acele tuple pentru care se verifica conditia din clauza WHERE interna. Daca nu exista nici o planta de culoare alba si care sa aiba pretul unitar mai mare decat pretul unitar din tuplul curent, conditia NOT EXISTS este evaluată la valoarea logica TRUE.

Exercitii recapitulative

Se dau urmatoarele relatii cu schemele lor:

-Scari (Nr_bloc, Scara, Lift)

-Apartamente(Nr_bloc,Scara,Apartament,Suprafata,Cutii_postale, Nr_prize_tv)

- Familii (Nr_mat, Nr_pers, Nr_pers_prez, Nr_chei)

Sa se exprime în SQL cererile:

(tabel nominal cu locatarii de pe scara = 3 din bloc = 34) = R1

(tabel nominal cu locatarii de pe scara = 1 din bloc = 34) = R2

(tabel nominal cu locatarii de pe scara = 2 din bloc = 34) = R3

tabel nominal cu locatarii de pe scarile 1,2,3 ale blocului 34

lista apartamentelor cu suprafata mai mare decât 50 mp

tabel nominal cu persoanele carelocuiesc pe scara 3 bloc 34 si nu locuiesc si pe scara 1 a aceluiasi bloc

Raspunsuri la exercitii

select nume

from scari, locatari

where (scari.bloc=34) and(scari.bloc = locatari.bloc)

and (scari.scara=3) and (scari.scara= locatar.scara)

select nume

from scari, locatari

where (scari.bloc=34) and(scari.bloc = locatari.bloc)

and (scari.scara=1) and (scari.scara= locatar.scara)

select nume

from scari, locatari

where (scari.bloc=34) and(scari.bloc = locatari.bloc)

and (scari.scara=2) and (scari.scara= locatar.scara)

select nume

from scari, locatari

where (scari.bloc=34) and(scari.bloc = locatari.bloc)

and (scari.scara=3) and (scari.scara= locatar.scara)

union select nume

from scari, locatari

where (scari.bloc=34) and(scari.bloc = locatari.bloc)

and (scari.scara=1) and (scari.scara= locatar.scara)

union select nume

from scari, locatari

where (scari.bloc=34) and(scari.bloc = locatari.bloc)

and (scari.scara=2) and (scari.scara= locatar.scara)

select Nr_bloc,Scara,Apartament

from apartamente

where suprafata > 50

select nume

from scari, locatari

where (scari.bloc=34) and(scari.bloc = locatari.bloc)

and (scari.scara=3) and (scari.scara= locatar.scara)

and not in ( select nume

from scari, locatari

where (scari.bloc=34) and(scari.bloc locatari.bloc)

and (scari.scara=1) and (scari.scara= locatar.scara))