+ All Categories
Home > Documents > GESTIUNEA OBIECTELOR QUERIES CU LIMBAJUL NEPROCEDURAL ACCESS-SQL

GESTIUNEA OBIECTELOR QUERIES CU LIMBAJUL NEPROCEDURAL ACCESS-SQL

Date post: 21-Oct-2015
Category:
Upload: irinucaa92
View: 52 times
Download: 1 times
Share this document with a friend
Description:
GESTIUNEA OBIECTELOR QUERIES CU LIMBAJUL NEPROCEDURAL ACCESS-SQL
21
1 8. GESTIUNEA OBIECTELOR QUERIES CU LIMBAJUL NEPROCEDURAL ACCESS-SQL 8.1. Prezentare generală SQL SQL (Structured Query Language) este unul dintre cele mai puternice limbaje de programare structurate folosite pentru interogarea bazelor de date relaţionale, fiind implementat de aproape toate sistemele orientate pe gestiunea bazelor de date relaţionale. Limbajul SQL a fost creat pe la începutul anilor ‟70 în laboratoarele de cercetare ale firmei IBM pentru implementarea modelului relaţional al lui E. F. Codd. Astfel, în anul 1976 a fost publicată sintaxa completă a SQL-ului, care a fost adoptat de ANSI (American National Standards Institute), iar în anul 1986, ca limbaj standard pentru lucrul cu baze de date relaţionale. SQL este un limbaj de programare neprocedural deoarece utilizatorul descrie numai informaţiile pe care vrea să le obţină în urma interogării bazelor de date, fără a fi necesar să stabilească modul de a ajunge la rezultatele aşteptate. În consecinţă, SQL poate fi considerat ca un limbaj din de aplicaţie, fiind orientat pe mulţimi. Ca atare, SQL este un limbaj simplu şi accesibil acelor utilizatori interesaţi de gestionarea bazelor de date relaţionale. Pe plan mondial, există un anumit grad de standardizare a limbajului SQL, în sensul că mai multe sisteme de gestiune a bazelor de date recunosc principalele instrucţiuni SQL (de exemplu Oracle, MS-Access etc). Totuşi, standardul în domeniu este considerat standardul ANSI, care se referă atât la aspectele de definire, interogare, procesare a datelor, a tranzacţiilor, cât şi la caracteristicile privind integritatea informaţiilor, la cursoarele derulante sau jocţiunile externe. Cu toate acestea, firme care produc sisteme de gestiune a bazelor de date oferă extensii proprii ale limbajului SQL. 8.2. Microsoft Access-SQL Access-SQL oferit de Microsoft diferă, în unele aspecte, de standardul ANSI în sensul că, pe de o parte, conţine instrucţiuni legate de securitate şi acces concurent (de exemplu, COMMIT, GRANT, LOCK) sau instrucţiuni DDL Furnizati mai multe detalii pentru noţiunea de „SQLşi comparaţi MYSql, MS-SQL, Oracle. Folosiţi drept principală sursă de informare Internetul.
Transcript

1

8. – GESTIUNEA OBIECTELOR QUERIES CU

LIMBAJUL NEPROCEDURAL ACCESS-SQL

8.1. Prezentare generală SQL

SQL (Structured Query Language) este unul dintre cele mai puternice

limbaje de programare structurate folosite pentru interogarea bazelor de date

relaţionale, fiind implementat de aproape toate sistemele orientate pe gestiunea

bazelor de date relaţionale. Limbajul SQL a fost creat pe la începutul anilor ‟70 în

laboratoarele de cercetare ale firmei IBM pentru implementarea modelului

relaţional al lui E. F. Codd. Astfel, în anul 1976 a fost publicată sintaxa completă

a SQL-ului, care a fost adoptat de ANSI (American National Standards Institute),

iar în anul 1986, ca limbaj standard pentru lucrul cu baze de date relaţionale.

SQL este un limbaj de programare neprocedural deoarece utilizatorul descrie

numai informaţiile pe care vrea să le obţină în urma interogării bazelor de date,

fără a fi necesar să stabilească modul de a ajunge la rezultatele aşteptate. În

consecinţă, SQL poate fi considerat ca un limbaj din de aplicaţie, fiind orientat pe

mulţimi. Ca atare, SQL este un limbaj simplu şi accesibil acelor utilizatori

interesaţi de gestionarea bazelor de date relaţionale.

Pe plan mondial, există un anumit grad de standardizare a limbajului SQL, în sensul că mai multe sisteme de gestiune a bazelor de date recunosc principalele instrucţiuni SQL (de exemplu Oracle, MS-Access etc). Totuşi, standardul în domeniu este considerat standardul ANSI, care se referă atât la aspectele de definire, interogare, procesare a datelor, a tranzacţiilor, cât şi la caracteristicile privind integritatea informaţiilor, la cursoarele derulante sau jocţiunile externe. Cu toate acestea, firme care produc sisteme de gestiune a bazelor de date oferă extensii proprii ale limbajului SQL.

8.2. Microsoft Access-SQL Access-SQL oferit de Microsoft diferă, în unele aspecte, de standardul

ANSI în sensul că, pe de o parte, conţine instrucţiuni legate de securitate şi acces concurent (de exemplu, COMMIT, GRANT, LOCK) sau instrucţiuni DDL

Furnizati mai multe detalii pentru noţiunea de „SQL” şi

comparaţi MYSql, MS-SQL, Oracle.

Folosiţi drept principală sursă de informare Internetul.

2

(Data Definition Language) pentru definirea datelor şi, pe de altă parte, include instrucţiunea TRANSFORM şi declaraţia PARAMETROS.

De asemenea, sistemul de gestiune a bazelor de date Microsoft Access începând cu versiunea 2000 acceptă folosirea limbajului de interogare SQL, în gestiunea bazelor de date de tip MS-Access. Astfel, prin tehnica (interfaţa) grafică QBE, informaţia definită pe grila QBE este transformată automat într-o instrucţiune SQL. Deci, se poate afirma că, Access-SQL este conceput mai mult pentru crearea interogărilor de selecţie.

MS-Access oferă utilizatorilor posibilitatea de a folosi instrucţiuni SQL în diferite situaţii, ca de exemplu:

• înlocuirea unor interogări ale bazei de date cu instrucţiunea SELECT, simplificând baza de date (va conţine mai puţine obiecte), însă micşorînd viteza de regăsire a datelor;

• crearea unor tipuri de interogări a bazei de date numai cu instrucţiuni SQL (de tip Union, de definire a datelor, de comunicare cu alte baze de date).

În MS-Access instrucţiunile SQL se pot introduce în două moduri: • în fereastra de dialog SQL View, deschisă în modul de lucru Design View al unei interogări, din care se alege comanda New/SQL; • în cadrul codului de instrucţiuni al aplicaţiilor.

Pentru a scrie corect instrucţiunile SQL în MS-Access este necesar să se respecte în mod strict unele reguli de sintaxă: • fiecare instrucţiune trebuie să se termine cu un caracter punct şi virgulă (;), cu toate că Access-SQL acceptă instrucţiunea şi fără acest caracter, care este denumit terminator de instrucţiune; • la crearea unei interogări, în care se folosesc câmpuri de date din mai multe tabele ale bazei de date, pentru a separa numele tabelului de numele câmpului

de date este necesar să se folosească caracterul punct (.);

• pentru a delimita parametri dintr-o listă trbuie folosită virgula (,);

• pentru a marca datele de tip caracter trebuie încadrate între două caractere

apostrof (‘) sau ghilimele (”);

• pentru a desemna unul sau mai multe caractere de înlocuire trebuie folosite

caracterele “?” şi “*”;

• pentru specificarea inegalităţilor din cadrul clauzelor trebuie folosite două paranteze ascuţite (< >); • pentru a pune în evidenţă şirurile de tip dată calendaristică şi de timp se

apelează la caracterul “#”;

• pentru a încadra numele de câmpuri de date atunci când conţin spaţii sau simboluri neacceptate de SQL trebuie să fie folosite parantezele drepte ([ ]).

De asemenea, pentru prezentarea corectă a sintaxei instrucţiunilor şi a clauzelor Access-SQL se folosesc convenţii de notare, printre care următoarele: • elementele de sintaxă incluse între paranteze drepte ([ ]) sunt opţionale; • dintre elementele de sintaxă incluse între acolade ({ }) şi separate printr-o bară verticală (|) trebuie să se aleagă numai un singur element;

3

• un şir de puncte de suspensie (…) precizează că se continuă o enumerare

(se repetă acele elemente după care sunt specificate).

În literatura de specialitate se cunosc trei metode de bază referitoare la implementarea limbajului SQL: • implementare prin apelare directă (Direct Invocation), care constă în introducerea instrucţiunilor în prezenţa promter-ului sistem (de la prompter); • implementare de tip modular (Modul Language), care foloseşte anumite proceduri apelate de programele aplicaţiilor;

• implementarede tip încapsulat (Embedded SQL), care foloseşte instrucţiuni încapsulate în codul de program, fiind de tip static şi dinamic.

În funcţie de rolul îndeplinit în gestiunea bazelor de date, instrucţiunile SQL se pot grupa astfel:

• instrucţiuni de definire a datelor, care se folosesc pentru descrierea structurii bazei de date;

• instrucţiuni de procesare a datelor, prin care se efectuează operaţii de adăugare, ştergere şi modificare asupra înregistrărilor din baza de date;

• instrucţiuni de selecţie a datelor, care se folosesc pentru consultarea bazei de date;

• instrucţiuni de procesare a tranzacţiilor, referitoare la unităţile logice de prelucrare şi de suport al unor operaţii multiple de prelucrare a datelor;

• instrucţiuni de control al cursorului; • instrucţiuni de control al accesului la baza de date.

Limbajul Access-SQL foloseşte un vocabular alcătuit dintr-o diversitate de categorii de cuvinte cheie dintre care principale sunt următoarele: • instrucţiunile, care au rolul cel mai important în executarea interogărilor bazei de date, deoarece determină executarea de acţiuni (exemplu, SELECT); • clauzele, prin care se restricţionează domeniul entităţilor care participă la interogări (exemplu, WHERE sau ORDER BY); • funcţiile, care îmbunătăţesc performanţele limbajului SQL de gestionare a bazelor de date; • operatorii, care efectuează operaţii de comparare a rezultatelor selecţiei.

La aceste elemente de vocabular mai sunt asociate şi alte elemente de sintaxă, denumite parametrii sau argumente, prin care se precizează modul de acţiune al instrucţiunilor.

Aceste elemente de vocabular Access-SQL se scriu de regulă cu litere majuscule şi sunt completate de parametrii, care se scriu cu litere minuscule.

Folosirea limbajului Access-SQL se bazează în principal pe conceptul de bloc deinterogare, care se poate formaliza astfel:

< bloc de interogare > listă de atribute din lista de tabele ale bazei de date criteriu de îndeplinit < expresie>

unde:

4

• listă atribute conţine atribute din structura tabelei bază de date sursă sau atribute calculate pe baza celor existente în tabel sau tabelele sursă, • <expresie> este condiţia logică pe care se crează criteriul de selecţie.

8.3. Etapele creării şi executării interogărilor Access-SQL La crearea şi executarea unei interogări în bazele de date MS-Access,

realizate prin folosirea de instrucţiuni, sunt parcurse într-o ordine logică şi cronologică, câteva etape principale: • Din fereastra de dialog Open, afişată după ce s-a lansat în execuţie MS-Access, se deschide baza de date în care se vor efectua interogările SQL; • Se selectează grupul de obiecte Queries. În continuare, se execută click pe toolbar-ul create butonul Query Wizard sau pe opţiunea Query Design;

Fig.8.1. Toolbar-ul Create afişând grupul de obiecte Queries

• Se închide, prin click pe butonul Close, fereastra de dialog Show Table, afişată peste fereastra Query ;

5

Fig.8.2. Ferestrele afişate suprapus Query şi Show Table

• La click dreapta pe numele interogării se activează opţiunea (modul de vizualizare) SQL View, pentru a crea interogarea Access-SQL. Ca efect, în fereastra Select Query se afişează instrucţiunea Select (fig.8.4) şi în continuare se tastează instrucţiunile SQL specifice, respectând regulile de sintaxă şi de punctuaţie corespunzătoare, pentru a crea interogarea de selecţie;

Fig.8.3. Query şi Opţiunile la click dreapta pe numele Interogării (Query1)

6

• Interogarea Access-SQL astfel creată, se lansează în execuţie într-unul din modurile:

• prin activarea butonului ! (Run) de pe bara de instrumente Query Design;

• prin activarea opţiunii Run din meniul Query.

Fig.8.4. Fereastra Select Query afişând instrucţiunea Select pentru a începe scrierea celorlalte instrucţiuni din blocul de interogare Select

În final, pe ecranul monitorului se afişează rezultatul interogării, rezultat pe care utilizatorul urmează să-l analizeze şi să-l interpreteze potrivit propriilor cerinţe şi restricţii.

Dacă interogarea Access-SQL conţine erori de sintaxă sau rezultate eronate (erori logice), este necesar să se revină în modul de afişare SQL View pentru efectuarea corecţiilor necesare.

8.4. Instrucţiuni pentru definirea datelor În MS-Access, definirea structurii datelor se poate realiza prin:

• interogări DDL (Data Definition Language); • folosirea ferestrelor de dialog Table Design şi Table Datasheet; • prin intermediul obiectelor DAO (Data Access Object).

Cu toate că interogările DDL nu oferă prea multe facilităţi, cum ar fi definirea regulilor de validare, a valorilor implicite etc, totuşi aceste interogări au avantajul că au la bază un limbaj standard, implementat de aproape toate sistemele de gestiune a bazelor de date relaţionale. În consecinţă, nu se pot crea interogări în fereastra de dialog QBE, ci numai în fereastra SQL View.

Pentru definirea datelor Access-SQL oferă instrucţiunile DDL următoare: CREATE TABLE, ALTER TABLE, DROP TABLE, DROP DATABASE.

Instrucţiunea CREATE TABLE este folosită pentru crearea structurii unui tabel, rezultatul fiind la fel ca în modul interfaţă grafică (asistat).

Instrucţiunea ALTER TABLE permite să se adauge un câmp de date la un tabel creat anterior. Totuşi, nu este posibilă adăugarea, respectiv ştergerea de câmpuri de date la nivelul bazei de date în ansamblu.

Instrucţiunea DROP TABLE se foloseşte pentru a şterge complet un tabel dintr-o bază de date, inclusiv indecşii şi valorile asociate.

Instrucţiunea DROP DATABASE se foloseşte la ştergea unei baze de date. Există însă o mulţime de restricţii stabilite de către administratorul bazei de date privind această operaţie.

7

8.5. Instrucţiuni de selecţie a datelor În limbajul Access-SQL instrucţiunile de selecţie a datelor constituie una

dintre categoriile cele mai importante ale limbajului de interogare. O cerinţă de selecţie a datelor din baza de date relaţională se exprimă printr-o structură denumită bloc de cerere.

În funcţie de scopul urmărit de utilizator şi de modul de formulare a cererilor de interogare se disting: • blocuri de cereri de interogare simple, care permit vizualizarea înregistrărilor dintr-un tabel al bazei de date, realizarea proiecţiilor şi selecţiilor asupra bazei de date, returnarea unor atribute calculate; • blocuri de cereri de interogare complexe, care permit realizarea de interogări cu structură complexă şi/sau folosirea mai multor tabele sursă, fiind: - blocuri de cereri prin interogarea mai multor tabele;

- blocuri de cereri operând pe grupuri de înregistrări; - blocuri de cereri prin folosirea subcererilor; - blocuri de cereri prin folosirea operatorilor UNION şi JOIN.

Blocurile de cereri prin interogarea mai multor tabele se disting prin menţiunea în clauza FROM a numelor tabelelor sursă interogate şi în clauza WHERE a criteriilor de compunere a înregistrărilor.

Blocurile de cereri operând pe grupuri de înregistrări se disting prin faptul că interogările operează pe grupuri de înregistrări definite după criterii specificate la nivelul grupului de înregistrări, executându-se funcţii agregate (exemplu: Count, Sum, Max, Min, Avg, Var etc).

Blocurile de cereri prin folosirea subcererilor permit realizarea unor căutări mai complexe în baza de date construind subcereri în cadrul unor cereri.

Blocurile de cereri prin folosirea operatorului UNION se disting prin faptul că pentru unirea înregistrărilor se foloseşte operatorul UNION.

Blocurile de cerere prin folosirea operatorului JOIN se disting prin faptul că operaţiile de asociere au ca rezultat obţinerea tuturor combinaţiilor posibile, care corespund conţinutului informaţional al fiecărui tabel sursă (pot fi admise mai mult de două tabele).

8.6. Instrucţiuni pentru manipularea datelor Aceste instrucţiuni sunt deosebit de utile în exploatarea bazelor de date, fiind implementate prin interogări de tip acţiune. Ţinând seama de modul de acţiune, este necesar să fie folosite cu deosebită atenţie, deoarece efectele acţiunii acestora sunt permanente (ireversibile), influenţând inclusiv integritatea referenţială a bazei de date. Dintre aceste instrucţiuni cele mai importante sunt următoarele: SELECT INTO, INSERT INTO, UPDATE şi DELETE.

Instrucţiunea SELECT INTO se foloseşte pentru a crea un nou tabel cu înregistrări din alt tabel sau din altă interogare.

8

Instrucţiunea INSERT INTO se foloseşte pentru a adăuga înregistrări dintr-un tabel în altul. Există două forme de sintaxă ale acestei instrucţiuni: • INSERT INTO…VALUES folosită pentru operaţii simple care implică lucrul cu un număr redus de înregistrări, astfel că se poate întroduce o singură înregistrare la un moment dat;

• INSERT INTO…SELECT folosită pentru a copia selectiv înregistrări

dintr-un tabel în altul sau în mai multe tabele.

Instrucţiunea UPDATE se foloseşte pentru a însera înregistrări noi, cât şi de a modifica valorile câmpurilor de date din înregistrările curente.

Instrucţiunea DELETE se foloseşte pentru ştergerea parţială sau totală a înregistrărilor din tabele ale bazei de date.

8.7. Definirea cererilor SQL în MS-Access MS-Access permite:

• generarea automată a codului SQL pentru cererile (interogările) din interfaţa QBE; • utilizatorilor să modifice aceste cereri schimbând modul de vizualizare (opţiunea View SQL din meniul Access); • utilizatorilor să formuleze cereri direct în SQL astfel:

- se activează interfaţa QBE; - [se precizează sursa de date]; - se comută în mod SQL (VIEW/SQL); - se scrie blocul de cerere SQL; - se execută cererea.

Blocuri de cereri se mai pot construi şi prin folosirea de programe scrise în

mediul de programare VBA.

8.8. Elementele limbajului:

Instrucţiunea: este reprezentă prin cuvinte rezervate/cheie : INSERT,

SELECT, UPDATE, ...;

Instrucţiunile pentru manipularea datelor sunt urmatoarele:

.................................................................................................................

.................................................................................................................

.................................................................................................................

9

Clauzele instrucţiunilor: definesc restricţii asupra modului de executie a

unei instructiuni; Exemple: WHERE, ORDER BY, GROUP BY,

HAVING;

Funcţii predefinite (totalizatoare): SUM, MAX, MIN, AVG, COUNT;

Operatorii: +, -, *, /, <, >, <=, >=, <>, LIKE, IN, BETWEEN, IS, AND, OR,

NOT

Exemplu : pret IS NULL, pret IS NOT NULL

Denumire_produs LIKE „R*” Denumire_produs LIKE „R?”

Reguli de creare a instrucţiunilor SQL:

La finalul unei instrucţiuni se va pune caracterul ; (punct şi virgulă)

Dacă într-o instrucţiune apar câmpuri din mai multe tabele acestea se vor

referi printr-o construcţie de genul nume_tabela.[nume_camp]

Înntr-o listă de valori elementele listei se separă prin , (virgulă)

Într-o instrucţiune SQL şirurile de caractere se încadrează între

caracterele „ (apostrof) sau “ (ghilimele). Exemple: “test”, „test‟

Informaţiile de tip dată calendaristică se în cadrează între două caractere

#. Exemplu: #10/01/2009#

8.9. Instrucţiuni

8.9.1 Instructiuni SQL pentru definirea datelor.

Instructiunea pentru crearea unei baze date: CREATE DATABASE nume_bd; Exemplu: CREATE DATABASE excursii; Instrucţiunea pentru crearea unei tabele: CREATE TABLE numele_tabelei(camp1 tip_de_data1 [NOT NULL][PRIMARY KEY], camp2 tip_de_data2 [NOT NULL] [….], ….); Tipurile de date utilizate în SQL:

o VARCHAR(n)

o MEMO

o NUMBER

o INTEGER

o DECIMAL

o LOGICAL

o DATE

10

Exemplu: Creaţi tabela materiale(Cod_Material Int, Denumire_material C(25), Pret N, Cantitate N, cod_magazie Int) CREATE TABLE materiale(Cod_material INTEGER PRIMARY KEY, denumire_material VARCHAR(25), pret NUMBER, cantitate NUMBER); Instrucţiunea pentru modificarea structurii unei tabele ALTER TABLE numele_tabelei ADD camp tip_de_data; Exemplu: ALTER TABLE materiale ADD cod_magazie INTEGER; Instrucţiunea pentru ştergerea unei tabele: DROP TABLE nume_tabela; Instructiunea pentru stergerea unei baze de date: DROP DATABASE nume_baza de date; Exemplu: Stergerea tabelei materiale: DROP TABLE materiale; Stergerea bazei de date; DROP DATABASE excursii; 8.9.2 Instructiuni SQL pentru manipularea datelor Instrucţiunea pentru adăugare de noi înregistrări într-o tabelă: INSERT INTO nume_tabela VALUES (val1, val2,… valn); sau INSERT INTO nume_tabela(camp1, camp2, …) VALUES (val1, val2, …); Exemplu: a) inseraţi în tabela materiale o înregistrare cu următoarele câmpuri: cod_material = 101, denumire_material = “televizor”, cantitate=120

INSERT INTO materiale(cod_material, denumire_material, cantitate) VALUES (101, „televizor‟, 120);

b) inseraţi în tabela materiale o înregistrare cu următoarele câmpuri: cod_material = 102, denumire_material=”PC”, cantitate=100, pret=1200, cod_magazie=12 INSERT INTO materiale VALUES(102, „PC‟, 100, 1200, 12); Instructiunea pentru ştergerea de înregistrări: DELETE FROM nume_tabela [WHERE conditie];

11

Exemplu: a) să se şteargă din tabela materiale toate materialele care au pretul mai mic decat 100; DELETE FROM materiale WHERE pret<100; b) să se şteargă din tabela materiale toate materialele care au pretul mai mic decat 100 şi cantitatea mai mare decât 150; DETELE FROM materiale WHERE pret<100 AND cantitate>150;

c) să se şteargă din tabela materiale toate materialele care au pretul mai mic decat 100 sau cantitatea mai mare decât 125; DELETE FROM materiale WHERE pret<100 OR cantitate>125;

Instrucţiunea pentru modificarea înregistrărilor: UPDATE nume_tabela SET camp1=val1, camp2=val2, …. [WHERE conditie]; Exemplu: a) să se modifice preţul tututor materialelor prin creşterea lui cu 10%. UPDATE materiale SET pret=1.1*pret; b) să se modifice preţul materialelor a căror denumire începe cu „TV” prin creşterea lui cu 15%. UPDATE materiale SET pret=1.15*pret WHERE denumire_material LIKE “TV*”; c) pentru toate materialele a căror denumire se termină cu „TV” preţul va scade cu 15% iar cantitatea va scade cu 12%. UPDATE materiale SET pret=0.85*pret, cantitate=0.88*cantitate WHERE denumire_material LIKE “*TV”;

8.9. 3 Instructiuni pentru selectia datelor

8.9.3.1 Instrucţiuni de selecţie simple

SELECT [domeniu] lista_ de_campuri FROM lista_de_tabele [WHERE conditie] [ORDER BY camp ASC|DESC]; Parametrul domeniu poate lua următoarele 2 valori: ALL, DISTINCT. ALL înseamnă că în rezultatul instrucţiunii se vor include toate înregistrările care satisfac criteriul de

12

selecţie, în timp ce DISTINCT are ca efect eliminarea înregistrărilor care conţin duplicate în câmpurile selectate. Lista de câmpuri cuprinde toate câmpurile ce vor apare în rezultatul interogării şi se poate specifica în mai multe moduri: - camp1, camp2, camp3, … - nume_tabela1.camp1, ….. - camp1 AS alias1, camp2 AS alias2, … -* Lista de tabele reprezintă tabelele de unde se vor selecta datele şi se poate specifica în următoarele moduri: - nume_tabela1, nume_tabela2, ….

- nume_tabela1 AS alias1, nume_tabela2 AS alias2, …. Clauza WHERE se utilizează pentru a specifica o condiţie care va sta la baza selecţiei înregistrărilor. Clauza ORDER BY permite sortarea rezultatelor crescător sau descrescător. Exemple: Fie tabela stocuri(denumire C(15), cod_material N, UM C(3), pret N, cod_depozit N). a) Să se afiseze conţinutul acestei tabele SELECT * FROM stocuri; SELECT denumire,cod_material,um,pret,cod_depozit FROM stocuri; b) Să se afiseze o listă cu următoarele trei coloane ce conţine toate materiale din tabela stocuri denumire um pret ========================== SELECT denumire, um, pret FROM stocuri; c) Să se afiseze o listă cu următoarele trei coloane ce conţine materiale a căror unitate de măsura este „kg” denumire um pret ========================== SELECT denumire, um, pret FROM stocuri WHERE um=”kg”; d) Se cere o listă cu mărfurile aflate în stoc în depozitele 2 şi 3, pentru care există un preţ de desfacere; SELECT denumire, um, pret FROM stocuri WHERE cod_depozit IN (2,3) AND pret IS NOT NULL; sau

13

SELECT denumire, um, pret FROM stocuri WHERE (cod_depozit=2 OR cod_depozit=3) AND pret IS NOT NULL; e) Se cere o listă cu mărfurile aflate în stoc în depozitele 2 şi 3, pentru care există un preţ de desfacere; Lista va avea trei coloane cu următoarele denumiri: Material UnitateMasura PretProdus ======================================= SELECT denumire AS Material, um AS UnitateMasura, pret AS PretProdus FROM stocuri WHERE (cod_depozit=2 OR cod_depozit=3) AND pret IS NOT NULL; f) să se afiseze toate materialele în ordine crescătoare a pretului; SELECT * FROM stocuri ORDER BY pret ASC; g) să se afişeze acele produse care au um=‟buc‟ ordonate invers alfabetic după denumire; SELECT * FROM STOCURI WHERE um=‟buc‟ ORDER BY denumire DESC; h) să se afişeze denumirea, pretul, um pentru acele materiale care au um = „l” sau „m” sau „kg”; SELECT denumire, pret, um FROM stocuri WHERE um IN („l‟, „m‟, „kg‟); sau SELECT denumire, pret, um FROM stocuri WHERE um=‟l‟ OR um=‟m‟ OR um=”kg”; Se consideră tabela studenti(nume, prenume, an, grupa, media) a) se cere o listă cu studentii din anul 2 si 3 al căror nume începe cu A, şi au media cuprinsa intre 8 si 10, sortata alfabetic dupa nume. Lista va avea următoarele coloane: Nume Prenume Media =================================== SELECT nume, prenume, media FROM studenti WHERE an IN (2,3) AND nume LIKE “A*” AND media BETWEEN 8 and 10 ORDER BY nume ASC;

14

8.9.3.2 Functii totalizatoare. Funcţiile totalizatoare se mai numesc şi funcţii de grup deoarece ele acţionează asupra tuturor câmpurilor dintr-o coloana a unei tabele sau asupra unor grupuri de câmpuri. Aceste funcţii sunt SUM, AVG, MAX, MIN, COUNT. Exemple de utilizare: a) se da tabela contracte(denumire_client, nr_contract, data_contract, val_contract) Se cere sa se afle numarul de contracte incheiate intre 1/1/2009 si 10/10/2010 SELECT COUNT(*) AS Numar_Contracte FROM contracte WHERE data_contract BETWEEN #1/1/2009# AND #10/10/2010#; Rezultatul va fi un tabel de genul: Numar_Contracte 12 b) se da tabela studenti(nume, prenume, an, grupa, nr_absente) se cere care este numarul total de absenţe SELECT SUM(nr_absente) AS TOTAL FROM studenti; Rezultat: TOTAL 250 SELECT SUM(nr_absente) AS Total FROM studenti WHERE an IN(1,2); Se cere numărul maxim de absente şi numărul minim de absente pentru studentii studentii anului 2. SELECT MAX(nr_absente) AS NrMaxAbsente, MIN(nr_absente) AS NrMinAbsente FROM studenti WHERE an=2; Rezultatul: NrMaxAabsente NrMinimAbsente 34 12 c) se dă tabela stocuri(denumire C(15), cod_material N, um C(3), pret N, cod_depozit N, stoc N). Se cere care este valoarea medie a stocului SELECT AVG(stoc) FROM stocuri;

15

8.9.3.3 Cereri de interogare complexe Se va exemplifica gruparea datelor prin clauza GROUP BY şi HAVING, aplicarea unor functii totalizatoare pe grupuri precum şi interogări pe mai multe tabele. Gruparea datelor, aplicarea funcţiilor totalizatoare pe grupuri Sintaxă: SELECT functie1(camp1) AS alias [, functie2(camp2) …] FROM tabela, GROUP BY camp [HAVING criteriu_selectie] [ORDER BY camp ASC|DESC] Exemple: Se dă tabela creante(denumire_client, val_datorie, data_scadentei); Se presupune ca tabela conţine următoarele înregistrări: Denumire_client Val_datorie Data_scadentei ===================================================== AAA 700 1/10/2009 AAA 1000 2/10/2009 AAA 800 3/10/2009 BBB 2500 1/10/2009 BBB 3400 11/09/2009 AAA 3233 10/08/2009 BBB 5500 01/07/2009 CCC 2300 03/10/2009 Se cere care este datoria totală pentru fiecare client in parte. SELECT denumire_client, SUM(val_datorie) FROM creante GROUP BY denumire_client; Rezultat: Denumire_client Val_datorie ==================================== AAA 5733 BBB 11400 CCC 2300 Se cere care este valoarea minimă şi maximă a datoriilor fiecărui client SELECT denumire_client, MAX(val_datorie) AS Max, MIN(val_datorie) AS Min FROM creante GROUP BY denumire_client;

denumire_client Max Min

16

denumire_client Max Min

AAA 3233 700

BBB 5500 2500

CCC 2300 2300

Se cere generarea unei liste a clientilor care au datorii mai mari de 2000 de lei. SELECT denumire_client, SUM(val_datorie) AS Total_datorie FROM creante GROUP BY denumire_client HAVING SUM(val_datorie)>2000; Se dă tabela Imobile(tip_imobil, val_asigurata, adresa). Se cere sa se construiasca o lista cu tipurile de imobile ce au valoare medie asigurata >30000 SELECT tip_imobil AS TipImobil, AVG(val_asigurata) AS MediaValAsigurate FROM imobile GROUP BY tip_imobil HAVING AVG(val_asigurata)>30000; Selecţie pe mai multe tabele – realizarea operaţiei JOIN între tabele Se dau următoarele tabele:

facturi(nr_factura, data_facturii, cod_furnizor, cota_tva) continut_factura(nr_factura, cod_material, cantitate, pret) furnizori(cod_furnizor, denumire_furnizor, adresa, banca, cont) materiale(cod_mateiral, denumire_material, um)

a) Se cere o listă cu facturile emise pentru fiecare client : Nr_factura data_facturii denumire_furnizor ================================================== SELECT facturi.nr_factura, facturi.data_facturii, furnizori.denumire_furnizor FROM facturi, furnizor WHERE facturi.cod_client=clienti.cod_client; În acest exemplu operaţia între cele două tabele a fost de tipul echiJoin. b) se cere o listă de forma: nr_factura denumire_material pret cantitate ============================================== SELECT facturi.nr_factura, materiale.den_material, continut_factura.pret, continut_factura.cantitate FROM facturi, materiale, continut_facturi WHERE facturi.nr_factura=continut_factura.nr_factura AND continut_factura.cod_material=materiale.cod_material AND;

17

Join extern stanga, join extern dreapta. Sintaxa: SELECT lista_campuri from tabela1 [LEFT OUTER|RIGHT OUTER] JOIN tabela2 ON criteriu_asociere [WHERE conditie][ORDER BY camp ASC|DESC] Se cere o lista cu toate materialele, chiar daca nu au intrări. SELECT Materiale.Denumire_Material, ContinutFactura.Pret, ContinutFactura.Cantitate FROM Materiale LEFT OUTER JOIN ContinutFactura ON Materiale.Cod_Material = ContinutFactura.Cod_Material;

8.9.3.4 Subinterogări 1. SELECT lista_campuri FROM tabela1 WHERE tabela1.camp=(SELECT camp FROM tabela2 WHERE criteriu_selectie); Se dau următoarele două tabele: -furnizori(cod_furnizor, den_furnizor, adresa_furnizor) -materiale(cod_material, den_material, cod_furnizor, valoare) Se cere generarea unei situatii care sa contina informatii despre furnizorul care a livrat produse in valoare de 2500 lei. SELECT cod_furnizor, den_furnizor, adresa_furnizor FROM furnizori WHERE cod_furnizor=(SELECT cod_furnizor FROM materiale WHERE valoare=2500) Se dă tabela masini(marca, tip, pret). Marca Tip Pret ================================ DACIA LOGAN 7000 DACIA SANDERO 8000 DACIA LOGAN 9000 FORD FIESTA 9000 FORD FOCUS 15000 Se cere generarea unei liste cu toate marcile care au pretul mediu mai mic decât pretul mediu general. Marca Pret Mediu =========================== DACIA 8000

18

SELECT marca, AVG(pret) FROM masini GROUP BY marca HAVING AVG(pret) < ( SELECT AVG(pret) FROM masini); 8.9.4 Reuniunea, intersectia şi diferenţa tabelelor

SELECT lista_campuri FROM tabela1 UNION SELECT lista_campuri FROM tabela2 [GROUP BY camp][HAVING criteriu_selectie][ORDER BY camp ASC|DESC] Se presupune existenta a doua 2 tabele: colaboratori2008, colaboratori2009 cu structura: (nume, prenume, varsta, categorie) Se cere o lista cu toti colaboratorii firmei ordonati alfabetic dupa nume. SELECT nume, prenume, varsta FROM colaboratori2008 UNION SELECT nume, prenume, varsta FROM colaboratori2009 ORDER BY nume; Care este media de varsta a colaboratorilor studenti din 2008, 2009? SELECT AVG(varsta) FROM colaboratori2008 GROUP BY categorie HAVING categorie=”student” UNION SELECT AVG(varsta) FROM colaboratori2009 GROUP BY categorie HAVING categorie=”student”; SELECT lista_campuri FROM tabela1 INTERSECT SELECT lista_campuri FROM tabela2 [GROUP BY camp][HAVING criteriu_selectie][ORDER BY camp ASC|DESC] Care sunt colaboratorii care au lucrat şi în 2008 şi în 2009? SELECT * FROM colaboratori2008 INTERSECT SELECT * FROM colaboratori2009; SELECT lista_campuri FROM tabela1 MINUS SELECT lista_campuri FROM tabela2 [GROUP BY camp][HAVING criteriu_selectie][ORDER BY camp ASC|DESC]

19

1. În funcţie de modul cum sunt gestionate bazele de date relaţionale, SQL face parte din categoria limbajelor: a. procedurale b. neprocedurale c. de asamblare d. algoritmice

2. În SQL utilizatorii îşi exprimă cerinţele informaţionale în modul: a. algoritmic b. procedural c. neprocedural d. simbolic

3. Sintaxa completă a limbajului SQL a fost publicată în anul 1976 de către: a. ASCII b. ISO c. OSI d. ANSI

4. Ca limbaj simplu şi accesibil utilizatorilor, implementat pe aproape toate SGBD-urile, SQL se bazează în principal pe conceptul de: a. bloc de cerere b. instrucţiune c. comandă d. tabel

5. Regăsirea, selectarea şi afişarea datelor din bazele de date MS-Access se realizează cu instrucţiuni SQL de: a. descriere b. interogare c. manipulare d. control

6. Crearea tabelelor, modificarea structurii lor şi ştergerea tabelelor se realizează cu instrucţiuni SQL de: a. descriere b. interogare c. manipulare d. control

7. Înserarea, ştergerea, selectarea şi afişarea datelor implică folosirea unor instrucţiuni SQL de: a. descriere b. interogare c. manipulare d. control

8. Un bloc de cerere SQL începe întotdeauna cu instrucţiunea: a. SELECT b. INSERT c. UPDATE d. UNION

9. Într-un bloc de cerere SQL numele sursei de date (tabele şi/sau interogări) este specificat de clauza: a. FROM b. WHERE c. HAVING d. GROUP BY

10. În blocurile de selecţie SQL expresiile aritmetice trebuie încadrate între caracterele: a. [ ] b. { } c. “ ” d. ( )

20

I. Bibliografie obligatorie

1. Florescu V. (coordonator) şi colectiv – Baze de date. Fundamente teoretice şi practice, Editura InfoMega, Bucureşti, 2002

2. Popa Gh. şi colectiv – Baze de date Access, Editura CISON, Buc., 2003

3. Mureşan M. – Baze de date. Aplicaţii în MS Access XP, Editura PRO UNIVERSITARIA, Bucureşti, 2006

4. Surcel Tr., Mârşanu R., Avram V., Avram D. – Medii de programare pentru gestiunea bazelor de date, Ed. Tribuna Economică, Bucureşti, 2004

5. Sagman S. – Microsoft OFFICE 2003 pentru Windows (Ghid de învăţare rapidă prin imagini), Editura Corint, Bucureşti, 2004

II. Bibliografie facultativă

1. Mureşan M. – Gestiunea bazelor de date. SGBD Access 2002, Editura CURTEA VECHE, Bucureşti, 2004

2. Năstase P. şi colectiv – Tehnologia bazelor de date Access 2000, Editura Economică, Bucureşti, 2000

3. Popa Gh. şi colectiv – Baze de date Access - Culegere de probleme, Editura CISON, Bucureşti, 2002

4. Habracken J. – Access 2002 pentru începători, traducere de Cora Radulian, Editura Teora, Bucureşti, 2002

5. Forta B. – SQL în lecţii de 10 Minute, Editura Teora, Bucureşti, 2006

21

Răspunsurile corecte la întrebările din testul de autoevaluare (tip grilă):

1. b 2. c 3. d 4. a 5. b 6. a 7. c 8. a 9. a 10. d


Recommended