Prezentarea generala a LIMBAJUL SQL

Prezentarea generala a LIMBAJUL SQL

SQL (Structured Query Language) este implementat de aproape toate sistemele de gestiune a bazelor de date relationale. In anul 1976 a fost publicata sintaxa completa a limbajului SQL care a fost adoptat de ANSI, in anul 1986 ca limbaj standard pentr lucrul cu baze de date relationale. SQL este un limbaj simplu si in consecinta accesibil utilizatorilor. Se fundamenteaza in principal pe conceptul de bloc de cerere care se poate formaliza astfel:

<bloc de cerere>

lista de atribute

din lista tabele ale bazei de date

criteriu de indeplinit <expresie>

unde: lista de atribute cuprinde atribute din structura tabelei sursa sau atribute calculate pe baza celor existente in tabela/tabele sursa.

Limbajul SQL pune la dispozitia utilizatorilor urmatoarele categorii de comenzi:

A.    Comenzi SQL de descriere a datelor, permitand definirea tabelelor reale si virtuale ale BD, modificarea structurii tabelelor, stergerea tabelelor si a indecsilor :

CREATE TABLE, CREATE VIEW, ALTER TABLE, DROP TABLE, DROP INDEX

B.    Comenzi SQL de manipulare a datelor, permitand inserarea, stergerea si actualizarea de inregistrari:

INSERT, DELETE, UPDATE

C.    Comenzi SQL de interogare a datelor, permitand regasirea, selectarea si afisarea a datelor: SELECT

D.    Comenzi SQL de control a datelor, permitand atribuirea si/sau revocarea dreptului de acces la date: GRANT, REVOCE.

A.    Comenzi SQL de descriere a datelor

Crearea tabelelor se realizeaza prin lansarea comenzii CREATE TABLE prezentand urmatoarea sintaxa:

CREATE TABLE <nume tabela>

([atribut1] tip_atribut1 (dimensiune)[,[atribut2] . .])

CONSTRAINT nume_index ;

unde:

atribut1, atribut2, . sunt campurile tabelei;

tip atribut1, tip atribut2, . specifica tipul campurilor;

primary key permite specificarea campului cheie primara pe care se construieste un index.

Exemplu:

CREATE TABLE Produse (Cod_Produs Text(4), Den_Produs Text(15), UM Text(4), Pret Number, Calitate Text(1) CONSTRAINT Cod_Produs PRIMARY KEY (Cod_Produs));

Modificarea structurii unei tabele prin adaugarea unui nou camp vom folosi comanda ALTER TABLE:

ALTER TABLE nume_tabela ADD COLUMN nume_atribut tip [NOT NULL], . ;

Exemplu:

ALTER TABLE Produse ADD COLUMN Perisabilitati Number

Stergerea unei tabele se realizeaza cu comanda DROP TABLE avand sintaxa:

DROP TABLE nume_tabela;

Crearea tabelelor virtuale, view-uri, permite definirea unor "ferestre" prin care putem "privi" in tabele sau, altfel spus, putem vizualiza datele dorite dintr-o tabela specificata.

CREATE VIEW nume_view [(nume_atribut), . ] AS cerere;

Exemplu:

Create view prod as select * from produse where pret>

Aceste view-uri, odata definite, pot fi utilizate oricand drept sursa a unei cereri, definitia lor fiind retinuta in dictionarul bazei de date.

B.    Comenzi SQL de interogare a datelor

O cerinta a utilizatorului de selectare a datelor din baza de date relationala se exprima prin ceea ce am numit un bloc de cerere.

Un bloc de cerere in SQL prezinta urmatoarea sintaxa simplificata:

SELECT [ALL/DISTINCT/DISTINCTROW] lista_atribute

FROM nume_tabele

[WHERE criteriu_de_cautare]

[GROUP BY atribut_grupare]

[HAVING criteriu_de_grupare]

[ORDER BY criteriu_de_ordonare[ASC/DESC]];

unde:

lista_atribute     - specifica atribute ale caror valori vor fi returnate;

nume_tabele - specifica tabelele din care se vor extrage datele;

ALL   - specifica returnarea tuturor inregistrarilor care indeplinesc conditile precizate in blocul de cerere;

DISTINCT - elimina inregistrarile care prezinta valori duplicate continute in atributele specificate;

DISTINCTROW    - vizeaza inregistrari duplicate care nu vor fi returnate in urma executarii cererii;

WHERE - permite, prin precizarea unei expresii, exprimarea criteriului de selectie;

ORDER BY   - precizeaza atributul dupa care se va face ordonarea;

GROUP BY   - folosit pentru a imparti o tabela in grupuri, acordand acestora valori pe un atribut sau lista de atribute;

HAVING    - folosit pentru a specifica criterii de selectie pe grupuri de inregistrari.

Operatori utilizati in blocurile de cereri

In construirea blocurilor de cereri se pot folosi urmatorii operatori:

operatori aritmetici: +, -, *, /, etc;

operatori logici: and, or, not;

operatori de atribuire si comparare: <, >, =, <=, >=, <>

alti operatori: in, as, like, between, etc.

Blocuri de cerere simple

Acestea permit vizualizarea inregistrarilor continute in cadrul unei tabele, realizarea proiectiilor si selectiilor asupra unei tabele, returnarea unor atribute calculate.

Exemplu:

a) Daca dorim sa vizualizam toate inregistrarile tabelei Produse, blocul de cerere va prezenta urmatoarea forma (* precizeaza returnarea realizarilor tuturor atributelor si nu doar a unora dintre ele):

Select distinctrow * from produse;

b) Daca se doreste afisarea realizarilor doar pentru anumite atribute din structura tabelei, in cazul nostru a denumirii si pretului produselor, blocul de cerere va prezenta urmatoarea forma:

Select distinctrow den_produs, pret from produse;

c) Daca doriti sa aflati ce calitate au produsele achizitionate, puteti construi urmatorul bloc de cerere:

Select distinctrow calitate from produse;

Inconvenientul este faptul ca toate realizarile celor n inregistrari ale tabelei, pentru atributul calitate, sunt returnate. Acelasi rezultat il obtinem si daca inlocuim in blocul de cerere clauza distinctrow cu all. Pentru a extrage valorile neduplicate trebuie folosita clauza distinct.

d) In cazul in care doriti sa vizualizati doar numele si pretul produselor avind pretul produselor avind pretul mai mare de 10000 vom avea:

Select distinctrow den_produs, pret  from produse where pret>

e) Daca doriti sa aflati care din produsele de calitate a doua au preturi mai mari de 10000:

Select distinctrow * from produse where pret>10000 and calitate=2;

f)  In cazul in care doriti sa cunoasteti denumirea produselor cu preturi inre 9000 si 11000 lei vom avea doua posibilitati:

Select den_produs from produse where pret>=9000 and pret<=11000;

Sau

Select den_produs from produse where pret between 9000 and

g) Daca doriti sa cunoasteti produsele care au unul din preturile 11000, 12300 sau 15000, in clauza where se va folosi operatorul in:

Select den_produs from produse where pret in (11000, 12300, 15000);

h) Daca doriti sa ordonati tabela Produse dupa calitate si pret, pornind de la pretul cel mai mare din grupa de calitate respectiva:

Select distinctrow * from produse order by calitate, pret desc;

i)  Daca doriti sa aflati pretul maxim, minim si mediu al produselor, avem:

Select distinctrow max(pret) as [pret max], min(pret) as [pret min], avg(pret) as [pret mediu] from produse;

Observati ca prin clauza as din exemplul de mai sus am putut modifica eticheta coloanelor ce returneaza valoarea functiilor specificate.

j)  In cazul in care doriti sa cunoasteti numarul de produse, cererea va utiliza functia Count:

Select count(*) as "Numar de produse" from produse;

k) Daca doriti sa cunoasteti la ce calitati si la ce preturi este oferit produsul pere, aveti doua posibilitati:

Select den_produs, pret from produse where den_produs="pere";

sau

Select den_produs, pret from produse where den_produs like "pere";

Operatorul like se utilizeaza mai ales cand cautarile se realizeaza pe subsiruri ale realizarilor atributelor de tip text.

l)  Daca doriti sa cunoasteti ce produse, ale caror nume incep cu litera "m", sunt stocate in tabela Produse:

Select den_produs from produse where den_produs like "m";

m)   Daca doriti sa cunoasteti preturile produselor ale caror nume incep cu litere de la "a" la "m":

Select den_produs, pret from produse where den_produs

like "[a-m]*";

Blocuri de cerere operand pe grupuri de inregistrare

In acest caz, interogarile vor lucra pe grupuri de inregistrari definite dupa criterii specificate (prin clauza GROUP BY), la nivelul grupului de inregistrari executandu-se urmatoarele functii agregate:

Count pentru a numara inregistrarile reunite in cadrul grupului;

Sum pentru a returna suma valorilor unui atribut la nivelul grupului;

Min returneaza valoarea minima pentru atributul respectiv;

Max returneaza valoarea maxima pentru atributul respectiv;

Avg returneaza valoarea medie pentru atributul respectiv;

Var returneaza variatia pentru atributul respectiv;

StDev returneaza variatia standard pentru multimea valorilor unui camp;

Exemple:

a) Pentru a stabili pretul maxim, minim si mediu al produselor la nivelul fiecarei calitati:

SELECT DISTINCTROW calitate, max(pret) AS [pret maxim], min(pret) AS [pret minim], avg(pret) AS [pret mediu] FROM produse GROUP BY calitate;

b) Pentru a afla cate produse sunt oferite in cadrul fiecarui nivel de calitate :

SELECT count(*) FROM produse GROUP BY calitate ORDER

BY calitate ;

c) Pentru a afla cate produse, cu preturile intre 11800 si 15000, sunt oferite in cadrul fiecarui nivel de calitate :

SELECT count(*) AS 'Nr. produse' FROM produse WHERE

pret BETWEEN 11800 AND 15000 GROUP BY calitate ORDER

BY calitate ;

d) Ce cantitati s-au livrat din fiecare produs (este necesara gruparea pe produs si aplicarea functiei SUM pe campul [cant fact]):

SELECT produse.[den_prod], SUM(livrari.[cant fact]) AS 'Total cantitati' FROM produse, livrari WHERE produse.[cod_produs] = livrari.[cod_prod] GROUP BY produse.[den_prod];

Utilizarea subcererilor

Realizarea unor cautari mai complexe in BD presupune construirea unor subcereri in cadrul unei cereri.

Exemple:

a) Sa presupunem ca dorim sa cunoastem care sunt produsele cu pretul mai mare decat al portocalelor:

SELECT DISTINCTROW [den_prod], pret FROM produse

WHERE pret > ( SELECT pret FROM produse WHERE den_prod='Portocale');

In cazul cererilor imbricate, prin predicatele ALL, ANY si SOME se pot restrictiona rezultatele returnate. Ulimele doua sunt sinonime si permit regasirea inregistrarilor din cererea principala, care satisfac comparatia cu oricare inregistrare din subcerere. Primul permite regasirea inregistrarilor din cererea principala care satisfac comparatia cu toate inregistrarilor returnate de subcerere.

b) Daca vrem sa cunoastem produsele de calitatea intai cu pretul mai mic decat al tuturor produselor de calitatea a doua:

SELECT [den_prod] FROM produse

WHERE calitate = 1 AND pret < ALL( SELECT pret FROM produse WHERE calitate = 2);

c) Pentru a afla care este cantitatea minima livrata pentru produsul mere, mai mica decat toate cantitatile livrate din produsele existente:

SELECT livrari.[cant fact] AS [cantitate minima] FROM produse, livrari WHERE produse.[cod_produs] = livrari.[cod_prod] AND produse.[den_prod] = 'mere' AND livrari.[cant fact] < ALL (SELECT [cant fact] FROM livrari);

d) Pentru a afla produsele pentru care nu s-au facut livrari, in cadrul blocului de cereri se va utiliza predicatul NOT EXISTS:

SELECT produse.[den_prod] FROM produse WHERE NOT EXISTS (SELECT * FROM livrari WHERE

produse.[cod_produs] = livrari.[cod_prod]);

CONCLUZIE:

Acces genereaza automat forma SQL a interogarilor exprimate in interfata QBE. Utilizatorii pot modifica aceste interogari schimband modul de vizualizare (optiunea SQL din meniul VIEW). In plus, utilizatorul poate formula interogari direct in SQL, astfel:

se activeaza interfata QBE;

se precizeaza sursa de date, eventual;

se comuta in mod SQL (View/SQL);

se scrie blocul de cerere SQL;

se executa interogarea.