Comenzi pentru interogarea bazelor de date. fraza select

comenzi pentru interogarea bazelor de date. fraza SELECT

In SQL o interogare se formuleaza printr-o fraza SELECT. Aceasta prezinta trei clauze principale: SELECT, FROM si WHERE.

SELECT corespunde operatorului proiectie din algebra relationala, fiind utilizata pentru desemnarea listei de atribute (coloanele) din tabela-rezultat;

FROM este cea care permite enumerarea relatiilor din care vor fi extrase informatiile aferente consultarii;

 prin WHERE se desemneaza predicatul selectiv al algebrei relationale, relativ la atribute ale relatiilor care apar in clauza FROM.

La modul general o consultare simpla in SQL poate fi prezentata astfel:

SELECT C1, C2, , Cn

FROM R1, R2, , Rm

WHERE P

Executia unei fraze SELECT se concretizeaza in obtinerea unei tabele (relatii) rezultat. Acesta poate fi o tabela propriu-zisa sau o tabela temporara (care, de obicei, nu poate fi actualizata), dar si o tabela derivata (imagine). Uneori tabela rezultat poate fi obtinuta sub 'forma' unei variabile-tablou.

Ci - reprezinta coloanele (care sunt atribute sau expresii de atribute) tabelei-rezultat;

Rj - sunt relatiile ce trebuie parcurse pentru obtinerea rezultatului;

P - este predicatul (conditia) simplu sau compus ce trebuie indeplinit de tupluri pentru a fi incluse in tabela-rezultat.

Cand clauza WHERE este omisa, se considera implicit ca predicatul P are valoarea logica 'adevarat'.

Daca in locul coloanelor C1, C2, Cn apare simbolul '*', in tabela-rezultat vor fi incluse toate coloanele (atributele) din toate relatiile specificate in clauza FROM. De asemenea, in tabela-rezultat, nu este obligatoriu ca atributele sa prezinte nume identic cu cel din tabela enumerata in clauza FROM. Schimbarea numelui se realizeaza prin optiunea AS.

Rezultatul unei fraze SELECT il vom considera ca fiind sub forma unei tabele oarecare. Trebuie avut in vedere, insa, ca rezultatul interogarii poate fi obtinut si sub forma unei tabele temporare sau chiar a unei variabile-tablou (matrice). In unele SGBD-uri, cum ar fi FoxPro, formatul general al frazei SELECT contine si clauza INTO.

SELECT .

FROM .

INTO destinatie

WHERE .

Destinatie specifica daca se va obtine o tabela 'normala', o tabela temporara (tabela care se sterge automat la inchiderea sa) sau o variabila-tablou. Daca clauza INTO nu este utilizata, atunci in urma interogarii se obtine o tabela temporara cu numele predeterminat (Query).

Uneori, tabela rezultat ('normala' sau temporara incalca' poruncile modelului relational. Conform restrictiei de entitate, intr-o relatie nu pot exista doua linii identice. Or, in SQL, tabela obtinuta dintr-o consultare poate contine doua sau mai multe tupluri identice

Spre deosebire de algebra relationala, in SQL nu se elimina automat tuplurile identice (dublurile) din tabela-rezultat. Pentru aceasta este necesara utilizarea optiunii DISTINCT:

SELECT DISTINCT C1, C2, , Cn

FROM R1, R2, , Rm

WHERE P

Figura nr. 6.1. Baza de date utilizata in exemple

In concluzie, o fraza SELECT, in forma in care a fost prezentata, corespunde:

unei selectii algebrice (clauza WHERE - P)

unei proiectii (SELECT - Ci)

unui produs cartezian (FROM - R1 R2 Rm)

si conduce la obtinerea unei noi relatii (tabele-rezultat) cu n coloane, fiecare coloana fiind:

un atribut din R1, R2, , Rm sau

o expresie calculata pe baza unor atribute din R1, R2, , Rm.

In exemplele incluse in acest capitol se va utiliza baza de date prezentata in figura 6.1.

Exemplu

Care este, pentru fiecare factura emisa, valoarea fara TVA ?

SELECT NrFactura, ValoareTotala - TVAColectata AS ValFaraTVA

FROM FACTURIEMISE

Tabela rezultat din figura 6.2 contine doua atribute: NrFactura si ValFaraTVA. Ultimul este un camp calculat.

Figura nr. 6.2. Exemplu de camp calculat (ValFaraTVA)

Interogari care utilizeaza operatorii asamblisti din algebra relationala

Reuniunea

SELECT *

FROM R1

UNION

SELECT *

FROM R2

Operatorul pentru reuniune este deci UNION. De remarcat ca, la reuniune, SQL elimina automat dublurile, deci nu este necesara utilizarea clauzei DISTINCT. Operatorul UNION este prezent in toate SGBD-urile importante.

Intersectia

Pentru realizarea intersectiei a doua tabele, R1 si R2 se utilizeaza operatorul INTERSECT:    

SELECT *

FROM R1

INTERSECT

SELECT *

FROM R2

Daca in produsele profesionale, precum DB2 (IBM) sau Oracle operatorul este prezent, in schimb multe din cele din categoria "usoara", precum Visual Fox Pro, INTERSECT ramane un deziderat, functionalitatea sa realizandu-se prin subconsultari (operatorii IN si EXISTS) sau, uneori, prin jonctiune.

Diferenta

Diferenta dintre tabelele R1 si R2 se realizeaza utilizand operatorul MINUS sau EXCEPT. Fraza SELECT urmatoare functioneaza in Oracle. 

SELECT *

FROM R1

MINUS

SELECT *

FROM R2

In DB2 MINUS trebuie inlocuit cu EXCEPT, iar in Visual FoxPro exista nici MINUS si nici EXCEPT, astfel incat, ca si in cazul intersectiei, este necesar a se recurgere la alti operatori, precum IN sau EXISTS.

Produsul cartezian

In SQL nu exista operator explicit pentru efectuarea produsului cartezian. Daca in clauza FROM apar doua relatii, R₁ si R₂, atunci, in lipsa unei conditii de jonctiune formulata in clauza WHERE, tabela rezultat va contine liniile obtinute din produsul cartezian R₁ A R₂.

SELECT *

FROM R1, R2

Interogari care utilizeaza operatorii relationali din algebra relationala

Selectia

Exemplu 1

Care sunt localitatile din judetul Iasi in care firma are clienti ?

Tabela in care se afla rezultatul si asupra careia se aplica predicatul de selectie este LOCALITATI. Predicatul este Judet = 'Iasi'. Fraza SELECT va avea forma:

SELECT *

FROM LOCALITATI

WHERE Judet = 'Iasi'

Exemplu 2

Care dintre facturile emise dupa 23.06.98 prezinta valoarea mai mare sau egala cu 3 000 000 lei ?

SELECT *

FROM FACTURIEMISE

WHERE Data > AND ValoareTotala >= 3000000

Dupa cum se observa, operatorul AND a fost utilizat pentru a introduce un 'SI' logic, dupa cum OR se utilizeaza pentru "SAU" logic. In SQL, pentru comparare, in afara operatorilor 'clasici' specificati mai sus, pot fi utilizati si alti operatori, dintre care in acest paragraf ne vom opri la:

BETWEEN (intre, cuprins intre),

LIKE (ca si, la fel ca),

IN (in) si

IS NULL.

Operatorul BETWEEN

Acest operator permite specificarea unui interval de valori in care trebuie sa se incadreze campul/expresia testata. Acest interval se refera la valori numerice sau la date calendaristice.

Exemplu 3

Se reformuleaza ultima interogare:

Care sunt facturile emise dupa 23.06.2000 si care au valoarea cuprinsa intre 3 000 000 si 4 000 000 lei ?

Fara operatorul BETWEEN fraza SELECT se scrie:

SELECT *

FROM FACTURIEMISE

WHERE Data > AND

ValoareTotala >= 3000000 AND ValoareTotala <= 4000000

Utilizand operatorul BETWEEN se poate scrie:

SELECT *

FROM FACTURIEMISE

WHERE Data > AND ValoareTotala BETWEEN 3000000 AND 4000000

Operatorul LIKE

Acest operator se foloseste pentru a compara un atribut de tip sir de caractere (ex. NumeClient, Adresa, Localitate) cu un literal (constanta de tip sir de caractere). Astfel, daca se doreste obtinerea unei tabele-rezultat care sa contina numai clientii ai caror nume incepe cu litera M, putem scrie predicatul din clauza WHERE sub forma: NumeClient LIKE 'M%'. Deoarece dupa litera M apare semnul '%', se vor extrage din tabela CLIENTI toate tuplurile pentru care valoarea atributului NumeClient incepe cu litera M, indiferent de lungimea acestuia (ex. MELCRET, MIGAS, MITA, MATSUSHITA etc.). Despre semnul '%' se spune ca este un specificator multiplu, joker sau masca.

Un alt specificator multiplu utilizat in multe versiuni SQL este liniuta de subliniere ('_'). Spre deosebire de '%', '_' substituie un singur caracter. Diferenta dintre cei doi specificatori multipli este pusa in evidenta in exemplele urmatoare.

Exemplu 4

Care sunt clientii ai caror nume este format din sapte caractere, incepe cu litera A si sunt societati cu raspundere limitata (SRL-uri) ?

SELECT *

FROM CLIENTI

WHERE NumeClient LIKE 'A__ SRL'

Rezultatul va fi cel din figura 6.3.

Figura nr. 6.3. Utilizarea specificatorului multiplu '_'

Daca s-ar fi utilizat simbolul '%':

SELECT *

FROM CLIENTI

WHERE NumeClient LIKE 'A%SRL',

rezultatul ar fi fost cel din figura 6.4.

Figura nr. 6.4. Utilizarea specificatorului multiplu '%'

In concluzie, '_' inlocuieste (substituie) un singur caracter, in timp ce '%' inlocuieste un sir de caractere de lungime variabila (intre 0 si n caractere). Cei doi specificatori multipli pot fi utilizati impreuna.

Operatorul IN

Format general:

expresie1IN (expresie2, expresie3, )

Rezultatul evaluarii unui predicat ce contine acest operator va fi 'adevarat' daca valoarea expresiei1 este egala cu (cel putin) una din valorile: expresie2, expresie3, Este util atunci cand conditiile de selectie sunt mai complexe.

Exemplu 6

Care sunt localitatile din judetele Iasi si Vaslui?

Fara utilizarea operatorului IN interogarea se scrie:

SELECT *

FROM LOCALITATI

WHERE Judet = 'Iasi' OR Judet = 'Vaslui'

Utilizand operatorul IN:

SELECT *

FROM LOCALITATI

WHERE Judet IN ('Iasi', 'Vaslui')

Operatorul IS NULL

O valoare nula este o valoare nedefinita. Este posibil ca la adaugarea unei linii intr-o tabela, valorile unor atribute sa fie necunoscute. In aceste cazuri valoarea atributului pentru tuplul respectiv este nula. Reamintim ca, prin definitie, nu se admit valori nule pentru grupul atributelor care constituie cheia primara a relatiei.

Exemplu 7

Daca se doreste aflarea clientilor pentru care nu s-a introdus adresa, interogarea se poate scrie:

SELECT *

FROM CLIENTI

WHERE Adresa IS NULL

Cum in baza noastra de date, numai clientului OCCO SRL nu-i cunoastem adresa, rezultatul interogarii este cel din figura 6.5.

Figura nr. 6.5. Extragerea valorilor NULLe

Observatii

Valoarea NULL nu se confunda cu valoarea zero (pentru atributele numerice) sau cu valoarea 'spatiu' (pentru atributele de tip sir de caractere)

Operatorul NULL se utilizeaza cu IS si nu cu semnul '='. Daca s-ar utiliza forma expresie = NULL si nu expresie IS NULL, rezultatul evaluarii va fi intotdeauna fals, chiar daca expresia nu este nula !

Proiectia. Optiunea ORDER BY

Coloanele tabelei-rezultat al consultarii sunt specificate in clauza SELECT, fiind separate prin virgula.

Exemplu 1

Care sunt judetele in care firma are clienti ?

Este necesara parcurgerea relatiei LOCALITATI, singurul atribut care intereseaza fiind Judet. Deoarece SQL nu elimina dublurile automat, daca se doreste ca in tabela-rezultat o localitate sa figureze o singura data, se utilizeaza optiunea DISTINCT:

SELECT DISTINCT Judet

FROM LOCALITATI

Exemplu 2

Care este denumirea fiecarei localitati si judetul in care se afla ?

SELECT Localitate, Judet

FROM LOCALITATI

Prezentarea localitatilor in ordinea alfabetica a numelui acestora este posibila prin apeland la clauza ORDER BY:

SELECT Localitate, Judet

FROM LOCALITATI

ORDER BY Localitate

Pentru ordonarea liniilor tabelei-rezultat in functie de judet si, in cadrul aceluiasi judet, in ordinea inversa a localitatii (de la Z la A), fraza SELECT se formuleaza astfel:

SELECT *

FROM LOCALITATI

ORDER BY Judet ASCENDING, Localitate DESCENDING

Figura nr. 6.6. Clauza ORDER BY

Optiunile ASCENDING (crescator) si DESCENDING (descrescator) indica deci modul in care se face ordonarea tuplurilor tabelei-rezultat al interogarii.

Prioritatea de ordonare este stabilita prin ordinea atributelor specificate in ORDER BY: ordonarea 'principala' se face in functie de valorile primului atribut specificat; in cadrul grupelor de tupluri pentru care valoarea primului atribut este identica, ordinea se stabileste dupa valoarea celui de-al doilea atribut specificat s.a.m.d.

Daca in ORDER BY lipsesc optiunile ASCENDING/DESCENDING, ordonarea se face crescator.

onctiunea

SQL nu prezinta clauze sau operatori speciali pentru realizarea theta-jonctiunii, echi-jonctiunii sau jonctiunii naturale. Dar, asa cum am vazut, o jonctiune este o combinatie de produs cartezian si selectie.

Exemplu 1

Revenind la exemplele din algebra relationala, echi-jonctiunea tabelelor FURNIZOR1 si FURNIZOR2 in SQL se realizeaza prin fraza SELECT:

SELECT *

FROM FURNIZOR1, FURNIZOR2

WHERE FURNIZOR1.CodF = FURNIZOR2.CodF

Observatie:

In SQL2, echijonctiunea poate fi realizata prin clauza INNER JOIN plasata in clauza FROM. Astfel, ultima fraza SELECT se poate redacta, in SQL2, fara clauza WHERE:

SELECT *

FROM FURNIZOR1 INNER JOIN FURNIZOR2 ON

FURNIZOR1.CodF = FURNIZOR2.CodF

Exemplu 2

Care sunt clientii din municipiul Focsani ?

SELECT *

FROM CLIENTI INNER JOIN LOCALITATI

ON CLIENTI.CodPostal = LOCALITATI.CodPostal

WHERE Localitate="Focsani"

Produsul cartezian al tabelelor CLIENTI si LOCALITATI este prezentat in figura 6.7.

Figura nr. 6.7. Produsul cartezian CLIENTI A LOCALITATI

Din cele 32 de linii sunt selectate cele care indeplinesc conditia de jonctiune, CLI­ENTI.Cod­Pos­tal = LOCALITATI.CodPostal, si pe cea suplimentara - Localitate="Focsani". In final, rezultatul este cel din figura 6.8.

Figura nr. 6.8. Rezultatul final al jonctiunii si al selectiei suplimentare

Exemplu 3

Care sunt facturile emise clientilor din judetul Iasi ?

SELECT NrFactura

FROM FACTURIEMISE, CLIENTI, LOCALITATI

WHERE FACTURIEMISE.CodClient = CLIENTI.CodClient

AND CLIENTI.CodPostal = LOCALITATI.CodPostal

AND Judet="Iasi"

Solutia este echivalenta cu urmatoarea:

SELECT NrFactura

FROM FACTURIEMISE FE INNER JOIN CLIENTI C

ON FE.CodClient = C.CodClient

INNER JOIN LOCALITATI L

ON C.CodPostal = L.CodPostal

WHERE Judet="Iasi"

Exemplu 4

Care sunt facturile emise in aceeasi zi ca si factura 111113 ?

Problema propusa poate fi rezolvata relativ usor folosind o subconsultare, dupa cum va fi prezentat in paragraful urmator. Pana una-alta, solutia pe care o avem in acest moment la indemana se bazeaza pe autojonctiune. Autojonctiune inseamna jonctiunea unei tabele cu ea-insasi, practic, jonctiunea a doua instante ale unei aceleasi tabele:

SELECT FE1.NrFactura, FE1.Data

FROM FACTURIEMISE FE1 INNER JOIN FACTURIEMISE FE2

ON FE1.Data= FE2.Data AND FE2.NrFactura=111113

Solutia de mai sus conduce la rezultatul din figura 6.9. Sa vedem prin ce mecanism.

Figura nr. 6.9. Facturile emise in aceeasi zi ca si 111113

Jonctiunea celor doua instante, FE1 si FE2, ale tabelei FACTURIEMISE dupa conditia FE1.Data = FE2.Data:

SELECT *

FROM FACTURIEMISE FE1 INNER JOIN FACTURIEMISE FE2

ON FE1.Data= FE2.Data

conduce la un rezultat precum cel din figura 6.10.

Figura nr. 6.10. Auto-jonctiunea, dupa valorile Data, tabelei FACTURIEMISE

Din cele 38 de linii, prin predicatul FE2.NrFactura=111113 raman numai 3, cele din figura 6.9.

Exemplu 5

Care sunt clientii carora NU le-am intocmit facturi pe 18/06/2000 ?

La aceasta problema se pot formula mai multe solutii. Una ar fi bazata pe diferenta dintre toti clientii (extrasi din tabela CLIENTI) si cei carora le-am trimis facturi pe 18 iunie. Tinand seama ca numele clientului este cheie alternativa, deci unic, se poate scrie:

SELECT NumeClient

FROM CLIENTI

MINUS

SELECT NumeClient

FROM CLIENTI INNER JOIN FACTURIEMISE

ON CLIENTI.CodClient=FACTURIEMISE.CodClient AND Data=

O asemenea solutie functioneaza in Oracle (folosind functia de conversie TO_DATE pentu constanta), DB2 (inlocuind, in plus, MINUS cu EXCEPT), nu insa si in Visual FoxPro. Avand in vedere ca nu avem cunostinte privind subconsultarile, putem recurge la un artificiu bazat pe o forma interesanta a jonctiunii, si anume jonctiunea externa.

Sa examinam fraza SELECT urmatoare si rezultatul acesteia din figura 6.11.

SELECT *

FROM CLIENTI C LEFT OUTER JOIN FACTURIEMISE FE

ON C.CodClient=FE.CodClient AND Data=

Figura nr. 6.11. O jonctiune externa

Prima observatie: in rezultat sunt incluse toti clientii, adica, toate inregistrarile din tabela CLIENTI. A doua observatie: jonctiunea nu mai este de tip INNER si LEFT OUTER, adica externa la stanga. Cum dintre CLIENTI si FACTURIEMISE, cea de la stanga este prima, rezulta ca in rezultat sunt extrase toate liniile din aceasta, chiar daca nu au linii corespondente in tabela din dreapta. In cazul nostru, TEXTILA SA, MODERN SRL, INTEGRATA SA, AMI SRL si AXON SRL nu au facut "cumparaturi" de la firma noastra pe 18 iunie 2000. Ne dam seama de acest lucru observand ca pe liniile coresponde acestora, valorile atributelor preluate din FAC­TURI­­EMISE sunt NULL.

Astfel incat, pentru a raspunde punctual la problema pusa, ar trebui extrase liniile in care, in urma jonctiunii externe, FE.NrFactura (sau oricare alt atribut din FE) este NULL. Paradoxal sau nu, fraza urmatoare nu obtine rezultatul scontat in VFP:

SELECT *

FROM CLIENTI C LEFT OUTER JOIN FACTURIEMISE FE

ON C.CodClient=FE.CodClient AND Data=

WHERE FE.NrFactura IS NULL

In schimb, se poate folosi un artificiu prin intrebuintarea functiei NVL:

SELECT *

FROM CLIENTI C LEFT OUTER JOIN FACTURIEMISE FE

ON C.CodClient=FE.CodClient AND Data=

WHERE NVL(FE.NrFactura,0) = 0

Functia NVL converteste valorile nule ale atributului NrFactura in 0. Principial, solutia bazata pe NVL are acelasi mecanism ca si precedenta interogare. Cu singura diferenta ca. functioneaza, dupa reiese din figura 6.12.

Figura nr. 6.12. Clientii fara facturi pe 18 iunie 2000

Fireste, pentru a obtine numai numele clientilor, este necesara inlocuirea asteriscului din clauza SELECT cu atributul NumeClient.

Ar mai fi de adaugat ca exista trei tipuri de jonctiune externa: la stanga (LEFT OUTER JOIN), la dreapta (RIGHT OUTER JOIN) si totala (FULL OUTER JOIN). La jonctiunea externa la dreapta sunt extrase liniile echi-jonctiunii plus liniile tabelei din dreapta ce nu indeplinesc conditia formulata prin predicatul de jonctiune. Jonctiunea externa totala reprezinta, in fapt, reuniunea (cu eliminarea dublurilor) jonctiunilor la stanga si la dreapta.

Sub-consultari. Operatorul IN

O alta facilitate deosebit de importanta a limbajului SQL o constituie posibilitatea includerii (imbricarii) a doua sau mai multe fraze SELECT, astfel incat pot fi formulate interogari cu mare grad de complexitate.

Operatorul IN poate fi utilizat si pentru includerea unei fraze SELECT intr-o alta fraza SELECT.

Exemplu 1

Care sunt facturile emise in aceeasi zi in care a fost intocmita factura 111113 ?

SELECT *

FROM FACTURIEMISE

WHERE Data IN

(SELECT Data

FROM FACTURIEMISE

WHERE NrFactura=111113)

Sub-consultarea

SELECT Data

FROM FACTURIEMISE

WHERE NrFactura=111114

are ca rezultat o tabela alcatuita dintr-o singura coloana (Data) si o singura linie ce contine valoarea atributului Data pentru factura 111113, ca in figura 6.13:

Figura nr. 6.13. Rezultatul sub-consultarii

Clauza WHERE Data IN determina cautarea in tabela FACTURIEMISE a tuturor tuplurilor (liniilor) care au valoarea atributului Data egala cu una din valorile tuplurilor (in cazul nostru, egala cu valoarea tuplului) din tabela obtinuta prin 'sub-consultare' (in cazul nostru, tabela din figura 6.13). Cu alte cuvinte, in acest caz WHERE Data IN va selecta toate facturile pentru care data emiterii este 18/06/2000 - figura 6.14.

Figura nr. 6.14. Facturile emise in aceeasi zi ca si 111113

Exemplu 2

Care sunt facturile emise in alte zile decat cea in care a fost intocmita factura 111113?

SELECT *

FROM FACTURIEMISE

WHERE Data NOT IN

(SELECT Data

FROM FACTURIEMISE

WHERE NrFactura=111113)

S-a utilizat negatia, testandu-se non-apartenenta la o relatie creata printr-o sub-fraza SELECT (vezi figura nr. 6.15).

Figura nr. 6.15. Facturile emise in alte zile decat factura 111113

Exemplu 3

Care sunt clientii carora li s-au trimis facturi intocmite in aceeasi zi cu factura 111113?

SELECT DISTINCT NumeClient

FROM CLIENTI

WHERE CodClient IN

(SELECT CodClient

FROM FACTURIEMISE

WHERE Data IN

(SELECT Data

FROM FACTURIEMISE

WHERE NrFactura=111113))

Am ilustrat modul in care pot fi imbricate (inlantuite, incluse) trei fraze SELECT. Solutia este valabila in SGBD-urile profesionale (DB2, Oracle.), nu insa si in VFP, in care orice interogare poate fi desfasutata pe maximum doua nivele (SELECT-ul principal, plus un nivel de sub-consultari). Pentru a reduce numarul "straturilor" de corelare, se poate folosi, in acest caz, jonctiunea:

SELECT DISTINCT NumeClient

FROM CLIENTI, FACTURIEMISE

WHERE CLIENTI.CodClient=FACTURIEMISE.CodClient

AND Data IN

(SELECT Data

FROM FACTURIEMISE

WHERE NrFactura=111113)

Rezultatul din figura 6.16 demonstreaza ca solutia este viabila.

Figura nr. 6.16. Clienti pentru care exista macar o factura

intocmita in aceeasi zi cu 111113

Se poate retine, ca regula generala, ca aproape orice consultare poate fi redactata in mai multe moduri, in functie de experienta si imaginatia celui care o formuleaza.

Functii de agregare: COUNT, SUM, AVG, MAX, MIN

Formatul general al unei fraze SELECT ce contine functii predefinite este:

SELECT functia-predefinita1, , functia-predefinitaN

FROM lista-tabele

WHERE conditii

Rezultatul oricarei fraze SELECT este o noua relatie (tabela). In lipsa optiunii GROUP BY, daca in clauza SELECT este prezenta o functie predefinita, tabela rezultat va contine o singura linie.

Functia COUNT contorizeaza valorile unei coloane, altfel spus, numara, intr-o relatie, cate valori diferite de NULL are coloana specificata.

Exemplu 1

Cati clienti are firma ?

SELECT COUNT (CodClient) AS Nr_Clienti

FROM CLIENTI

In functia COUNT se poate utiliza ca argument, in locul numelui unei coloane, semnul *; in acest caz se va determina cate linii are tabela la care se aplica functia respectiva.

Exemplu 2

La cati clienti s-au trimis facturi ?

SELECT COUNT (

FROM CLIENTI

WHERE CodClient IN

(SELECT CodClient

FROM FACTURIEMISE)

Rezultatul corect poate fi insa obtinut si prin utilizarea clauzei DISTINCT astfel:

SELECT COUNT (DISTINCT CodClient)

FROM FACTURIEMISE

Functia SUM calculeaza suma valorilor unei coloane.

Exemplu 3

Care este valoarea totala a facturilor emise ?

SELECT SUM (ValoareTotala) AS Total_FP

FROM FACTURIEMISE

Figura 6.17. Totalul vanzarilor

Exemplu 4

Care este totalul valorii facturilor trimise clientului AXON SRL ?

SELECT SUM (ValoareTotala) AS Total_FE_AXON

FROM FACTURIEMISE, CLIENTI

WHERE FACTURIEMISE.CodClient = CLIENTI.CodClient

AND NumeClient = 'AXON SRL'

Functiile MAX si MIN. Determina valorile maxime, respectiv minime ale unei coloane in cadrul unei tabele.

Exemplu 5

Care este cea mai mica valoare a unei facturi emise ?

SELECT MIN(ValoareTotala)

FROM FACTURIEMISE

Exemplu 6

Care este factura emisa ce are cea mai mare valoare ?

SELECT NrFactura, ValoareTotala

FROM FACTURIEMISE

WHERE ValoareTotala =

(SELECT MAX (ValoareTotala)

FROM FACTURIEMISE)

Subconsultarea extrage valoarea totala maxima a unei facturi, valoare ce va fi utilizata ca argument pentru SELECT-ul principal. Rezultatul este cel din figura 6.18.

Figura nr. 6.18. Factura cea mai valoroasa

Atentie ! Varianta urmatoare nu este corecta:

SELECT NrFactura, MAX(ValoareTotala )

FROM FACTURIEMISE

Daca in Oracle sau DB2 la executia acestei interogari se afiseaza un mesaj de eroare, in Visual FoxPro nu, asa ca unii utilizatori s-ar putea baza pe rezultatul afisat.

Gruparea tuplurilor. Clauzele GROUP BY si HAVING

SQL permite utilizarea clauzei GROUP BY pentru a forma grupe (grupuri) de tupluri ale unei relatii, pe baza valorilor comune ale unei coloane. In frazele SELECT formulate pana in acest paragraf, prin intermediul clauzei WHERE au fost selectate tupluri din diferite tabele.

Prin asocierea unei clauze HAVING la o clauza GROUP BY este posibila selectarea anumitor grupe de tupluri ce indeplinesc un criteriu.

Rezultatul unei fraze SELECT ce contine clauza GROUP BY este o tabela care va fi obtinuta prin regruparea tuturor liniilor din tabelele enumerate in FROM, care prezinta o aceeasi valoare pentru o coloana sau un grup de coloane.

Formatul general este:

SELECT coloana 1, coloana 2, ., coloana m

FROM tabela

GROUP BY coloana-de-regrupare

Exemplu 1

Care este totalul zilnic al valorii facturilor emise ?

SELECT Data, SUM (ValoareTotala) AS Total_Zilnic

FROM FACTURIEMISE

GROUP BY Data

In acest caz tabela-rezultat va avea un numar de linii egal cu numarul de date calendaristice distincte din tabela FACTURIEMISE. Pentru toate facturile aferente unei zile se va calcula suma valorilor, datorita utilizarii functiei SUM(ValoareTotala).

Succesiunea pasilor este urmatoarea:

1. Se ordoneaza liniile tabelei FACTURIEMISE in functie de valoarea atributului Data - figura 6.19.

Figura nr. 6.19. Pasul 1 al gruparii

2. Se formeaza cate un grup pentru fiecare valoare distincta a atributului Data - vezi figura 6.20.

Figura nr. 6.20. Al doilea pas al gruparii

3. Pentru fiecare din cele noua grupuri se calculeaza suma valorilor atributului ValoareTotala. Ta­be­la rezultat va avea noua linii, ca in figura 6.21.

Figura nr. 6.21. Rezultatul final al gruparii

Exemplu 2

Care este numarul facturilor emise pentru fiecare client ?

SELECT NumeClient, COUNT(NrFactura)

FROM FACTURIEMISE INNER JOIN CLIENTI

ON FACTURIEMISE.CodClient = CLIENTI.CodClient

GROUP BY FACTURIEMISE.CodClient

Pana la standardul SQL99 si publicarea Amendamentului OLAP la acest standard, in SQL nu pot fi calculate, prin GROUP BY, subtotaluri pe mai multe niveluri. Pentru aceasta este necesara scrierea de programe in SGBD-ul respectiv.

Clauza HAVING permite introducerea unor restrictii care sunt aplicate grupurilor de tupluri, deci nu tuplurilor 'individuale', asa cum 'face' clauza WHERE. Din tabela rezultat sunt eliminate toate grupurile care nu satisfac conditia specificata.

Clauza HAVING 'lucreaza' impreuna cu o clauza GROUP BY, fiind practic o clauza WHERE aplicata acesteia.

Formatul general este:

SELECT coloana 1, coloana 2, . , coloana m

FROM tabela

GROUP BY coloana-de-regrupare

HAVING caracteristica-de-grup

Exemplu 3

Pentru facturile emise intereseaza valoarea zilnica a acestora (in functie de data la care au fost intocmite, dar numai daca aceasta (valoarea zilnica) este de mai mare de cinci milioane lei.

SELECT Data, SUM(ValoareTotala)

FROM FACTURIEMISE

GROUP BY Data

HAVING SUM(ValoareTotala) > 15000000

La executia acestei fraze, se parcurg cei trei pasi prezentati la exemplul 1, apoi, din cele noua tupluri obtinute prin grupare, sunt extrase numai cele care indeplinesc conditia SUM(ValoareTotala)>15000000. Rezultatul final este cel din figura 6.22.

Figura 6.22. Rezultatul consultarii - exemplul 3

Exemplu 4

Sa se afiseze ziua in care s-au intocmit cele mai multe facturi

SELECT Data

FROM FACTURIEMISE

GROUP BY Data

HAVING COUNT(*) >= ALL

(SELECT COUNT(*)

FROM FACTURIEMISE

GROUP BY Data)

Din pacate, nici acest tip de interogare (prezenta subconsultarilor in clauza HAVING) nu este agreat de Visual FoxPro, astfel incat este necesara utilizarea mai multor fraze SELECT si salvarea rezultatelor intermediare fie in tabele derivate (view-uri), fie in cursoare (NR_PE_ZILE) care, in VFP sunt tabele temporare a caror viata este limitata de inchiderea lor, explicita sau implicita:

SELECT Data, COUNT(*) AS Nr ;

FROM FACTURIEMISE ;

INTO CURSOR NR_PE_ZILE ;

GROUP BY Data                       

SELECT Data, Nr ;

FROM NR_PE_ZILE ;

WHERE Nr >= ;

(SELECT MAX(Nr) ;

FROM NR_PE_ZILE)

Continutul cursorului NR_PE_ZILE, precum si rezultatul final sunt cele din figura 6.23.

Figura 6.23. Obtinerea in VFP a zilei cu cele mai multe facturi

Comenzi pentru actualizarea bazelor de date

SQL prezinta comenzi specifice pentru modificarea continutului unei tabele, intelegand prin aceasta trei actiuni prin care se actualizeaza baza:

a) adaugarea de noi linii la cele existente intr-o tabela,

b) stergerea unor linii,

c) modificarea valorii unui atribut.

Adaugarea de inregistrari

Exemplu 1

Sa presupunem ca, la un moment dat, intreprinderea vinde produse si firmei RODEX SRL care are sediul pe strada Sapientei, nr.44 bis, in localitatea Iasi.

Acest nou client trebuie 'introdus' in baza de date, operatiune care in SQL, se realizeaza prin comanda:

INSERT

INTO CLIENTI

VALUES (1009, 'RODEX SRL', 'Sapientei, 44 bis', '6600')

Fraza INSERT de mai sus poate fi scrisa si sub forma

INSERT

INTO CLIENTI (CodClient, NumeClient, Adresa, CodPostal)

VALUES (5009, 'RODEX SRL', 'Sapientei 44 bis', 6600).

Dupa cum se observa, dupa numele tabelei (CLIENTI) au fost enumerate toate atributele pentru care se introduc valori prin clauza VALUES. Daca nu s-ar fi cunoscut adresa clientului RODEX, atunci fraza INSERT ar fi avut una din formele:

INSERT

INTO CLIENTI (CodClient, NumeClient, Adresa, CodPostal)

VALUES (5009, 'RODEX SRL', NULL, '6600')

sau

INSERT

INTO CLIENTI (CodClient, NumeClient, CodPostal)

VALUES (5009, 'RODEX SRL', '6600')

In noua linie a tabelei CLIENTI valoarea atributului Adresa va fi NULL.

Stergerea de inregistrari

Operatiunea de eliminarea a una sau mai multe linii dintr-o tabela, pe baza unui predicat, se realizeaza in SQL prin comanda DELETE care are sintaxa:

DELETE

FROM nume-tabela

WHERE predicat

Exemplu 2

Sa se elimine din tabela CLIENTI linia aferenta clientului MODERN SRL (cod 1002).

DELETE

FROM CLIENTI

WHERE CodClient = 1002

Exemplu 3

Sa se stearga datele referitoare la fiecare vanzare de produs pentru clientii din orasul Focsani.

DELETE

FROM FACTURIEMISE

WHERE CodClient IN

(SELECT CodClient

FROM CLIENTI, LOCALITATI

WHERE CLIENTI.CodPostal=LOCALITATI.CodPostal AND

Localitate = 'Focsani'))

Nici aceasta forma nu functioneaza in VFP. In general, stergerea unor linii trebuie privita cu multa circumspectie, deoarece atunci cand linia de sters contine valori ale unor atribute ce apar in alte tabele ca si chei straine, exista riscul pierderii integritatii referentiale.

Standardul SQL92 (nu si dialectul SQL din VFP) permite la crearea unei tabele descrierea actiunii care se va derula la stergerea unei linii parinte in cazul in care exista linii-copil. Spre exemplu, se poate refuza stergerea de linii din tabela CLIENTI, daca la crearea tabelei FACTURIEMISE se specifica:

CREATE TABLE FACTURIEMISE

(NrFactura DECIMAL(8) NOT NULL,

DataFactura        DATE,

CodClient DECIMAL(6) NOT NULL,

ValoareTotala DECIMAL(15) not NULL,

TVAColectata DECIMAL(14) ,

PRIMARY KEY (NrFactura),

FOREIGN KEY (CodClient) REFERENCES CLIENTI

ON DELETE RESTRICT)

Modificarea valorilor unor atribute

Pentru modificarea valorilor unuia sau multor atribute dintr-o tabela, comanda utilizata este UPDATE care are formatul general:

UPDATE tabela

SET atribut = expresie

WHERE predicat

Ca rezultat, vor fi modificate valorile atributului specificat, noile valori ale acestuia fiind cele care rezulta in urma evaluarii expresiei; modificarea se va produce pe toate liniile tabelei care indeplinesc conditia specificata in predicat.

Exemplu 4

In tabela CLIENTI, fiecarui client i-a fost atribuit un cod unic, incepand cu 1001. Daca din diferite motive, se doreste ca 'numerotarea' clientilor sa inceapa de la 3001, pastrandu-se ordinea, se poate articula fraza UPDATE urmatoare:

UPDATE CLIENTI

SET CodClient = CodClient + 2000

Exemplul este suficient de neinspirat, deoarece in practica modificarea codului clientilor antreneaza modificarea valorilor acestui atribut in toate tabelele in care apare (in cazul nostru FACTURIEMISE), datorita faptului ca este o cheie straina.