Interogari (Queries)

Interogarile sunt componente Access de extragere si de afisare a a datelor din una sau mai multe tabele/interogari, pe baza de criterii. Un alt scop pentru care se folosesc interogarile il constituie modificarea structurii si continutului bazei de date (functii de creare tabele, adaugare, modificare si stergere inregistrari in tabele).

Prima categorie formeaza interogarile de selectie, cea de-a doua formeaza interogarile de actiune.

O interogare se transforma intr-o instructiune a limbajului SQL (Structured Query Language), recunoscut de toate bazele de date relationale.

In acest paragraf ne vom referi numai la interogarile de selectie, care la randul lor pot fi: interogari simple, interogari total si interogari tabel incrucisat.

Functiile unei interogari de selectie:

Preluarea campurilor din mai multe tabele relationate;

Selectarea inregistrarilor care indeplinesc anumite criterii;

Sortarea inregistrarilor dupa unul sau mai multe campuri, ascendent sau descendent;

Introducerea de campuri noi, de tip valoare, rezultate ca urmare a unor calcule.

Sunt mai multe modalitati de a construi o interogare (fig. 6), dintre acestea vom alege modul Design View.

Etapele de realizare a unei interogari sunt urmatoarele (fig. 7):

Objects Queries->New->Design View;

Se deschide fereastra Query Design si fereastra Show Tables/Queries;

Se adauga campurile din tabele in grila de iesire (prin tragere cu mouse-ul, dublu click pe camp, selectare in rubrica Field);

Se definesc campurile si ordinea de sortare (randul Sort - Ascending sau Descending);

Se indica afisarea sau nu a fiecarui camp (randul Show- caseta de validare);

Se stabilesc criteriile de selectie (randul Criteria);

Se adauga campurile noi, calculate;

Se executa interogarea activand butonul Run sau optiunea Query->Run.

Se salveaza interogarea, activand butonul Save sau optiunea File->Save, daca se considera ca raspunde cerintelor de proiectare.

Pentru definirea criteriilor de selectie multiple exista doua variante:

introducerea lor in campurile respective pe aceeasi linie (Criteria), ceea ce implica selectarea inregistrarilor care indeplinesc toate criteriile mentionate;

introducerea lor in campurile respective pe linii diferite (linia Criteria si liniile or), ceea ce implica selectarea inregistrarilor care indeplinesc unul dintre criteriile mentionate.

Conditiile de selectie sunt specificate cu ajutorul unor operatori de comparare, cum ar fi: <, >, =, <>, Like (comparare cu un nume generic scris cu caractere de inlocuire), Between (valoare incadrata intr-un interval dat).

Cateva exemple de criterii de selectie

< Date()-30 'in campul respectiv data sa fie cu 30 zile in urma

Like "Pop*" 'se accepta numele care incep cu Pop: Popa, Popescu

Between 1 and 100 'numere cuprinse intre 1 si 100

In criteriile de selectie se folosesc constante sau parametri, a caror valoare se precizeaza de catre utilizator in timpul executiei. De exemplu, se poate inlocui criteriul din imagine prin secventa:

Between [param1] and [param2]

In cadrul interogarilor exista posibilitatea adaugarii de campuri calculate, asa cum este campul total de plata din fig.7. Pentru aceasta, intr-o coloana libera se tasteaza numele campului (diferit de cele existente in tabele), urmat de semnul ":" si formula de calcul prin care se obtine. In imaginea de mai sus, campul calculat este rezultatul adunarii campurilor rata] si [dob].

Pentru obtinerea de campuri calculate de tip numeric se pot folosi operatori aritmetici, iar pentru campurile de tip text se pot folosi operatori de lucru cu siruri de caractere. De exemplu, cu ajutorul operatorului de concatenare (&) se compun campurile Nume si Prenume pentru a rezulta un nou camp, Student:

Student: [Nume]&" "&[Prenume]

Interogari de tip total (Totals) - realizeaza gruparea inregistrarilor, dupa anumite campuri si aplica functii de insumare, contorizare, medie aritmetica etc asupra valorilor unui camp din gruparea respectiva.

Pentru a crea o interogare de acest tip se procedeaza astfel (fig. 8):

Se realizeaza o interogare de selectie simpla in care sunt specificate campurile pe care se face gruparea si campurile asupra carora se aplica functiile;

Se activeaza butonul Totals ceea ce determina aparitia in grila Query a randului Total;

In coloana campului totalizat se alege functia agregat care se va aplica:

ð     Count - numararea inregistrarilor din grupare

ð     Sum calcularea totalului valorilor din grupare

ð     Avg - calcularea mediei aritmetice a valorilor din grupare

ð     StDev - calcularea deviatiei standard a valorilor din grupare

ð     Min sau Max - identificarea valorii minime sau maxime

ð     First sai Last - selectarea primei sau ultimei valori din grupare, conform ordinii de sortare

ð     Expression - crearea unei expresii calculate in campul respectiv;

ð     Where - indica utilizarea campului numai pentru criterii de selectie (nu este afisat la executia interogarii)

Observatie: In cazul optiunii Count si Avg inregistrarile care contin valoarea Null nu sunt luate in consideratie.

In exemplul din fig. 8 se face totalul valorilor ratei (campul rata) si dobanzii (campul dob) pentru fiecare numar de contract (campul nrc). Se observa ca nu a mai fost selectat campul luna, deoarece el ar fi schimbat continutul interogarii, in sensul ca s-ar fi facut un total pe contract si pe luna al ratelor si dobanzilor.

La executia interogarii, campul rata ar avea titlul SumOfrata, iar campul dob ar avea titlul SumOfdob. Pentru a schimba aceste titlurile implicite, am adaugat in linia Field, in fata denumirii campurilor, noua lor denumire, Total rate si Total dobanzi, urmata de semnul ":".

Facem precizarea ca este logic ca un camp sa apara de doua ori in interogarea de tip Total daca el constituie, in acelasi timp si criteriu de grupare si camp caruia i se aplica o functie agregat.

Fig. 8 Construirea interogarii de tip Total

Interogari de tip tabel incrucisat (Crosstab) - reprezinta o varianta a interogarilor de tip Totals in care valorile unuia dintre campurile Group By se transforma in titluri ale coloanelor, in timpul executiei interogarilor. Ele grupeaza datele in doua moduri si afiseaza o valoare in punctul de intersectie.

Sunt definite trei tipuri de campuri:

ð     Unul sau mai multe campuri care sa furnizeze titlurile de rand (Row Heading) la afisarea datelor;

ð     Un singur camp, obligatoriu, care sa furnizeze titlurile de coloana (Column Heading) la executia interogarii; campul respectiv trebuie sa contina un numar limitat de valori, pentru ca interogarea sa aiba sens;

ð     Un singur camp, obligatoriu, care sa furnizeze valorile de afisat (Value) la intersectia dintre linia si coloana respectiva; valorile sunt obtinute prin aplicarea unei functii agregat (Sum, Count, Avg) campului respectiv.

Access ofera posibilitatea creerii interogarilor de tip tabel incrucisat printr-un program Wizard, Crosstab Query Wizard sau in modul Design View, optiunea Query - Crosstab. Vom aborda in lucrare cea de a doua modalitate de lucru.

Pentru a crea o interogare de tip Crosstab in modul Design View se procedeaza astfel (fig. 9):

Se realizeaza o interogare de selectie simpla in care sunt specificate campurile pe care se face gruparea si campurile asupra carora se aplica functiile;

Optional, se activeaza butonul Totals pentru a selecta campul si functia agregat corespunzatoare;

Se activeaza interogarea de tip tabel incrucisat fie din butonul Query Type, fie din meniul Query, optiunea Crosstab Query; apare linia Crosstab in grila interogarii;

Se stabilesc campurile Row Heading (pot fi mai multe), Column Heading (obligatoriu unul) si Value (obligatoriu unul si anume cel caruia i s-a atasat functia agregat);

Se executa interogarea (butonul Run) si se salveaza (butonul Save).

La sfarsitul capitolului va fi prezentat un exemplu de baza de date, care va contine si interogari de selectie simple, de tip Total si Crosstab.

Interogari de actiune - au ca efect modificarea structurii sau a datelor din baza de date. Sunt 4 tipuri de interogari de actiune:

1. Make Table - creaza o tabela noua pornind de la alte tabele existente;
2. Append - adauga inregistrari, obtinute prin preluarea valorilor din alte tabele, intr-o tabela existenta;
3. Update - actualizeaza (modifica) valoarea din campurile specificate;
4. Delete - sterge inregistrari selectate dintr-o tabela.

Executia interogarilor de actiune este precedata de un mesaj de avertisment, daca optiunea SetWarnings nu a fost dezactivata:

"Are you sure you want to run this action query?"

Este indicat sa se vizualizeze inregistrarile selectate de interogarea de actiune, prin deschiderea interogarii in modul Design, trecerea in modul de afisare Datasheet si executia cu Run.

Access nu permite anularea efectului interogarilor de actiune. Modificarile aduse bazei de date sunt permanente si ireversibile. De aceea este bine sa existe o copie de siguranta a bazei de date care sa permita revenirea la starea anterioara.

1. Make Table - extrage datele din mai multe tabele relationate si creaza o tabela noua in baza de date curenta sau intr-o alta baza de date. De cate ori se executa, se sterge tabela anterioara si se rescrie. Se creaza astfel:

se construieste o interogare de selectie in care se adauga campurile din tabelele sursa;

din meniul Query sau activand butonul Query Type se alege optiunea Make Table;

apare o caseta de dialog in care se precizeaza numele noii tabele create.

Se utilizeaza in special pentru crearea tabelelor temporare, care se sterg frecvent. Crearea si eliminarea frecventa a tabelelor temporare conduce la marirea dimensiunii spatiului alocat bazei de date. De aceea se recomanda compactarea periodica a bazei de date sau crearea tabelelor    temporare intr-o alta baza de date.

2. Append - adauga inregistrari din mai multe tabele relationate intr-o tabela existenta. Etapele de creare sunt:

se construieste o interogare de selectie in care se adauga campurile din tabelele sursa;

din meniul Query sau activand butonul Query Type se alege optiunea Append;

in caseta de dialog se precizeaza numele tabelei destinatie;

apare linia Append To unde se completeaza automat campurile destinatie care au numele identic cu al campurilor sursa sau se completeaza de catre proiectant, daca numele nu coincid.

Se recomanda precizarea unor chei primare in tabela destinatie, care sa nu permita dublarea inregistrarilor.

O utilizare frecventa a interogarilor de tip Append este la arhivarea datelor.

3. Update - selecteaza anumite inregistrari dintr-o tabela pe baza de criterii si actualizeaza valoarea unui camp. Se creaza ca orice interogare de selectie, devenind efectiv o interogare de tip Update la activarea optiunii Update din meniul Query:

se construieste o interogare de selectie in care se adauga campul de actualizat si campul care furnizeaza criteriul de selectie;

din meniul Query sau activand butonul Query Type se alege optiunea Update;

apare linia Update To unde se completeaza noile valori.

Daca se face actualizarea unui camp care este cheie primara intr-o tabela si relatia intre tabele prevede actualizari in cascada (Cascading Updates) se preiau aceste modificari si in tabelele unde campul respectiv este cheie straina.

Pentru a avea siguranta ca sunt selectate corect inregistrarile se executa interogarea initial in modul Datasheet View.

3. Delete - permite stergerea dintr-o tabela a inregistrarilor care indeplinesc o anumita conditie. Crearea unei interogari Delete:

se pleaca de la o interogare de selectie in care se adauga tabela si se selecteaza campurile care furnizeaza criteriile de selectie ale inregistrarilor;

din meniul Query sau activand butonul Query Type se alege optiunea Delete;

apare linia Where unde se completeaza criteriile.

Pentru selectatia corecta a inregistrarilor care se vor sterge se executa interogarea initial in modul Datasheet View.

Daca tabela in care se sterg inregistrari este implicata in relatii de tip one-to-many cu alte tabele si a fost prevazuta optiunea de stergere in cascada (Cascading Deletes) atunci se vor sterge si inregistrarile din tabelele relationate.