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))