|
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
LOCALITATI
CodPostal
Localitate
Judet
6600
5300
Vrancea
5725
Pascani
6750
Tg. Frumos
CLIENTI
CodClient
NumeClient
Adresa
CodPostal
1001
TEXTILA SA
Bld. Copou, 87
6600
1002
MODERN SRL
Bld. Garii, 22
5300
1003
OCCO SRL
NULL
6600
1004
FILATURA SA
Bld. Unirii, 145
5300
1005
INTEGRATA SA
I.V.Viteazu, 115
5725
1006
AMI SRL
Galatiului, 72
6750
1007
AXON SRL
Silvestru, 2
6600
1008
ALFA SRL
Prosperitatii, 15
5725
FACTURIEMISE
NrFactura
CodClient
Data
ValoareTotala
TVAColectata
111111
1003
17.06.2000
17000000
2714286
111112
1001
17.06.2000
2850000
455042
111113
1004
18.06.2000
5850000
934034
111114
1003
18.06.2000
28500000
4550420
111115
1008
18.06.2000
35700000
5700000
111116
1008
19.06.2000
8700000
1389076
111117
1006
20.06.2000
11000000
1756303
111118
1007
23.06.2000
15000000
2394958
111119
1005
24.06.2000
47250000
7544118
111120
1003
24.06.2000
3000000
478992
111121
1001
24.06.2000
4250000
678571
111122
1007
24.06.2000
8750000
1397059
111123
1006
25.06.2000
6600000
1053782
111124
1004
25.06.2000
38650000
6171008
111125
1003
30.06.2000
12850500
2051761
111126
1002
01.07.2000
54250000
8661765
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
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, R1 si R2, atunci, in lipsa unei conditii de jonctiune formulata in clauza WHERE, tabela rezultat va contine liniile obtinute din produsul cartezian R1 A R2.
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 = '
Rezultat:
CodPostal
Localitate
Judet
6600
5725
Pascani
6750
Tg. Frumos
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
Rezultat:
NrFactura
CodClient
Data
ValoareTotala
TVAColectata
111119
1005
24.06.2000
4 725 000
850 500
111124
1004
25.06.2000
3 850 000
693 000
111126
1002
01.07.2000
5 425 000
976 500
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 (
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
= '
Utilizand operatorul IN:
SELECT *
FROM LOCALITATI
WHERE Judet
IN ('
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, CLIENTI.CodPostal = 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="
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
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 FACTURIEMISE 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. Tabela 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 = '
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.