CREAREA SI UTILIZAREA INTEROGARILOR

CREAREA sI UTILIZAREA INTEROGĂRILOR

Interogarile (queries) reprezinta poate cel mai interesant si complex tip de obiecte ale oricarei baze de date, deoarece prin intermediul lor se pot procesa, sintetiza si utiliza într-un mod superior datele existente în tabele.

La fel ca si la tabele, Access-ul pune la dispozitie instrumente evoluate de proiectare - "vrajitori", ferestre de dialog specializate si alte modalitati de acces la date care fac posibila programarea vizuala si exclud aproape în totalitate necesitatea de a scrie cod. Interogarile Access fructifica pe deplin aceste facilitati si îi ajuta chiar si pe utilizatorii fara cunostinte deosebite în domeniu 21221p156v l informatic, sa poata raspunde cu usurinta unor întrebari complexe legate de datele existente în baza de date.

Chiar daca afisarea directa a rezultatelor interogarilor nu se ridica la rafinamentul si frumusetea celor prezentate prin intermediul formularelor si rapoartelor, ele sunt utile atât specialistilor în etapa de programare cât si utilizatorilor finali. Acestia, nu de putine ori, au nevoie sa interogheze baza de date pentru a obtine informatii ce nu au fost materializate de programatori prin formulare si rapoarte si nu au nevoie ca rezultatele sa fie prezentate într-o forma prea elaborata

Access    este un instrument software evoluat, care face într-adevar posibila programarea vizuala si dezvoltarea rapida a aplicatiilor.

Interogarile sunt utilizate pentru reunirea câmpurilor din mai multe tabele în vederea unei prezentari agregate a informatiilor, pentru selectarea, stergerea sau adaugarea înregistrarilor în/din tabele dupa anumite criterii, pentru crearea sau actualizarea datelor.

Interogarile pot fi de tip:

• Select Query (de selectare)
• Total Query (de totalizare)
• Crosstab Query (de tip cap de tabel cu mai multe intrari)
• Make-Table Query (de creare tabele)
• Update Query (de actualizare)
• Append Query (de adaugare)
• Delete Query (de stergere).

Ultimele 4 tipuri se numesc si interogari de actiune, ele neavând ca efect afisarea unor informatii la iesire, într-o tabela virtuala, ca celelalte de mai sus, ci ocupându-se doar cu modificarea datelor stocate în tabelele din sectiunea Tables.

n exemplele care urmeaza consideram baza de date continând cele patru tabele si relatiile de mai jos:

Continutul celor 4 tabele se considera a fi urmatorul:

Ca si la tabele exista posibilitatea crearii interogarilor cu ajutorul "vrajitorilor" specializati sau prin intermediul machetei de proiectare Design View. Dupa comutarea în panoul obiectelor Queries apare urmatoarea fereastra

Aici cele doua optiuni sunt:

1. Create query in Design View

2. Create query by using wizard

Semnificatia acestor optiuni este similara cu cea de la obiectele Tables. De fapt prezenta lor constituie un standard Access, deoarece ele se vor regasi si în sectiunile Forms si Reports

Prima optiune, cea mai utilizata, este echivalenta cu apasarea butonului Design din ecranul anterior si atunci când este lansata în executie prezinta urmatorul dialog (Show Table

Dupa selectarea tabelelor sau interogarilor dorite pentru a constitui baza de prelucrare ("materia prima") pentru query-ul curent, se apasa butonul <Add>. Selectia poate fi individuala, contigua (cu Shift+clic) sau necontigua (cu Ctrl+clic), fiind similara cu modalitatea de selectie din programul Windows Explorer

În exemplul nostru, s-a dorit o interogare prin care sa obtinem o lista cu numele angajatilor si studiile acestora. În acest scop, înainte de apasarea butonului <Add> s-au selectat (necontiguu folosind tasta Ctrl) cele doua tabele necesare interogarii - Angajati si Studii. Terminarea si închiderea lucrului cu acest dialog de adaugare se marcheaza prin apasarea butonului <Close>.

Urmatoarea fereastra afisata reprezinta macheta de proiectare vizuala a obiectelor de tip query. Se observa ca cele doua tabele au fost aduse automat legate prin relatia care a fost definita si care exista în Relatioships

În partea de jos a acestei machete se gaseste un tabel vid (cu liniile: Field, Table, Sort, Show, Criteria, or), în ale carui coloane vor trebui trase cu mouse-ul prin drag & drop câmpurile din tabelele (din partea de sus), ale caror valori vor fi afisate sau vor servi drept criterii de interogare. În cazul nostru vor fi trase câmpurile Nume si Prenume din tabela Angajati si Denumire studii din tabela Studii. În urma acestor actiuni macheta de proiectare va arata astfel:

Dupa apasarea butonului View (primul buton din bara de instrumente - vezi imaginea anterioara), pe ecran va fi afisata urmatoarea lista cu rezultatele interogarii:

Se observa ca sunt afisate doar cele doua câmpuri (coloane) selectate. Relatia dintre tabele

realizata prin intermediul câmpului cod studii, asigura posibilitatea afisarii studiilor corespunzatoare pentru fiecare persoana chiar daca valorile acestui câmp nu apar explicit.

Oricând se poate reveni în macheta de proiectare prin apasarea primului buton din bara de instrumente, care acum contine simbolul unui echer albastru. Rezultatul interogarii se poate previzualiza înaintea listarii sau se poate lista efectiv la imprimanta prin intermediul butoanelor corespunzatoare din toolbar (sunt butoanele standard care contin simbolurile imprimanta si lentila

Continuând exemplul, sa presupunem ca managerul de resurse umane solicita o alta ordonare a listei. El cere ca lista sa fie sortata dupa studii si anume întâi cele superioare, apoi studiile medii si fara, iar în cadrul aceluiasi tip de studii numele persoanelor sa apara sortate alfabetic. Deci "în traducere" aceasta înseamna o tripla sortare. Prima va fi o sortare descrescatoare (descending) dupa câmpul cod studii (deoarece 2=studii superioare, =studii medii, 0=fara studii), a doua, o sortare crescatoare alfabetic (ascending) dupa câmpul Nume iar la eventuale nume identice (de familie) dupa Prenume.

Cele trei câmpuri (cod studii si Nume, resp. Prenume) vor fi chei de sortare în cadrul acestei interogari.

Ordinea de plasare a cheilor în interogare este importanta. Prioritatea cheilor descreste de la stânga la dreapta (prima cheie, cea mai importanta cod studii va fi plasata cel mai la stânga în tabelul machetei de proiectare, urmându-i Nume si apoi Prenume).

Tabelul din macheta de proiectare ofera pentru sortare linia (optiunea) Sort iar pentru marcajul câmpurilor (coloanelor) vizibile la consultarea interogarii, linia Show cu controale de marcare (check box). Câmpurile în dreptul carora exista bifa pe linia Show, vor fi vizibile. Initial toate câmpurile au prezenta bifa în dreptul lor pe linia Show (deci în mod implicit vor fi vizibile toate coloanele interogarii) . În cazul nostru pentru ca nu dorim sa fie vizibile valorile câmpului cod studii sau 2) vom debifa respectivul control de marcare.

Macheta de proiectare va fi:

Dupa apasarea butonului View (primul din stânga pe toolbar) rezultatul interogarii va fi urmatoarea lista dublu sortata

Sa presupunem ca se solicita o situatie mai complexa care pe lânga nume, prenume si studii sa afiseze functia detinuta si data angajarii. Lista trebuie sa fie sortata în ordinea crescatoare a functiilor, apoi în cadrul fiecarei functii în ordine alfabetica dupa numele persoanelor.

În acest caz, deoarece se solicita informatii despre functiile detinute, este necesara adaugarea în interogare a tabelei Functii. Dialogul de adaugare Show Table se apeleaza prin apasarea butonului cu acelasi nume din bara de instrumente sau alegând optiunea Show Table la clic dreapta de mouse în yona de sus a ferestrei Query1 .

Acest dialog este:

Dupa adaugarea tabelei Functii se trag în tabel prin "drag & drop" câmpurile necesare interogarii si se stabilesc cheile de sortare. Macheta de proiectare va arata astfel:

La apasarea butonului View va fi afisata urmatoarea lista

Un query poate fi salvat prin procedurile standard Save sau Save as care au fost prezentate în capitolul anterior referitor la tabele. Orice query salvat poate deveni o sursa de date (similar unei tabele) pentru un alt query, un form sau un report

Orice interogare realizata în maniera vizuala sau cu ajutorul vrajitorilor specializati se traduce de catre Access într-o comanda SQL (vizibila la alegerea optiunii SQL View din meniul obtinut la clic dreapta de mouse în zona de sus a ferestrei Query, meniu prezentat mai jos:

.

Iata codul SQL creat automat de catre Access pentru ultima interogare:

SELECT Angajati.Nume, Angajati.Prenume, Functii.[Denumire functie], Studii.[Denumire studii], Angajati.[data ang]

FROM Studii INNER JOIN (Functii INNER JOIN Angajati ON Functii.[Cod functie] = Angajati.[cod functie]) ON Studii.[cod studii] = Angajati.[cod studii]

ORDER BY Angajati.[cod functie], Angajati.Nume, Angajati.Prenume;

Trebuie remarcata cu aceasta ocazie puterea Access-ului ca instrument rapid de dezvoltare vizuala a aplicatiilor, faptul ca da posibilitatea unui numar mare de persoane care nu sunt programatori de meserie sa realizeze prelucrari complexe ale datelor.

Interogarile create pâna acum si utilizate pentru exemplificari fac parte din interogarile de tip Select. Vom prezenta în continuare toate tipurile de interogari puse la dispozitie de Access

INTEROG RILE DE TIP SELECT

Interogarile de tip Select, la fel ca orice alt tip de interogari, presupun existenta în cadrul machetei de proiectare a:

• tabelelor sau interogarilor care constituie sursa de date,
• relatiilor dintre acestea mostenite din Relationships
• tabelului cu:

câmpurile,

cheile de sortare,

marcatorii de vizibilitate (Show

criteriile (conditiile) de selectie.

Ele se pot utiliza pentru extragerea de date din una sau mai multe tabele (query-uri) si efectuarea unor calcule.

Orice cod SQL al query-urilor de tip Select începe evident cu comanda Select (vezi codul SQL din subcapitolul anterior).

Selectiile pot fi facute si pe baza anumitor criterii utile pentru filtrarea datelor. Linia Criteria din tabelul existent în macheta de proiectare este destinata special acestui scop. Scrierea conditiilor complexe este asistata de un vrajitor specializat care poate fi apelat apasând optiunea Build din meniul contextual al câmpului în care se scrie conditia, de pe linia Criteria.

Rezultatele interogarii de selectie pot fi prezentate sortate crescator sau descrescator dupa unul sau mai multe câmpuri. Alegerea câmpurilor respective se face în linia Sort. Daca de exemplu se doreste ca ultima lista (creata în subcapitolul anterior) sa afiseze doar persoanele cu studii medii, atunci va fi necesara aplicarea unui asemenea filtru de selectie.

Macheta de proiectare va fi:

Se observa existenta cifrei pe linia Criteria în dreptul câmpului cod studii. Aceasta actiune simpla determina filtrarea si deci afisarea doar a înregistrarilor care corespund conditiei (adica au cod studii ceea ce corespunde studiilor medii). Efectul aplicarii filtrului poate fi observat în urmatoarea figura

Lucrul cu functia like

Pot exista si filtre multiple. De exemplu, este posibil ca în vederea sarbatoririi Sfântului Ion, sa se solicite o lista care sa afiseze doar persoanele cu studii superioare (cod studii=2) care au inclus în prenume subsirul Ion. În acest sens se va utiliza o interogare de selectie având drept criteriu de selectie pentru nume, functia like (însemnând ca si) cu urmatorii parametrii: like "*Ion*"

Caracterul * (se citeste orice), are aceeasi semnificatie ca si la filtrele pentru fisiere exemplu *.exe, *.*, etc . Deci, criteriul de selectie pentru nume like "*Ion*" se poate citi: "toate numele persoanelor care au orice sir de caractere înainte de sirul Ion si orice sir de caractere dupa sirul Ion

Macheta de proiectare pentru aceasta interogare va fi:

Rezultatul interogarii este:

Daca se doreste o lista care sa contina toate numele care încep cu "Ion", comanda like din linia Criteria este: like "Ion*". Invers, daca se doreste o selectie pentru toate numele care se termina cu caracterele "ion" atunci comanda like este: like "*ion"

Iata în continuare efectul ultimei comenzi (like "*ion"

Daca se doreste o interogare a persoanelor cu studii superioare care contin subsirul Ion fie în nume, fie în prenume, machete de cautare va fi cea din figura de mai jos, care are completata atât linia Criteria, cu o prima conditie compusa (prenumele sa contina subsirul Ion si persoana sa aiba studii superioare - cod studii=2), cât si linia Or (sau) cu cea de-a doua conditie posibila compusa (adica persoana sa contina în nume subsirul Ion si sa aiba studii superioare).

Concatenarea sirurilor de caractere

Sub Access sirurile de caractere se pot concatena. Operatorul de concatenare este "&". Daca avem doua siruri: " si "qwe" atunci rezultatul concatenarii lor prin intermediul operatorului de concatenare (" 23" & "qwe") este: " 23qwe".

Selectiile anterioare bazate pe functia like puteau fi scrise si astfel:

like "*" & "ion" & "*"

like "ion" & "*"

like "*" & "ion"

Dialogurile parametrizate

Exista situatii în care selectia nu se face de fiecare data dupa aceleasi caractere ce sunt continute în nume. În acest caz, se lasa operatorului sarcina de a introduce de la tastatura succesiunea de caractere ce sunt cautate. Acest lucru se poate realiza scriind parametrizat criteriul de selectie în linia Criteria pentru câmpul nume sau prenume. La fiecare lansare în executie a interogarii se afiseaza o fereastra de dialog prin intermediul careia utilizatorul specifica sirul de caractere ce constituie baza de selectie.

Textul afisat care va aparea în acest dialog ar putea fi:

Introduceti caracterele incluse in nume:

În linia Criteria textul trebuie cuprins între paranteze drepte:

Introduceti caracterele incluse in prenume:

Comanda like exhaustiva care prin intermediul parametrizarii va cuprinde toate cazurile de selectie pentru câmpul prenume este:

Like "*" & Introduceti caracterele incluse în prenume:] & "*"

Secventa corespunzatoare din macheta de proiectare este:

Dialogul care îi va aparea utilizatorului la lansarea interogarii este:

Caseta în care poate fi introdus orice subsir de caractere cautat în prenume.

Lucrul cu functia between

Daca se doreste o lista care sa prezinte o selectie a angajatilor doar pentru categoriile functiilor de sefi atunci va mai trebui introdusa în interogare si tabela Categorii functii. Continutul acestei tabele este:

Se observa ca în aceasta tabela câmpul cod categorie pentru functiile de sefi are valori cuprinse între 2 si 4. Deci, pentru selectia categoriilor de functii de sefi va fi necesara utilizarea functiei Between (între).

Macheta de proiectare pentru interogare este:

Rezultatul interogarii va fi:

INTEROG RILE DE TIP TOTALS

Alte modalitati de punere a problemelor de interogare sunt cele referitoare la gruparea datelor din tabele dupa diferite criterii si prezentarea unor date statistice despre acestea. Pentru fiecare din grupele ce se formeaza se pot calcula indicatori cum sunt:

• Sum( ) - suma valorilor unui câmp,
• Count( ) - numarul înregistrarilor din fiecare grupa
• Avg( ) - media valorilor unui câmp pentru fiecare grupa
• StDev( ) - abaterea standard a valorilor unui câmp pentru fiecare grupa
• Var( ) - dispersia valorilor unui câmp pentru fiecare grupa
• Min Max( ) - valoarea minima / maxima
• Last( ) - ultimul, First( ) - prima / ultima valoare din grupa pentru câmpul respectiv.

Sa presupunem ca avem urmatorul query de tip Select

Oricând un query de tip Select poate fi transformat într-un query de tip Totals prin apasarea butonului din bara de instrumente:     .

Dupa apasarea butonului , macheta de proiectare va deveni:

Se observa în tabelul din partea de jos a machetei aparitia liniei Total care are înscrisa functia Group By în dreptul fiecarui câmp. Daca dorim o situatie care sa ne prezinte câte persoane din fiecare categorie de functii exista în institutie, atunci înseamna ca dorim o grupare a lor (Group By) dupa categoria de functie si apoi o numarare (contorizare - Count) a acestora.

Pentru rezolvare, în dreptului câmpului cod persoana (deoarece dorim sa numaram persoane), pe linia Total vom face clic cu mouse-ul pe controlul combo box disponibil, ceea ce va determina deschiderea urmatoarei liste derulante din care vom alege functia Count:

În dreptul câmpului cod angajat pe linia Total va aparea în loc de Group By, functia Count

Rezultatul acestui query va fi:

Codul SQL generat automat de Access pentru aceasta interogare este:

SELECT [Categorii functii].[Denumire categorie functie], Count(Angajati.[Cod angajat]) AS [CountOfCod angajat]

FROM ([Categorii functii] INNER JOIN Functii ON [Categorii functii].[cod categ functie] = Functii.[cod categorie functie]) INNER JOIN Angajati ON Functii.[Cod functie] = Angajati.[cod functie]

GROUP BY [Categorii functii].[Denumire categorie functie], [Categorii functii].[cod categ functie];

O alta întrebare la care raspunsul ar fi usor de dat cu ajutorul interogarilor de tip Totals ar fi: "Câti angajati cu studii medii si câti cu studii superioare sunt în institutie?".

Macheta de proiectare este:

Se observa ca pe linia Criteria în dreptul câmpului cod studii s-a înscris conditia >0. În acest fel se selecteaza doar persoanele cu studii superioare si cele cu studii medii (persoanele fara studii au câmpul: cod studii=0

Raspunsul la întrebare (rezultatul interogarii) va fi: