Selectarea grupurilor. Clauza HAVING

De multe ori nu ne intereseaza sa afisam toate grupurile de obtinute prin folosirea clauzei GROUP BY. Pentru a filtra grupurile folosim clauza HAVING. Asa cum am vazut in exemplele anterioare putem folosi clauza GROUP BY fara clauza HAVING insa clauza HAVING poate fi folosita doar atunci cand este prezenta clauza GROUP BY

Haideti sa analizam un exemplu. Sa presupunem ca dorim sa afisam toti candidatii care au obtinut un procent in alegeri mai mare de 5% din numarul total de persoane cu drept de vot. Pentru aceasta procedam astfel:

folosim clauza GROUP BY pentru a grupa liniile dupa candidati si calculam pentru fiecare candidat procentul obtinut:

SELECT candidat,

100*sum(numar_voturi)/sum(numar_alegatori)

FROM voturi v JOIN judete j

ON v.judet=j.cod_judet

GROUP BY candidat

Tabelul II.4.17.

Folosim clauza HAVING pentru a filtra grupurile care se vor afisa

SELECT candidat,

100*sum(numar_voturi)/sum(numar_alegatori)

FROM voturi v JOIN judete j

ON (v.judet=j.cod_judet)

GROUP BY candidat

HAVING 100*sum(numar_voturi)/sum(numar_alegatori)>5

Tabelul II.4.18.

Bineinteles ca putem folosi clauzele WHERE GROUP BY si HAVING impreuna. In acest caz, clauza WHERE va filtra mai intai liniile din tabela, liniile ramase vor fi grupate apoi conform criteriului dat de clauza GROUP BY si in final sunt afisate doar acele grupuri care respecta conditia data de clauza HAVING. (figura II.4.2.)

Atentie! Trebuie facuta distinctia clara dintre clauzele WHERE si HAVING. Clauza WHERE actioneaza asupra liniilor in timp ce HAVING actioneaza la nivel de grup.

Figura II.4.2. Ordinea de executare a clauzelor comenzii SELECT

Sa vedem de exemplu cum se evalueaza comanda urmatoare

SELECT candidat,

100*sum(numar_voturi)/sum(numar_alegatori)

FROM voturi v JOIN judete j

ON (v.judet=j.cod_judet)

WHERE numar_voturi>15000

GROUP BY candidat

HAVING 100*sum(numar_voturi)/sum(numar_alegatori)>5

Tabelul II.4.19.

Observati insa mai intai ca prin adaugarea clauzei WHERE, rezultatele obtinute difera putin de cele din tabelul II.4.18, aceasta pentru ca la calculul procentului obtinut de catre candidatul 12 de exemplu nu mai este inclusa urmatoarea linie din tabela

Tabelul II.4.20.

Asadar comanda se evalueaza astfel

Mai intai sunt filtrate liniile din tabela

SELECT candidat, numar_voturi, numar_alegatori

FROM voturi v JOIN judete j

ON (v.judet=j.cod_judet)

WHERE numar_voturi>15000

Tabelul II.4.21.

Observati ca au fost afisate doar 11 linii din totalul de 39 cate are tabela.

Liniile obtinute la pasul anterior sunt grupate pe candidati si se aplica functiile de grup

SELECT candidat,

100*sum(numar_voturi)/sum(numar_alegatori)

FROM voturi v JOIN judete j

ON (v.judet=j.cod_judet)

WHERE numar_voturi>15000

GROUP BY candidat

Tabelul II.4.22.

In final sunt afisate doar acele linii obtinute la pasul anterior care indeplinesc conditia din clauza HAVING

SELECT candidat,

100*sum(numar_voturi)/sum(numar_alegatori)

FROM voturi v JOIN judete j

ON (v.judet=j.cod_judet)

WHERE numar_voturi>15000

GROUP BY candidat

HAVING 100*sum(numar_voturi)/sum(numar_alegatori)>5

Tabelul II.4.23.

Subinterogari

Sunteti patronul unei firme. In ultima perioada unul dintre salariatii firmei, pe nume Ionescu, s-a remarcat in mod deosebit prin activitatea sa. Ati decis de aceea sa ii mariti salariul si pentru a decide cu cat sa-l mariti doriti sa aflati care sunt persoanele cu salariu mai mare decat salariul lui Ionescu si care sunt salariile castigate de acestia. Cum faceti acest lucru?

Mai intai veti determina salariul angajatului Ionescu:

SELECT salariul

FROM angajati

WHERE nume='Ionescu'

Sa notam cu S salariul returnat de aceasta comanda. Acum putem afisa foarte simplu angajatii cu salariu mai mare decat S

SELECT nume, prenume

FROM angajati

WHERE salariul>S

Intrebarea care se pune acum este daca nu exista posibilitatea de a uni aceste doua comenzi in una singura. Raspunsul este afirmativ. Vom inlocui in a doua comanda valoarea S cu comanda care a generat aceasta valoare astfel:

SELECT nume, prenume

FROM angajati

WHERE salariul > ( SELECT salariul

FROM angajati

WHERE nume='Ionescu' )

Asadar am inclus prima interogare in interiorul celei de a doua interogari. O astfel de interogare aflata in interiorul unei alte comenzi SQL se numeste subinterogare. Subinterogarile sunt intotdeauna rulate inaintea comenzii in care sunt incluse, doar pe baza rezultatelor returnate de subinterogari putandu-se obtine rezultatele interogarii exterioare subinterogarii.

Un proces similar cu modul de rulare al subinterogarilor este modul in care calculam expresiile cu paranteze (figura II.5.1).

Figura II.5.1.

Subinterogarile sunt in general folosite atunci cand dorim sa afisam informatii dintr-o tabela pe baza unor informatii pe care le preluam din aceeasi tabela sau din alte tabele. De exemplu putem afisa angajatii care lucreaza in acelasi departament cu angajatul X si sunt mai tineri decat persoana X

Exista doua tipuri de subinterogari:

subinterogari simple care returneaza o singura linie;

subinterogari multiple care returneaza mai multe linii si/sau mai multe coloane.

Inainte de a prezenta fiecare din aceste tipuri de subinterogari trebuie sa subliniem cateva restrictii de utilizare a subinterogarilor:

o subinterogare va fi intotdeauna inclusa in paranteza

subinterogarea nu poate contine clauza ORDER BY

Subinterogari simple

Subinterogarile simple, asa cum am precizat, vor returna intotdeauna o singura valoare.

Ele pot sa apara in clauza WHERE sau in clauza HAVING si sunt folosite impreuna cu operatorii < > <= >= <>

Vom prezenta cateva exemple folosind urmatoarele tabele:

Persoane (Id, IdFirma, Nume, Localitate, DataN)

Firme (Id, Nume, Localitate)

Dorim sa afisam toate persoanele care lucreaza la aceeasi firma la care lucreaza si Ionescu:

SELECT Nume FROM persoane

WHERE IdFirma = ( SELECT IdFirma

FROM angajati

WHERE nume = 'Ionescu')

Acelasi rezultat l-am putea obtine cu ajutorul unui selfjoin astfel:

SELECT p.nume

FROM persoane p, persoane i

WHERE p.IdFirma = i.IdFirma AND

i.nume = 'Ionescu'

insa folosirea subinterogarilor este mult mai usoara si mai naturala si in general este mai rapida.

Iata un exemplu de folosire a operatorului <> impreuna cu o subinterogare:

SELECT nume

FROM persoane

WHERE localitatea <> (SELECT localitatea FROM persoane

WHERE nume='Ionescu')

Comanda afiseaza toate persoanele care nu locuiesc in aceeasi localitate cu Ionescu.

Subinterogarile pot folosi functii de grup ca in exemplul urmator:

SELECT nume FROM persoane

WHERE DataN = (SELECT max(DataN) FROM persoane)

Aceasta comanda va afisa cea mai tanara persoana din tabela persoane, data sa de nastere este cea mai mare, adica este cea mai recenta data de nastere.

Similar putem utiliza subinterogarile simple in clauza HAVING. Sa vedem de exemplu cum putem afisa codul firmei cu cei mai multi angajati:

SELECT IdFirma FROM persoane

GROUP BY IdFirma

HAVING count(*) = ( SELECT max(count(*))

FROM persoane

GROUP BY IdFirma )

Subinterogarea determina mai intai numarul maxim de persoane angajate la o firma, iar apoi afiseaza Id-ul firmei care are numarul de angajati egal cu acest maxim.

Atentie! Am fi tentati sa scriem o comanda de forma:

SELECT DISTINCT IdFirma

FROM persoane

WHERE count(*) = ( SELECT max(count(*))

FROM persoane

GROUP BY IdFirma )

dar am precizat in capitolul anterior functiile de grup NU pot sa apara in clauza WHERE

Subinterogarile pot fi imbricate una in alta pe oricate nivele. Numarul maxim de nivele de imbricare a interogarilor este teoretic nelimitat. Singura limitare care poate interveni este data de dimensiunea bufferelor.

In exemplul urmator, am construit o interogare care afiseaza numele firmei care are numarul maxim de angajati. Aceasta interogare foloseste interogarea din exemplul anterior pentru a determina Id-ul firmei cu numar maxim de angajati, iar apoi cauta in tabela firme numele acestei firme.

SELECT nume

FROM firme

WHERE Id = (SELECT IdFirma

FROM persoane

GROUP BY IdFirma

HAVING count(*) = ( SELECT max(count(*))

FROM personae

GROUP BY IdFirma

)

)

Interesant este faptul ca in cadrul unei subinterogari se poate face referire la tabelele din clauza WHERE a interogarii parinte. Astfel daca dorim sa afisam toate persoanele care lucreaza in aceeasi localitate in care si locuiesc vom scrie astfel:

select nume

from persoane p

where localitate = ( select localitate

from firme f

where p.idfirma=f.id

)

Am folosit subinterogarea pentru a afla localitatea in care se gaseste firma la care lucreaza fiecare angajat in parte. Acest tip de subinterogari se numesc subinterogari corelate.

Subinterogari multiple

Am vazut cum putem utiliza subinterogarile simple. Vom studia acum cum utilizam subinterogarile care returneaza mai multe linii. Cand o subinterogare returneaza mai mult de o linie, nu mai este posibil sa folosim operatorii de comparatie < > <= >= <> , deoarece o valoare simpla nu poate fi comparata direct cu un set de valori. Va trebui sa comparam o valoare simpla cu fiecare valoare din setul de valori returnate de subinterogare. Pentru a realiza acest lucru vom folosi cuvintele cheie ANY si ALL impreuna cu operatorii de comparatie, pentru a determina daca o valoare este egala, mai mica sau mai mare decat orice valoare sau decat una din valorile din setul de date returnat de subinterogare.

Pentru a exemplifica modul de folosire a subinterogarilor multiple vom utiliza tabela jucatori cu urmatorul continut: Tabelul II.5.1. Tabela Jucatori

Subinterogari multiple cu operatorul IN

Cum aflam oare numele si localitatea jucatorilor a caror rating este egal cu al unui jucator sub de ani? Vom afla mai intai care sunt ratingurile jucatorilor sub de ani:

SELECT rating

FROM jucatori

WHERE varsta<21

Vom obtine trei valori ale ratingului si anume si respectiv Tabelul II.5.2.

apoi vom afisa persoanele a caror rating este sau

SELECT * FROM jucatori

WHERE rating IN ( 6, 2, 4 )

Rezultatul va fi cel din tabelul II.5.3. Tabelul II.5.3.

Aceste doua comenzi se pot scrie impreuna in una singura prin folosirea unei subinterogari multiple astfel:

SELECT * FROM jucatori

WHERE rating IN ( SELECT rating FROM jucatori

WHERE varsta<21 )

Ce se intampla daca o subinterogare multipla returneaza o valoare nula iar operatorul folosit este IN? De exemplu ce va afisa comanda:

SELECT * FROM jucatori

WHERE rating IN ( SELECT rating FROM jucatori

WHERE localitate='Sibiu')

Mai intai subinterogarea va afisa ratingurile tuturor persoanelor din Sibiu:  Tabelul II.5.4.

deci interogarea anterioara este echivalenta cu

SELECT * FROM jucatori WHERE rating IN ( 3, 4, NULL)

Sau SELECT * FROM jucatori

WHERE rating=3 OR rating=4 OR rating=NULL

insa din comparatia cu NULL nu rezulta nimic (NULL nu poate fi comparat decat cu operatorii IS NULL sau IS NOT NULL in rest nu vom obtine nici un rezultat), asadar se vor afisa doar jucatorii cu ratingul egal cu sau

Tabelul II.5.5.

Daca insa subinterogarea va returna doar o singura valoare nula ca de exemplu comanda:

SELECT rating FROM jucatori

WHERE nume='Ilie'

Tabelul II.5.6.

atunci interogarea exterioara, neavand cu ce alta valoare sa compare, nu va returna nici o linie:

Figura II.5.2.

Subinterogari multiple cu ALL

Fie urmatoarea comanda:

SELECT * FROM jucatori

WHERE rating > ALL ( SELECT rating FROM jucatori

WHERE varsta<21 )

Interogarea interioara returneaza multimea valorilor ratingurilor tuturor persoanelor cu varsta mai mica decat , iar interogarea exterioara va verifica fiecare persoana din tabela pentru a vedea daca ratingul sau este mai mare decat fiecare valoare returnata de catre interogarea interioara.

Interogarea interioara va returna valorile (tabelul II.5.2), deci comanda anterioara este echivalenta cu

SELECT * FROM jucatori

WHERE rating > ALL ( 2, 4, 6 )

sau

SELECT * FROM jucatori

WHERE rating>2 AND rating>4 AND rating>6

In concluzie am afisat toate persoanele al caror rating este mai mare decat ratingul tuturor persoanelor mai mici de 21 de ani.

Deci operatorul >ALL se poate interpreta ca mai mare decat valoarea maxima din multimea de valori returnata de catre subinterogare. Similar operatorul <ALL se poate interpreta ca mai mic decat valoarea minima din multimea valorilor returnate de catre subinterogare

Daca una dintre valorile returnate de catre interogarea interioara este nula atunci interogarea exterioara nu va afisa nici o linie daca este folosita optiunea ALL. Sa vedem un exemplu. Dorim sa afisam toate persoanele cu rating mai mare decat ratingurile tuturor persoanelor din Sibiu:

select * from jucatori

where rating >ALL ( select rating

from jucatori

where localitate='Sibiu' )

Interogarea interioara returneaza urmatoarele valorile si NULL (tabelul II.5.4.) si interogarea exterioara se poate scrie echivalent:

select * from jucatori

where rating>3 AND rating>6 AND rating>NULL

Conditia din clauza where are valoarea true doar daca toate cele trei conditii sunt adevarate. Insa expresia 'rating>NULL' are valoarea NULL, adica nu este nici adevarata nici falsa. Asadar conditia din clauza WHERE nu este adevarata niciodata si comanda nu afiseaza nici o linie.

Subinterogari multiple cu ANY

Daca folosirea optiunii ALL se putea traduce printr-o conditie compusa cu operatorul AND, in cazul optiunii ANY se va putea traduce conditia in alta conditie care foloseste operatorul OR

Fie urmatoarea comanda:

select * from jucatori

where rating >ANY ( SELECT rating FROM jucatori

WHERE varsta<21 )

Am vazut ca interogarea interioara returneaza valorile si (tabelul II.5.2) Comanda exterioara va afisa toti jucatorii care au un rating mai mare decat a oricarui jucator sub 21 de ani, sau altfel spus se afiseaza persoanele cu rating mai mare decat a cel putin unei persoane cu varsta sub de ani.

Tabelul II.5.7.

Putem spune ca operatorul >ANY poate fi interpretat ca mai mare decat valoarea minima din multimea de valori returnata de catre subinterogare. Similar operatorul <ANY se poate interpreta ca mai mic decat valoarea maxima din multimea valorilor returnate catre subinterogare

Daca una din valorile returnate de catre interogarea interioara este nula, interogarea exterioara poate afisa totusi ceva. De exemplu comanda

SELECT * FROM jucatori

WHERE rating >ANY ( SELECT rating FROM jucatori

WHERE localitate='Sibiu' )

va afisa  Tabelul II.5.8.

Acest lucru se intampla deoarece comanda data se poate scrie echivalent

SELECT * FROM jucatori

WHERE rating >ANY ( 3, 4, NULL )

deoarece subinterogarea returneaza valorile si NULL (tabelul II.5.4.), si aceasta comanda se poate scrie si

SELECT * FROM jucatori

WHERE rating>3 OR rating>4 OR rating>NULL

Conditia din WHERE este adevarata daca cel putin una din cele trei conditii este adevarata. Cum ultima conditie, rating>NULL, nu va fi niciodata adevarata, este suficient ca ratingul jucatorului sa fie mai mare decat sau mai mare decat , pentru ca el sa fie afisat.

Daca insa subinterogarea va returna o singura valoare nenula, si nimic altceva, atunci comanda exterioara nu va afisa nimic:

Figura II.5.3.

Modul in care se pot folosi optiunile ANY IN si ALL se pot rezuma in figura II.5.4.

Figura II.5.4.

Echivalentele ce se pot folosi cu aceste optiuni sunt rezumate in tabelul urmator:

Tabelul II.5.9.

Subinterogari multiple cu EXISTS

Putem folosi operatorul EXISTS pentru a verifica daca o subinterogare returneaza vreo linie. De obicei se foloseste acest operator impreuna cu subinterogari corelate. De exemplu comanda urmatoare afiseaza toti angajatii care sunt managerii altor angajati:

SELECT employee_id, first_name, last_name

FROM employees a

WHERE EXISTS (SELECT employee_id FROM employees b

WHERE b.manager_id=a.employee_id)

In subinterogare am determinat angajatii coordonati de catre un angajat afisat de catre interogarea exterioara.

Evident aceasta comanda o putem transcrie cu ajutorul operatorului IN astfel:

SELECT employee_id, first_name, last_name

FROM employees a

WHERE employee_id IN

(SELECT employee_id FROM employees b

WHERE a.employee_id=b.employee_id)

Este destul de usor de dedus ca folosirea operatorului EXISTS ofera performante mai mari intrucat IN compara fiecare valoare returnata de catre interogarea exterioara cu fiecare valoare returnata de subinterogare, pe cand operatorul EXISTS verifica doar existenta a cel putin unei linii returnata de subinterogare, fara a face nici o comparatie.

Subinterogari multiple in clauza FROM

O subinterogare multipla poate fi folosita si in clauza FROM a unei interogari ca in exemplul urmator:

SELECT a.employee_id, first_name, last_name, nrang

FROM employees a, (SELECT manager_id, count(*) nrang

FROM employees GROUP BY manager_id

HAVING count(*)>0) b

WHERE a.employee_id=b.manager_id

care afiseaza id-ul, numele, prenumele si numarul de subalterni ai tuturor managerilor (tabelul II.5.10).

Tabelul II.5.10.

Dupa etapa de modelare a bazelor de date, primul pas in realizarea unei aplicatii de baze de date consta in crearea obiectelor ce compun baza de date: tabele, indexi, vederi, sinonime etc.

Crearea tabelelor, presupune stabilirea numelor tabelelor si a coloanelor ce le compun, stabilirea tipurilor de date pe care le au coloanele tabelei, dar si declararea restrictiilor (constrangerilor) care asigura integritatea si coerenta informatiilor din baza de date.

Crearea tabelelor

Pentru crearea unei tabele se foloseste comanda CREATE TABLE. Cea mai simpla forma a acestei comenzi, in care pentru moment nu se definesc valori implicite pentru coloane si nu definim nici o restrictie este:

CREATE TABLE numetabel

( coloana1 tip1,

coloana2 tip2,

.

coloanan tipn )

unde - numetabel este numele atribuit tabelului nou creat. Acest nume trebuie sa respecte restrictiile privind definirea numelor despre care a discutat in capitolul II.1.

coloana1, coloana2, ., coloanan sunt numele coloanelor din tabela nou creata

tip1, tip2, ., tipn reprezinta tipul datelor ce vor fi retinute in coloanele tabelei nou create si dimensiunea (daca este cazul). Principalele tipurile de date existente in Oracle au fost prezentate in capitolul I.3. Pe langa numele tipului respectiv se precizeaza in paranteza lungimea tipului, respectiv numarul de caractere pentru un sir de caractere, sau numarul total de cifre si numarul de cifre de dupa virgula pentru valorile numerice.

De exemplu, pentru crearea tabelei corespunzatoare entitatii Jucator despre care am discutat in capitolul I.3 folosim comanda:

CREATE TABLE jucatori (

nr_legitimatie NUMBER(3),

nume VARCHAR2(30), prenume VARCHAR2(30),

data_nasterii DATE, adresa VARCHAR2(50),

telefon CHAR(13), email VARCHAR2(30),

cod_echipa NUMBER(3) )

Deocamdata nu am definit cheia primara si cheia straina.

Pentru crearea tabelei ECHIPE folosim comanda:

CREATE TABLE jucatori (

cod NUMBER(3),

nume VARCHAR2(30), localitate VARCHAR2(30),

adresa_club VARCHAR2(50) )

Iata inca un exemplu:

CREATE TABLE elevi (

id NUMBER(5),

nume VARCHAR2(30), prenume VARCHAR2(30),

bursier CHAR(1), media NUMBER(4,2) )

In acest exemplu, pentru tipul campului media s-au precizat doua valori. Prima ( ) reprezinta numarul total de cifre ale numarului, iar al doilea numar reprezinta numarul de cifre zecimale ( ). Daca sunt introduse mai mult de doua zecimale se va face rotunjire la doua zecimale. La partea intreaga pot exista doua cifre. Daca numarul introdus are mai mult de doua cifre la partea intreaga se va semnala o eroare. De asemenea, am declarat un camp bursier, care ne va ajuta sa memoram daca un elev este sau nu bursier. Insa, in Oracle nu exista tipul logic (sau boolean), motiv pentru care am optat pentru tipul CHAR(1), pentru un elev bursier vom memora in acest camp valoarea 'D', pentru ceilalti elevi acest camp ramanand necompletat.

O alta metoda de creare a unei tabele defineste structura pe baza structurii unei tabele deja existente si in acelasi timp copiaza datele din tabela deja existenta. Datele care se copiaza din tabela deja existenta (liniile dar si coloanele ce se copiaza) se precizeaza prin clauza AS urmata de o subinterogare. De exemplu comanda urmatoare creeaza tabela bursieri pe baza tabelei elevi deja existenta:

CREATE TABLE bursieri

AS SELECT id, nume, prenume FROM elevi

WHERE bursier='D'

Se observa ca nu sunt copiate coloanele media si bursier din tabela elevi

Definirea valorilor implicite pentru coloane

Sintaxa comenzii CREATE TABLE prezentata anterior este una mult simplificata. In cadrul acestei comenzi putem utiliza clauza DEFAULT pentru a defini o valoare implicita pentru o coloana a tabelei. Aceasta clauza precizeaza ce valoare va lua un atribut atunci cand, la inserarea unei linii in tabela, nu se specifica in mod explicit valoarea atributului respectiv. Clauza DEFAULT apare dupa precizarea tipului coloanei si este urmata de constanta care defineste valoarea implicita:

CREATE TABLE angajati

( nume varchar2(30), prenume varchar2(30),

adresa varchar2(50) DEFAULT 'Necunoscuta',

localitate varchar2(20) DEFAULT 'Bucuresti',

data_ang date DEFAULT SYSDATE,

salar NUMBER(5) DEFAULT 800 )

Dupa cum se vede in exemplul anterior valoarea implicita poate fi o constanta dar poate fi de asemenea o expresie, sau o una din functiile speciale SYSDATE si USER (care returneaza numele utilizatorului curent) dar nu poate fi numele altei coloane sau al unei functii definite de utilizator.

Pentru o coloana pentru care nu s-a definit o valoare implicita, si nu face parte din cheia primara sau dintr-o restrictie NOT NULL sau UNIQUE (despre care povestim mai tarziu), sistemul va considera ca valoare implicita valoarea NULL