1

Limbajul SQL

- este un limbaj declarativ care permite utilizatorului să descrie informaţiile pe care doreşte să le obţină în urma interogării fără a preciza algoritmii necesari pentru obţinerea rezultatelor.

Tipuri de interogări SQL: A. Instrucţiuni pentru definirea datelor care permit descrierea structurii bazei de date B. Instrucţiuni de manipulare a datelor în sensul adăugării, modificării şi ştergerii

înregistrărilor; C. Instrucţiuni de selecţie a datelor care permit consultarea bazei de date; D. Instrucţiuni privind controlul accesului la date. Etape de creare a unei instrucţiuni SQL: 1. Din fereastra Database se va selecta Queryes-Create Query in Design View. 2. Din fereastra Show Table se vor selecta interogările, tabelele sau ambele tipuri de

obiecte ce constituie suportul noii interogări. 3. Din meniul View-SQL View se activează fereastra de scriere a instrucţiunii SQL. 4. execuţia propriu-zisă se realizează prin activarea butonului Run(Query-Run). Reguli de scriere a unei instrucţiuni SQL: a. Orice frază SQL se încheie cu ; b. Atunci cand numele campului conţine spaţii sau alte semne neacceptate de

Access, numele campului va fi introdus în [ ]. c. Cand campurile noii interogări provin din mai multe tabele se va preciza numele

campului precedat de numele tabelei.(Nume tabela.Nume camp). d. Inegalitatea în cadrul frazei Select se face cu <>. A. Instrucţiuni pentru descrierea structurii bazei de date

În general, aceste instrucţiuni permit crearea unei tabele, modificarea structurii acesteia sau ştergerea ei. 1. Crearea structurii tabelelor CREATE TABLE – permite crearea unei tabele într-o BD. Sintaxa: CREATE TABLE numetabela(numecamp1 tip1(marime),

Numecamp2 tip2(marime)..PRIMARY KEY/UNIQUE/NOT NULL)

Ex: Să se creeze tabela PERSONAL(Marca, Nume, Funcţia) Create Table Personal (Marca Number(4) PRIMARY KEY, Nume Text(15), Functia Text(10))

2. Modificarea structurii tabelelor Comanda ce permite modificarea structurii unei tabele este ALTER TABLE şi ea realizează una din opţiunile:

Ü adăgarea unui nou câmp în structură Ü modificarea unui câmp existent Ü ştergerea unui câmp din structura tabelei.

2

ALTER TABLE numetabela ADD numecamp tipcamp/ALTER COLUMN camp2 tip2 DROP numecamp. Ex: Adăugarea unui nou câmp:

ALTER TABLE FURNIZOR ADD Codpostal Number(5) Modificarea unui câmp din structura: ALTER TABLE FURNIZOR ALTER COLUMN Adresa Char(20) Stergerea unui câmp din structură: ALTER TABLE FURNIZOR DROP COLUMN Codpostal 3. Stergerea tabelelor.

Comanda de ştergere a unai tabele din baza de date este DROP TABLE, cu sintaxa: DROP TABLE numetabela

B. Instrucţiuni de manipulare a datelor

În cadrul acestor instrucţiuni Access include 3 comenzi: INSERT INTO, UPDATE şi DELETE comenzi ce acţionează doar asupra înregistrărilor unei singure tabele.

1. Adaugarea de noi înregistrări într-o tabelă

INSERT INTO numetabela [(atribut1, atribut2,…)]

VALUES (val_atribut1, val_atribut2,...) Ordinea valorilor din clauza VALUES trebuie să fie identică cu cea declarată la crearea tabelelor. Exemplu: Să se adauge în tabela Furnizor o noua înregistrare: (11, „aaa”, „Bucuresti”).

INSERT INTO furnizor VALUES (11, "aaa", "Bucuresti");

2. Modificarea înregistrărilor unei tabele.

UPDATE numetabela SET atribut1=expresie1 WHERE conditie;

Exemple: 1. Să se dubleze pretul tuturor materialelor a căror Unitate de măsură este exprimată în kg.

UPDATE materiale SET pret=pret * 2 WHERE um=”kg”;

3

3. Stergerea înregistrărilor unei tabele. DELETE * FROM numetabela WHERE conditie; Exemplu: 1. Să se şteargă toate facturile emise în ultima lună. DELETE * FROM factura WHERE dataf BETWEEN date()-30 AND date(); 2. Să se şteargă toate facturile emise de furnizorii din Bucuresti. DELETE FROM factura

WHERE codfz IN (SELECT codfz FROM furnizor WHERE adresa=”Bucuresti”); C. Instrucţiuni de selecţie a datelor ce permit consultarea bazei de date. 1. Instrucţiuni de selecţie a datelor bazate pe o singură tabelă - sevesc la regăsirea şi afişarea datelor dorite de utilizatori. Sintaxa generală a frazei SELECT este:

SELECT [domeniu]<numeatribute> FROM tabelă WHERE condiţia de filtrare a datelor ORDER BY atribut de ordonare[ASCENDING/DESCENDING]; unde: Domeniu={ALL, DISTINCT, TOP N}

ALL – realizeaza returnarea tuturor inregistrarilor DISTINCT – realizează eliminarea înregistrărilor duplicate. TOP n afişează primele n inregistrări cerute.

Exemple :

a.Să se afişeze în ordine cronologică toate facturile emise în primele 3 luni ale anului 2008.

SELECT nrfact, dataf FROM factura WHERE year(dataf)=2008 and

month(dataf) in (1,2,3) ORDER BY dataf; b.Să se afişeze în ordine alfabetică, furnizorii din provincie. SELECT denfz, adresa FROM furnizor

4

WHERE adresa <> "bucuresti" ORDER BY denfz; c.Să se afişeze facturile emise în perioada 15-20 septembrie 2007. SELECT nrfact, dataf FROM factura WHERE dataf BETWEEN #09/15/2007# AND #09/20/2007#; d. Să se afişeze primele 3 facturi emise in anul 2008. SELECT TOP 3 * FROM factura WHERE YEAR(dataf)=2008; 2. Instrucţiuni de selecţie a datelor avand ca sursa 2 sau mai multe tabele.

Sintaxa generală a frazei SELECT este: SELECT [domeniu]<lista campuri > FROM Tabelă1 INNER JOIN Tabela2 ON Tabela1.campcomun=Tabela2.campcomun WHERE condiţia de filtrare a datelor ORDER BY atribut de ordonare[ASCENDING/DESCENDING]; Exemple: a. Sa se afiseze in ordine alfabetica furnizorii care au emis facturi in anul curent.

SELECT furnizor.[denfz], factura.[nrfact], factura.[dataf] FROM FACTURA INNER JOIN FURNIZOR ON factura.codfz=furnizor.codfz WHERE year(dataf)=year(date()) ORDER BY denfz; SAU SELECT furnizor.[denfz], factura.[nrfact], factura.[dataf] FROM FACTURA,FURNIZOR WHERE factura.codfz=furnizor.codfz AND year(dataf)=year(date()) ORDER BY denfz;

b. Sa se afişeze facturile emise de furnizorii din provincie. SELECT furnizor.denfz, factura.nrfact, factura.dataf, furnizor.adresa FROM FACTURA INNER JOIN FURNIZOR ON factura.codfz=furnizor.codfz WHERE adresa<>”Bucuresti”;

3. Instructiuni SQL de sintetizare a datelor. Sintaxa generală a frazei SELECT este:

SELECTatributgrupare, functieagregat(atribut) AS eticheta FROM tabele sursa WHERE condiţia de filtrare a datelor GROUP BY atribut de grupare HAVING conditie asociata functiei agregat

5

ORDER BY atribut de ordonare[ASCENDING/DESCENDING]; Obs! – pentru campurile care nu participa la grupare e necesara folosirea unor functii agregat: SUM, MIN, MAX, AVG, COUNT - clauza WHERE filtreaza inregistrarile inainte de grupare - clauza HAVING permite filtrarea datelor rezultate in urma gruparii.

Exemple:

a. Sa se afişeze numarul total de facturi emise de fiecare furnizor in prima luna a anului 2008. SELECT codfz, COUNT(nrfact) as Total_facturi FROM factura WHERE month(dataf)=1 and year(dataf)=2008 GROUP BY codfz;

b. Sa se afişeze furnizorii care au emis mai mult de 2 facturi in primele 2 luni ale anului 2008. SELECT codfz, COUNT(nrfact) as Total_facturi FROM factura WHERE month(dataf) IN (1,2) and year(dataf)=2008 GROUP BY codfz HAVING COUNT(nrfact)>2;

c. Sa se afişeze zilele, din anul curent, in care au fost emise mai mult de 5 facturi. SELECT dataf FROM factura WHERE year(dataf)=year(date()) GROUP BY dataf HAVING COUNT(nrfact)>5;

d. Sa se afişeze in ordine alfabetica materialele care au inregistrat vanzari de peste 10000 lei. SELECT materiale.denmat, SUM(cantf*pretf) AS VALT FROM materiale INNER JOIN liniefactura ON materiale.codmat=liniefactura.codmat GROUP BY denmat HAVING SUM(cantf*pretf)>10000 ORDER BY denmat;

4. Imbricarea cererilor (subinterogari).

O cerere SELECT poate fi utilizata pentru a returna una sau mai multe inregistrari

ce vor putea fi folosite intr-o comanda INSERT/UPDATE/DELETE sau chiar intr-o alta comanda SELECT. Atunci cand subcererea returnează mai multe înregistrari, pt comparatii, se pot utiliza operatori:

IN – testează apartenenţa unei valori la o mulţime ANY – compară o valoare cu orice element al unie mulţimi

6

ALL – compară o valoare cu toate elementele unie mulţimi EXISTS – verifică existenţa unei mulţimi. In general subcererea conţine un singur camp rezultant (pentru a fi folosit in comparatii) si sunt executate in ordinea inversa a declararii lor.

a. Sa se afiseze furnizorii care nu au emis facturi in 2008. Select Furnizor.Codfz, furnizor.Denfz From Furnizor Where Furnizor.CodFz Not In (Select Distinct CodFz From Factura Where Year(DataFact)=2008); b. Sa se afiseze furnizorii care au emis mai mult de 5 facturi in 2008. SELECT Furnizor.Codfz, furnizor.Denfz FROM Furnizor WHERE Furnizor.CodFz IN (Select Factura.[CodFz] From Factura WHERE year(Factura.Dataf) = 2008 Group By Factura.[CodFz] Having Count(Factura.[Nrfact])> 5); c. Sa se afişeze furnizorii de la care s-au primit cele mai multe facturi.

SELECT CODFZ, COUNT(nrfact) AS Total F FROM factura GROUP BY codfz HAVING COUNT(nrfact)>=ALL(SELECT COUNT(nrfact)

FROM factura GROUP BY codfz);

5. Interogari reuniune O selectie reuniune contine mai multe tabele sau cereri SELECT asupra carora se aplica operatorul UNION. Sintaxa generala este: Tabela 1/Cerere1-SELECT UNION Tabela 2/Cerere2 –Select Order By atribut ordonare; OBS!

1. Implicit prin aceste selectii de tip reuniune se elimina inregistrarile duplicate. Atunci cand utilizatorul doreste si afisarea acestora se fol clauza ALL.

2. Tabelele sursa trebuie sa contina acelasi numar de campuri.Campurile selectate trebuie sa fie de acelasi tip. Ordinea campurilor trebuie sa fie aceeasi.

3. Clauza ORDER BY se poate specifica numai dupa ultima cerere SELECT si poate sa contina denumirile sau etichetele campurilor specificate in prima cerere SELECT.

7

Ex: Se considera tabele Clienti(CodC, Den Adresa) si Furnizori(CodF, Den, Adresa). Sa se afiseze toti partenerii societatii. SELECT CODC AS COD, DEN, ADRESA FROM CLIENTI UNION SELECT CODF, DEN, ADRESA FROM FURNIZORI ORDER BY COD;

Ex propus spre rezolvare: 1. Sa se afiseze in ordine alfabetica toate materialele care au fost aprovizionate

in anul curent. 2. Sa se afiseze toate materialele care au pretul mai mare decat al merelor. 3. Sa se reduca cu 50% pretul unitar al materialelor care au avut o

aprovizionare mai mica decat media aprovizionarilor totale. 4. Sa se stearga toti furnizorii care nu au emis facturi in ultimii 2 ani. 5. Sa se stearga materialele pentru care nu s-au inregistrat aprovizionari.