LUCRUL CU INTEROGARILE SIMPLE DE SELECTARE

Lucrul cu interogarile simple de selectare

Scopul lucrarii

Lucrarea are ca scop familiarizarea studentilor cu crearea si lucrul cu interogarile de selectare in Microsoft Office 2007.

Notiuni teoretice

O interogare (query) este o intrebare" cu privire la datele stocate in baza de date, stocata si ea in baza de date. Aceasta identifica obiectele care contin datele si campurile care intereseaza si apoi foloseste functii, expresii si instructiuni SQL pentru a formula intrebarea cu privire la datele respective.

In Acces pot fi create mai multe tipuri de interogari. Dintre acestea, interogarea simpla de selectare (select query) este cel mai utilizat tip.

O interogare de selectare afiseaza informatii in vizualizarea Datasheet si este utilizata fie pentru a crea subseturi de date care pot fi utilizate pentru a raspunde la anumite intrebari, fie pentru a furniza date altor obiecte de baze de date.

Sursele de inregistrari din care interogarile ob in datele pot fi tabele, alte interogari sau combinatii de tabele si inregistrari.

Interogarea poate fi creata cu una din cele doua optiuni, Query Wizard sau Query Design, existente in fila Create, grupul Other (fig. t1).

Crearea unei interogari de selectare cu expertul Query Wizard

Dupa apasarea butonului Query Wizard, din fereastra care apare (fig. t2) se selecteaza tipul de interogare Simple Query Wizard, apoi, din noua fereastra de dialog (fig. t3) se alege tabelul/interogarea din care vor fi preluate campuri si, cu butoanele (pentru preluarea unui camp) sau (pentru preluarea tuturor campurilor) se adauga campurile in lista Selected Fields.

Urmatoarea fereastra de dialog (fig. t4) este ultima si ofera posibilitatea de a schimba numele interogarii si de a o deschide in unul din cele doua moduri: Datasheet (prin optiunea implicita Open the query to view information) sau Design (prin optiunea Modify the query design).

Alegerea optiunii Open the query to view information conduce la vizualizarea rezultatelor, fara a putea limita numarul de inregistrari intoarse ca rezultat.

Pentru a restrange inregistrarile care sunt returnate in rezultatele interogarii, respectiv specificarea unor criterii, se alege optiunea Modify the query design (pentru criterii, a se vedea §t2.6 ).

Nota 1: Rezultatele interogarii se pot vedea numai in vizualizarea Datasheet, deci modalitatea de a rula o interogare este trecerea acesteia in acest mod de vizualizare.

Nota 2: In orice moment, se poate comuta interogarea dintr-un mod de vizualizare in altul, Datasheet - pentru rulare, Design - pentru proiectare (pentru comutare, a se vedea § 1.2.4 si § 1.2.6).

Crearea unei interogari de selectie in modul de vizualizare Design

Crearea unei interogari direct in vizualizarea Design ofera, inca de la inceput, accesul la toate facilitatile de proiectare, inclusiv specificarea unor criterii.

Dupa selectarea optiunii Query Design din fila Create, grupul Other, sunt solicitate sursele de inregistrari (tabele si interogari) prin fereastra Show Table (fig.t5).

Nota: In orice moment, poate fi afisata fereastra Show Table pentru a adauga o noua sursa de inregistrare, printr-un clic pe butonul Show Table din grupul Query Setup al filei Design.

Apoi, apare grila de proiectare, care este formata din doua panouri (fig. t6).

Panoul superior afiseaza toate sursele de inregistrari cu listele de campuri pe care le contin, precum si relatiile dintre tabelele respective.

Panoul inferior afiseaza campurile (Field) cu care s-a stabilit ca se lucreaza si pentru fiecare dintre acestea:

- celula Table identifica sursa de inregistrare din care provin (tabel sau interogare);

- celula Sort specifica ordinea de sortare (Ascending sau Descending);

- celula Show, care contin casete de selectie, determina daca interogarea afiseaza datele campului respectiv;

- celula Criteria permite scrierea unei expresii care constituie un criteriu, necesar daca se doreste restrangerea datelor furnizate de interogare;

- celula Or este o extensie a celulei Criteria, care permite introducerea mai multor conditii pentru un camp.

Adaugarea campurilor in grila de proiectare

Exista patru metode pentru a adauga campuri, unul cate unul, din sursele de inregistrare afisate in panoul superior al grilei de priectare, in celulele Field din panoul inferior al acesteia.

1. Se trage cu mouse-ul numele campului din panoul superior in celula Field a panoului inferior.

2. Se executa dublu clic cu mouse-ul pe numele campului din panoul superior, iar Acces il va adauga in prima celula Field gasita goala.

t Se tasteaza numele campului in celula Field.

4. Se selecteaza campul dorit din lista derulanta asociata celulei Field.

Pentru a adauga, printr-o singura actiune, toate campurile unei surse de inregistrare din panoul superior, in celulele Field din panoul inferior, exista urmatoarele doua posibilitati.

1. Se trage cu mouse-ul campul asterisc ( ) din lista de campuri in celula Field. Acelasi efect se obtine si daca se efectueaza un dublu clic pe campul asterisc. Ca efect, in celula Field respectiva va aparea numele generic al tuturor campurilor din tabel (de exemplu TblStudenti.*). Limitarea este insa esentiala si consta in lipsa de flexibilitate legata de faptul ca rezultatul interogarii afiseaza toate campurile, nefiind posibila utilizarea sortarii si a functiilor globale.

2. Se efectueaza un dublu clic pe bara de titlu a sursei de inregistrare dorite din panoul superior pentru a selecta toate campurile din lista si apoi se trage cu mouse-ul intreaga selectie intr-o celula Field a panoului de jos. Ca efect, fiecare camp selectat va aparea in propria celula Field, ca si cand adaugarea ar fi fost facuta camp cu camp.

Executarea interogarii

Executarea (rularea) unei interogari este posibila din modul de vizualizare Design si determina trecerea in modul de vizualizare Datasheet.

Pentru rulare, exista doua posibilitati:

1. Se apasa butonul Run din grupul Results al filei Design.

2. Se comuta, prin una din modalitatile cunoscute, in vizualizarea Datasheet.

Sortarea inregistrarilor dintr-o interogare

Rezultatul interogarii poate fi sortat dupa datele din campuri, cu exceptia campurilor de tip Memo, si va influenta toate obiectele Acces (formulare, rapoarte) bazate pe aceasta interogare.

Tipul de sortare se selecteaza din lista derulanta asociata celulei Sort a fiecarui camp din panoul inferior al grilei de proiectare si poate fi:

Ascending - crescatoare;

Descending - descrescatoare;

(not sorted) - nesortata.

Nota: Articolele nule sunt afisate primele la o sortare Ascending si ultimele la o sortare Descending.

Stabilirea criteriilor pe campuri

Un criteriu de interogare este o condi ie, bazata pe valorile unui camp, folosita pentru a restrange numarul de inregistrari care sunt returnate in rezultatul interogarii.

Expresiile pentru criterii de camp pof fi introduse numai in vizualizarea Design a interogarii (fig. t6), in celula Criteria si/sau celula Or, care constituie o extensie a celulei Criteria. Numai inregistrarile care intalnesc criteriile definite in randul Criteria sau in randul Or vor fi incluse in rezultat.

Daca se doreste o zona mai mare in care sa se tasteze expresia, dupa pozitionarea mouse-ului in celula de criteriu sau criteriu alternative, se foloseste combinatia de taste Shift+F2 pentru a afi a caseta Zoom. Ca alternativa, se poate selecta optiunea Zoom din meniul de comenzi rapide afisat cu un clic dreapta al mouse-ului pe celula de criteriu.

Nota: Pentru a specifica mai mult de doua seturi de criterii alternative, se pot utiliza liniile de sub linia Or, fiecare linie reprezintand un set independent de criterii.

Intr-o interogare simpla de selectie, cele mai utilizate criterii sunt cele care gasesc text partial sau integral in campuri (tabelul t1).

Tabelul t1 Expresii uzuale pentru criterii de interogare pe campuri text

Nota: Metacaracterul inlocuieste un sir oarecare de caractere si se foloseste pentru inceputul sau sfarsitul sirului, iar metacaracterul inlocuieste un singur caracter.

Pentru construirea expresiilor complexe, se poate folosi instrumentul Expression Builder (fig. t7) care se afiseaza in doua moduri, cu conditia ca cursorul sa fie pozitionat in celula de criteriu (sau intr-o celula Field pentru un camp de calcul) :

alegand optiunea Build din meniul de comenzi rapide, afisat cu un clic dreapta al mouse-ului (fig. t8a);

apasand butonul Builder din grupul Query Setuo al filei Design (fig. t8b).

Caseta din zona 1, care este goala la inceput, este zona in care apare expresia, pe masura ce este construita si in care se poate si tasta expresia sau o parte a ei.

In zona 2 este un sir de butoane cu operatorii (matematici, logici si de grupare) cel mai frecvent utilizati in scrierea expresiilor.

Zona formata din casetele 3, 4 si 5 contine elementele ce pot fi selectate pentru a fi utilizate in expresii. Caseta 3 contine o serie de foldere cu obiectele din baza de date (tabele, interogari, formulare, rapoarte, functii, constante, operatori si expresii frecvent utilizate). Caseta 4 afiseaza continutul folderului deschis in caseta 3, iar caseta 5 este o lista cu valorile posibile ale elementului selectat in caseta 4.

Adaugarea calculelor la interogare

O interogare poate furniza dupa rulare si calcule obtinute pe baza datelor din sursele de inregistrari.

Pentru a adauga un calcul la interogare, in vizualizarea Design, in prima celula libera din linia Field, se tasteaza expresia dorita ce poate folosi functii Acces, eventual precedata de numele noului camp urmat de caracterul " : ".

Daca nu este furnizat un nume pentru noul camp, Access va utiliza un nume generic, de exemplu Expr1.

Nota: Ca si la scrierea criteriilor pentru campuri, poate fi folosita fereastra Zoom sau

instrumentul Expression Builder.

Sintetizarea campurilor

Dupa executarea interogarii, in vizualizarea Datasheet, exista posibilitatea adaugarii unul rand special, denumit rand Total si afisat sub randul asterisc (*), in care sa se afiseze valori agregate pentru fiecare coloana. Aceasta inseamna sintetizarea coloanelor, nu neaparat toate.

Valorile agregate sunt rezultatul functiilor agregate, care efectueaza calcule in grupuri de inregistrari in coloane de date i returneaza un singur rezultat.

Pentru afisarea randului Total, in vizualizarea Datasheet, fila Home, grupul Records, se apasa butonul Totals (fig. t9).

Apoi, in randul Total, se face clic pe celula din campul care se doreste a fi sintetizat si, din lista derulanta care apare (fig. t10), se selecteaza functia agregata dorita.

Functiile agregate, actiunea realizata si tipul de date pentru care pot fi folosite sunt sintetizate in tabelul t2.

Nota: Sintetizarea campurilor prin functii agregate este posibila intotdeauna cand datele apar in vizualizarea Datasheet (pe linii si coloane), deci si pentru tabele, nu numai pentru interogari.

Tabelul t2 Functiile agregate Acces

Nota: In vizualizaurea Design a interogarii, exista butonul Totals in grupul Show Hide al filei Design (fig. t11).

Acesta lucreaza ca un comutator si permite afisarea/ascunderea unui rand suplimentar de celule numit Total, din care, cu un clic dreapta al mouse-ului se poate alege (fig. t12) ce tip de contorizare a datelor se realizeaza.

Ca efect, rularea interogarii va determina ca, in fiecare celula a coloanei (campului) respective, sa fie contorizate numai datele care sunt identice in toate liniile (inregistrarile) pentru campurile alese sa fie surse de inregistrari ale interogarii. Nu este vorba de o contorizare a tuturor datelor (inregistrarilor) din camp (coloana), ci numai a celor identice in interogarea curenta. In plus, titlul coloanei se modifica in mod automat prin adaugarea unui prefix de felul SumOf sau AvgOf, in functie de tipul de contorizare selectat. Acest nume poate fi modificat oricand prin suprascriere.

Functii utilizate in expresii

IIf(Expresie; Adevarat Fals) - returneaza valoarea continuta fie in argumentul Adevarat, fie in argumentul Fals, in functie de rezultatul evaluarii expresiei continute in argumentul Expresie.

Val(Sir) - converteste sirul de caractere alfanumerice dintre paranteze intr-un numar.

CStr(Numar) - converteste numarul dintre paranteze intr-un sir de caractere.

Mid(Text; Start; Lungime) - extrage din sirul de caractere Text un numar de caractere determinat de argumentul Lungime, incepand cu pozitia determinata de argumentul Start.

Date() - furnizeaza data curenta.

Year(Date) - furnizeaza anul continut in argumentul Date.

Chestiuni de studiat

tt1. Pe baza tabelului TblStudenti, folosind expertul Query Wizard, se va crea crea o interogare de selectare care sa afiseze numele, prenumele si sectia tuturor studentilor din baza de date. Acestia vor fi ordonati alfabetic, dupa nume.

tt2. Pe baza tabelului TblStudenti, direct in vizualizarea Design, se va crea crea o interogare de selectare care sa afiseze numele, prenumele si sectia tuturor fetelor din baza de date. Rezultatele vor fi ordonate alfabetic pe sectii.

ttt Pe baza celor trei tabele (TblStudenti, TblDiscipline si TblNote), in vizualizarea Design, se va crea crea o interogare de selectare care sa afiseze numele, prenumele, denumirea disciplinei, nota si numarul de credite pentru toti studentii. Rezultatele vor fi ordonate alfabetic, dupa nume.

Dupa executarea interogarii, in vizualizarea Datasheet, va fi realizata sintetizarea urmatoarelor informatii: disciplinele si notele obtinute vor fi numarate, iar creditele posibile de obtinut vor fi insumate.

tt4. Pe baza celor trei tabele, in vizualizarea Design, se va crea crea o noua interogare de selectare care sa afiseze numele, prenumele, nota, dar si un camp nou, respectiv numarul de credite obtinute, calculat pe baza informatiilor din alte doua campuri (Nota si numarul de credite asociate disciplinelor din campul Credite). Inregistrarile asociate aceluiasi nume vor fi contorizate astfel: notele obtinute de fiecare student vor fi numarate, iar creditele obtinute dupa promovare vor fi insumate.

Rezultatele vor fi ordonate alfabetic, dupa nume.

tt5. Se va crea o interogare de selectare, care va afisa numele, prenumele si varsta tuturor studentilor din baza de date.

tt6. Se va crea o interogare de selectare, care va afisa numele, prenumele si numarul de discipline nepromovate de fiecare student.

Modul de lucru

Se lanseaza in executie Microsoft Office Access 2007 si se deschide baza de date de lucru.

In fila Create, grupul Other, se apasa butonul Query Wizard si se parcurg ferestrele de dialog (vezi §t2.1) astfel incat sa se creeze interogarea de selectare solicitata la punctul tt1, atribuindu-i numele QryListaStudenti.

Pentru sortarea ascendenta dupa nume, se trece interogarea in modul Design si, in coloana Nume, linia Sort, se selecteaza optiunea Ascending (vezi §t2.5).

Se ruleaza interogarea (vezi §t2.4).

In fila Create, grupul Other, se apasa butonul Query Design si se parcurg pasii pentru crearea interogarii de selectare (vezi §t2.2) solicitate la punctul tt2.

In grila de proiectare, pe langa campurile Nume, Prenume si Sectie, necesare sa fie afisate dupa rularea interogarii, se adauga si campul CNP, pe baza caruia se va stabili criteriul de restrangere a rezultatelor.

Pentru a afisa doar studentii de sex feminin, se stabileste un criteriu de interogare (vezi §t2.6). Astfel, in coloana CNP, linia Criteria se tasteaza Like 2*. Pentru a nu afisa campul CNP in rezultatul interogarii, se debifeaza optiunea de afisare din caseta Show a campului respectiv.

Pentru sortarea ascendenta pe sectii, in coloana Sectie, linia Sort, se selecteaza optiunea Ascending.

Se salveaza interogarea cu numele QryListaFete si se ruleaza.

In fila Create, grupul Other, se apasa butonul Query Design si se parcurg pasii pentru crearea interogarii de selectare solicitate la punctul ttt

In etapa de stabilire a surselor de inregistrari, selectarea simultana a celor trei tabele din fereastra Show Table se realizeaza fie prin selectare succesiva cu mouse-ul tinand tasta Ctrl apasata, fie prin selectarea primului si ultimului tabel tinand tasta Shift apasata.

In grila de proiectare, se specifica sortarea Ascending dupa campul Nume.

Se salveaza interogarea cu numele QrySintetizareStudenti si se ruleaza.

Pentru sintetizarea campurilor comform cerintelor, in modul de vizualizare Datasheet, se apasa butonul Totals din fila Home, grupul Records. Apoi, in linia Total, la coloanele DenumireDisciplina si Nota se selecteaza optiunea Count, iar la coloana Nota se selecteaza optiunea Sum. Se constata sintetizarea campurilor respective.

Se salveaza interogarea.

In fila Create, grupul Other, se apasa butonul Query Design si se parcurg pasii pentru crearea interogarii de selectare solicitate la punctul tt4. Pentru inceput vor exista 3 cimpuri in interogare: Nume, Prenume, si Nota.

Se adauga la interogare un nou camp, de calcul (vezi §t2.7), denumit CrediteObtinute, care sa valideze valoarea continuta in campul Credite numai daca Nota este mai mare sau egala cu 5. Pentru aceasta, in vizualizarea Design, in prima celula libera din linia Field, se tasteaza expresia:

CrediteObtinute: IIf([Nota]>=5;1;0)*[NrCredite]

Pentru functia IIf, a se vedea §t2.9.

Pentru contorizarea informatiilor (vezi §t2.8), in vizualizarea Design, se apasa butonul butonul Totals din grupul Show Hide al filei Design. In linia Total care apare, se fac urmatoarele selectii: Count pentru coloana Nota si Sum pentru coloana CrediteObtinute.

Se salveaza interogarea cu numele QryContorizareNoteCredite si se ruleaza.

Se creeaza o noua interogare de selectare, pe baza tabelului TblStudenti si campurilor Nume si Prenume, pentru a rezolva cerintele de la punctul tt5.

In grila de proiectare, se creaza un nou camp, cu numele Varsta, care calculeaza varsta fiecarui student pe baza informatiilor continute in cimpul CNP si datei curente. Pentru aceasta, in prima celula libera din linia Field, se tasteaza expresia:

Varsta: Year(Date())-Val(CStr(19) & Mid([CNP];2;2))

care foloseste functiile detaliate in §t2.9.

Se salveaza interogarea cu numele QryVarstaStudenti si se ruleaza.

Pe baza celor trei tabele (TblStudenti, TblDiscipline si TblNote), in vizualizarea Design, se creeaza o interogare de selectare pe baza campurilor Nume, Prenume si DenumireDisciplina.

Se adauga un camp de calcul, cu numele NrDisciplineNepromovate, pentru a determina numarul de discipline nepromovate de fiecare student. Pentru aceasta, in prima celula libera din linia Field, se tasteaza expresia:

NrDisciplineNepromovate: Count([DenumireDisciplina])-Sum(IIf([Nota]>=5;1;0))

Pentru contorizarea informatiilor, in vizualizarea Design, se apasa butonul butonul Totals din grupul Show Hide al filei Design. In linia Total care apare, se fac urmatoarele selectii: Count pentru coloana DenumireDisciplina si Expression pentru coloana NrDisciplineNepromovate.

In linia Show, se debifeaza caseta din dreptul coloanei DenumireDisciplina.

Se salveaza interogarea cu numele QryNumarRestante si se ruleaza.

Se salveaza baza de date si se inchide.