Curs 4: CREAREA ŞI UTILIZAREA INTEROGĂRILOR

Interogările (queries) reprezintă poate cel mai interesant şi complex tip de obiecte ale oricărei baze de date, deoarece prin intermediul lor se pot procesa, sintetiza şi utiliza într-un mod superior datele existente în tabele.

La fel ca şi la tabele, Access-ul pune la dispoziţie instrumente evoluate de proiectare – „vrăjitori”, ferestre de dialog specializate şi alte modalităţi de acces la date care fac posibilă programarea vizuală şi exclud aproape în totalitate necesitatea de a scrie cod. Interogările Access fructifică pe deplin aceste facilităţi şi îi ajută chiar şi pe utilizatorii fără cunoştinţe deosebite în domeniul informatic, să poată răspunde cu uşurinţă unor întrebări complexe legate de datele existente în baza de date.

Chiar dacă afişarea directă a rezultatelor interogărilor nu se ridică la rafinamentul şi frumuseţea celor prezentate prin intermediul formularelor şi rapoartelor, ele sunt utile atât specialiştilor în etapa de programare cât şi utilizatorilor finali. Aceştia, nu de puţine ori, au nevoie să interogheze baza de date pentru a obţine informaţii ce nu au fost materializate de programatori prin formulare şi rapoarte şi nu au nevoie ca rezultatele să fie prezentate într-o formă prea elaborată.

Access este un instrument software evoluat, care face într-adevăr posibilă programarea vizuală şi dezvoltarea rapidă a aplicaţiilor.

Interogările sunt utilizate pentru reunirea câmpurilor din mai multe tabele în vederea unei prezentări agregate a informaţiilor, pentru selectarea, ştergerea sau adăugarea înregistrărilor în/din tabele după anumite criterii, pentru crearea sau actualizarea datelor.

Interogările pot fi de tip: Select Query (de selectare) Total Query (de totalizare) Crosstab Query (de tip cap de tabel cu mai multe intrări) Make-Table Query (de creare tabele) Update Query (de actualizare) Append Query (de adăugare) Delete Query (de ştergere).

Ultimele 4 tipuri se numesc şi interogări de acţiune, ele neavând ca efect afişarea unor informaţii la ieşire, într-o tabelă virtuală, ca celelalte de mai sus, ci ocupându-se doar cu modificarea datelor stocate în tabelele din secţiunea Tables.

În exemplele care urmează considerăm baza de date conţinând cele patru tabele şi relaţiile de mai jos:

1

Conţinutul celor 4 tabele se consideră a fi următorul:

2

Ca şi la tabele există posibilitatea creării interogărilor cu ajutorul „vrăjitorilor” specializaţi sau prin intermediul machetei de proiectare Design View. După comutarea în panoul obiectelor Queries apare următoarea fereastră:

Aici cele două opţiuni sunt:1. Create query in Design View2. Create query by using wizard

Semnificaţia acestor opţiuni este similară cu cea de la obiectele Tables. De fapt prezenţa lor constituie un standard Access, deoarece ele se vor regăsi şi în secţiunile Forms şi Reports.

Prima opţiune, cea mai utilizată, este echivalentă cu apăsarea butonului Design din ecranul anterior şi atunci când este lansată în execuţie prezintă următorul dialog (Show Table):

3

După selectarea tabelelor sau interogărilor dorite pentru a constitui baza de prelucrare („materia primă”) pentru query-ul curent, se apasă butonul <Add>. Selecţia poate fi individuală, contiguă (cu Shift+clic) sau necontiguă (cu Ctrl+clic), fiind similară cu modalitatea de selecţie din programul Windows Explorer.

În exemplul nostru, s-a dorit o interogare prin care să obţinem o listă cu numele angajaţilor şi studiile acestora. În acest scop, înainte de apăsarea butonului <Add> s-au selectat (necontiguu folosind tasta Ctrl) cele două tabele necesare interogării – Angajaţi şi Studii. Terminarea şi închiderea lucrului cu acest dialog de adăugare se marchează prin apăsarea butonului <Close>.

Următoarea fereastră afişată reprezintă macheta de proiectare vizuală a obiectelor de tip query. Se observă că cele două tabele au fost aduse automat legate prin relaţia care a fost definită şi care există în Relatioships:

4

În partea de jos a acestei machete se găseşte un tabel vid (cu liniile: Field, Table, Sort, Show, Criteria, or), în ale cărui coloane vor trebui trase cu mouse-ul prin drag & drop câmpurile din tabelele (din partea de sus), ale căror valori vor fi afişate sau vor servi drept criterii de interogare. În cazul nostru vor fi trase câmpurile Nume şi Prenume din tabela Angajaţi şi Denumire studii din tabela Studii. În urma acestor acţiuni macheta de proiectare va arăta astfel:

După apăsarea butonului View (primul buton din bara de instrumente – vezi imaginea anterioară), pe ecran va fi afişată următoarea listă cu rezultatele interogării:

5

Se observă că sunt afişate doar cele două câmpuri (coloane) selectate. Relaţia dintre tabelerealizată prin intermediul câmpului cod studii, asigură posibilitatea afişării studiilor corespunzătoare pentru fiecare persoană chiar dacă valorile acestui câmp nu apar explicit.

Oricând se poate reveni în macheta de proiectare prin apăsarea primului buton din bara de instrumente, care acum conţine simbolul unui echer albastru. Rezultatul interogării se poate previzualiza înaintea listării sau se poate lista efectiv la imprimantă prin intermediul butoanelor corespunzătoare din toolbar (sunt butoanele standard care conţin simbolurile imprimantă şi lentilă).

Continuând exemplul, să presupunem că managerul de resurse umane solicită o altă ordonare a listei. El cere ca lista să fie sortată după studii şi anume întâi cele superioare, apoi studiile medii şi fără, iar în cadrul aceluiaşi tip de studii numele persoanelor să apară sortate alfabetic. Deci „în traducere” aceasta înseamnă o triplă sortare. Prima va fi o sortare descrescătoare (descending) după câmpul cod studii (deoarece 2=studii superioare, 1=studii medii, 0=fără studii), a doua, o sortare crescătoare alfabetic (ascending) după câmpul Nume iar la eventuale nume identice (de familie) după Prenume.

Cele trei câmpuri (cod studii şi Nume, resp. Prenume) vor fi chei de sortare în cadrul acestei interogări.

Ordinea de plasare a cheilor în interogare este importantă. Prioritatea cheilor descreşte de la stânga la dreapta (prima cheie, cea mai importantă, cod studii va fi plasată cel mai la stânga în tabelul machetei de proiectare, urmându-i Nume şi apoi Prenume).

Tabelul din macheta de proiectare oferă pentru sortare linia (opţiunea) Sort iar pentru marcajul câmpurilor (coloanelor) vizibile la consultarea interogării, linia Show cu controale de marcare (check box). Câmpurile în dreptul cărora există bifă pe linia Show, vor fi vizibile. Iniţial toate câmpurile au prezentă bifa în dreptul lor pe linia Show (deci în mod implicit vor fi vizibile

6

toate coloanele interogării) . În cazul nostru pentru că nu dorim să fie vizibile valorile câmpului cod studii (0, 1 sau 2) vom debifa respectivul control de marcare.

Macheta de proiectare va fi:

După apăsarea butonului View (primul din stânga pe toolbar) rezultatul interogării va fi următoarea listă dublu sortată:

Să presupunem că se solicită o situaţie mai complexă care pe lângă nume, prenume şi studii să afişeze funcţia deţinută şi data angajării. Lista trebuie să fie sortată în ordinea crescătoare a funcţiilor, apoi în cadrul fiecărei funcţii în ordine alfabetică după numele persoanelor.

În acest caz, deoarece se solicită informaţii despre funcţiile deţinute, este necesară adăugarea în interogare a tabelei Funcţii. Dialogul de adăugare

7

Show Table se apelează prin apăsarea butonului cu acelaşi nume

din bara de instrumente sau alegând opţiunea Show Table la clic dreapta de mouse în zona de sus a ferestrei Query1 .

Acest dialog este:

După adăugarea tabelei Funcţii se trag în tabel prin „drag & drop” câmpurile necesare interogării şi se stabilesc cheile de sortare. Macheta de proiectare va arăta astfel:

La apăsarea butonului View va fi afişată următoarea listă:

8

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 sursă de date (similar unei tabele) pentru un alt query, un form sau un report.

Orice interogare realizată în manieră vizuală sau cu ajutorul vrăjitorilor specializaţi se traduce de către Access într-o comandă SQL (vizibilă la alegerea opţiunii SQL View din meniul obţinut la clic dreapta de mouse în zona de sus a ferestrei Query, meniu prezentat mai jos:

. Iată codul SQL creat automat de către 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]

9

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

Trebuie remarcată cu această ocazie puterea Access-ului ca instrument rapid de dezvoltare vizuală a aplicaţiilor, faptul că dă posibilitatea unui număr mare de persoane care nu sunt programatori de meserie să realizeze prelucrări complexe ale datelor.

Interogările create până acum şi utilizate pentru exemplificări fac parte din interogările de tip Select. Vom prezenta în continuare toate tipurile de interogări puse la dispoziţie de Access.

INTEROGĂRILE DE TIP SELECT

Interogările de tip Select, la fel ca orice alt tip de interogări, presupun existenţa în cadrul machetei de proiectare a:

tabelelor sau interogărilor care constituie sursa de date, relaţiilor dintre acestea moştenite din Relationships tabelului cu:

câmpurile, cheile de sortare, marcatorii de vizibilitate (Show), criteriile (condiţiile) de selecţie.

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

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

Selecţiile pot fi făcute şi pe baza anumitor criterii utile pentru filtrarea datelor. Linia Criteria din tabelul existent în macheta de proiectare este destinată special acestui scop. Scrierea condiţiilor complexe este asistată de un vrăjitor specializat care poate fi apelat apăsând opţiunea Build din meniul contextual al câmpului în care se scrie condiţia, de pe linia Criteria.

Rezultatele interogării de selecţie pot fi prezentate sortate crescător sau descrescător după unul sau mai multe câmpuri. Alegerea câmpurilor respective se face în linia Sort. Dacă de exemplu se doreşte ca ultima listă (creată în subcapitolul anterior) să afişeze doar persoanele cu studii medii, atunci va fi necesară aplicarea unui asemenea filtru de selecţie.

Macheta de proiectare va fi:

10

Se observă existenţa cifrei 1 pe linia Criteria în dreptul câmpului cod studii. Această acţiune simplă determină filtrarea şi deci afişarea doar a înregistrărilor care corespund condiţiei (adică au cod studii=1 ceea ce corespunde studiilor medii). Efectul aplicării filtrului poate fi observat în următoarea figură:

Lucrul cu funcţia like

Pot exista şi filtre multiple. De exemplu, este posibil ca în vederea sărbătoririi Sfântului Ion, să se solicite o listă care să afişeze doar persoanele cu studii superioare (cod studii=2) care au inclus în prenume subşirul Ion. În acest sens se va utiliza o interogare de selecţie având drept criteriu de selecţie pentru nume, funcţia like (însemnând ca şi) cu următorii parametrii: like “*Ion*”.

Caracterul * (se citeşte orice), are aceeaşi semnificaţie ca şi la filtrele pentru fişiere exemplu *.exe, *.*, etc . Deci, criteriul de selecţie pentru nume like “*Ion*” se poate citi: „toate numele persoanelor care au orice şir de caractere înainte de şirul Ion şi orice şir de caractere după şirul Ion”.

Macheta de proiectare pentru această interogare va fi:

11

Rezultatul interogării este:

Dacă se doreşte o listă care să conţină toate numele care încep cu „Ion”, comanda like din linia Criteria este: like “Ion*”. Invers, dacă se doreşte o selecţie pentru toate numele care se termină cu caracterele „ion” atunci comanda like este: like “*ion”.

Iată în continuare efectul ultimei comenzi (like “*ion”).

Dacă se doreşte o interogare a persoanelor cu studii superioare care conţin subşirul Ion fie în nume, fie în prenume, machete de căutare va fi cea din figura de mai jos, care are completată atât linia Criteria, cu o primă condiţie compusă (prenumele să conţină subşirul Ion şi persoana să aibă studii superioare – cod studii=2), cât şi linia Or (sau) cu cea de-a doua condiţie posibilă compusă (adică persoana să conţină în nume subşirul Ion şi să aibă studii superioare).

12

Concatenarea şirurilor de caractere

Sub Access şirurile de caractere se pot concatena. Operatorul de concatenare este “&”. Dacă avem două şiruri: “123” şi “qwe” atunci rezultatul concatenării lor prin intermediul operatorului de concatenare (“123” & “qwe”) este: “123qwe”.

Selecţiile anterioare bazate pe funcţia like puteau fi scrise şi astfel:like “*” & “ion” & “*”like “ion” & “*”like “*” & “ion”

Dialogurile parametrizate

Există situaţii în care selecţia nu se face de fiecare dată după aceleaşi caractere ce sunt conţinute în nume. În acest caz, se lasă operatorului sarcina de a introduce de la tastatură succesiunea de caractere ce sunt căutate. Acest lucru se poate realiza scriind parametrizat criteriul de selecţie în linia Criteria pentru câmpul nume sau prenume. La fiecare lansare în execuţie a interogării se afişează o fereastră de dialog prin intermediul căreia utilizatorul specifică şirul de caractere ce constituie baza de selecţie.

Textul afişat care va apărea în acest dialog ar putea fi:

Introduceţi caracterele incluse in nume:

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

[Introduceţi caracterele incluse in prenume:]

Comanda like exhaustivă care prin intermediul parametrizării va cuprinde toate cazurile de selecţie pentru câmpul prenume este:

Like "*" & [Introduceţi caracterele incluse în prenume:] & "*"

13

Secvenţa corespunzătoare din macheta de proiectare este:

Dialogul care îi va apărea utilizatorului la lansarea interogării este:

Casetă în care poate fi introdus orice subşir de caractere căutat în prenume.

Lucrul cu funcţia between

Dacă se doreşte o listă care să prezinte o selecţie a angajaţilor doar pentru categoriile funcţiilor de şefi atunci va mai trebui introdusă în interogare şi tabela Categorii funcţii. Conţinutul acestei tabele este:

14

Se observă că în această tabelă câmpul cod categorie pentru funcţiile de şefi are valori cuprinse între 2 şi 4. Deci, pentru selecţia categoriilor de funcţii de şefi va fi necesară utilizarea funcţiei Between (între).

Macheta de proiectare pentru interogare este:

Rezultatul interogării va fi:

INTEROGĂRILE DE TIP TOTALS

Alte modalităţi de punere a problemelor de interogare sunt cele referitoare la gruparea datelor din tabele după diferite criterii şi prezentarea unor date statistice despre acestea. Pentru fiecare din grupele ce se formează se pot calcula indicatori cum sunt:

Sum( ) – suma valorilor unui câmp, Count( ) – numărul înregistrărilor din fiecare grupă, Avg( ) – media valorilor unui câmp pentru fiecare grupă , StDev( ) – abaterea standard a valorilor unui câmp pentru fiecare

grupă, Var( ) – dispersia valorilor unui câmp pentru fiecare grupă, Min( ), Max( ) – valoarea minimă / maximă, Last( ) - ultimul, First( ) – prima / ultima valoare din grupă pentru

câmpul respectiv.

15

Să presupunem că avem următorul query de tip Select:

Oricând un query de tip Select poate fi transformat într-un query de tip

Totals prin apăsarea butonului din bara de instrumente: .

După apăsarea butonului , macheta de proiectare va deveni:

Se observă în tabelul din partea de jos a machetei apariţia liniei Total care are înscrisă funcţia Group By în dreptul fiecărui câmp. Dacă dorim o situaţie care să ne prezinte câte persoane din fiecare categorie de funcţii există în

16

instituţie, atunci înseamnă că dorim o grupare a lor (Group By) după categoria de funcţie şi apoi o numărare (contorizare - Count) a acestora.

Pentru rezolvare, în dreptului câmpului cod persoana (deoarece dorim să numărăm persoane), pe linia Total vom face clic cu mouse-ul pe controlul combo box disponibil, ceea ce va determina deschiderea următoarei liste derulante din care vom alege funcţia Count:

În dreptul câmpului cod angajat pe linia Total va apărea în loc de Group By, funcţia Count.

Rezultatul acestui query va fi:

Codul SQL generat automat de Access pentru această 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 altă întrebare la care răspunsul ar fi uşor de dat cu ajutorul interogărilor de tip Totals ar fi: „Câţi angajaţi cu studii medii şi câţi cu studii superioare sunt în instituţie?”.

Macheta de proiectare este:

17

Se observă că pe linia Criteria în dreptul câmpului cod studii s-a înscris condiţia >0. În acest fel se selectează doar persoanele cu studii superioare şi cele cu studii medii (persoanele fără studii au câmpul: cod studii=0).

Răspunsul la întrebare (rezultatul interogării) va fi:

18