INTEROGARI

1. SCOPUL LUCRARII :

Studiul si intelegerea modului de lucru cu interogarile si aplicatii in limbajul SQL .

PREZENTARE TEORETICA

INTRODUCERE

In lucrarile anterioare am pregatit terenul pe care ne vom desfasura in cele ce urmeaza. Adica am creat tabele si relatii intre acestea .

Interogarile isi au esenta tocmai in tabele, fara de care nu ar avea nici un rost.

EXEMPLE DE LIMBAJE DE BAZE DE DATE

Orice sistem SGBD modern ofera un limbaj de interogare a BD. Acest

limbaj este de obicei de tip SQL, dar nu este o regula general-valabila. Varianta 'neplacuta', adica cea in care nu se dispune de un limbaj de interogare, este aceea de a concepe singuri accesul la date, folosindu-ne de un limbaj cu suport pentru BD si de cunostintele noastre de programare ( care in aceasta situatie ar fi oarecum minime, deoarece limbajul ales de noi ar trebui luat de la capat) .

Limbajele de interogare sunt clasificate in doua categorii :

-A) Limbaje algebrice - aici o interogare este exprimata prin intermediul unor operatori aplicati asupra relatiilor. SQL este un limbaj de acest tip ;

-B) Limbaje bazate pe calcul relational - o interogare este exprimata prin intermediul unor conditii ( sau predicate) pe care t-uplele ( adica randurile) tabelelor ( numite relatii in jargonul relational) trebuie sa le satisfaca. Aceasta categorie contine doua sub-categorii, functie de obiectul asupra caruia se aplica operatia :

-A.1) limbaje orientate pe t-uple , in care predicatele se aplica asupra tuplelor ( adica randurilor) tabelelor( exemplu de astfel de limbaj este QUEL);

-A.2) limbaje orientate pe domenii , in care predicatele se aplica domeniului din care ia valori atributul ( coloana) tabelului ( exemplu : QBE, dezvoltat de IBM) .

OBSERVATIE : Access 95 foloseste o grila de interogare bazata pe QBE

( Query By Example) . In fundal, totul este transformat in SQL !

NOTIUNEA DE INTEROGARE IN ACCESS

De departe scopul crearii si intretinerii unei BD consta in a obtine

informatii diverse. Aceste informatii sunt furnizate in urma unui proces de extragere a datelor, bazat pe interogari .

In esenta o interogare este o definitie a datelor pe care le doriti : ce campuri, din ce tabele, conform caror criterii, in ce ordine de sortare .

O interogare inseamna de fapt o intrebare. Aceasta intrebare este pusa unui tabel ( sau mai multor tabele)

Raspunsul oferit de SGBD( sistemul de gestiune a bazei de date) cu care lucram consta intr-o multime de campuri afisate sub forma de inregistrari ( adica randuri ) din tabelele interogate. Ordinea de afisare a lor depinde de baza de date respectiva si de ordinea pe care am dorit-o noi, in grila de interogare.

Facilitati

Tot cu ajutorul interogarilor mai puteti :

sterge linii sau campuri din diferite tabele ;

aduce la zi liniile sau campurile dorite din anumite tabele ;

insera informatii in linii sau campuri ;

crea si sterge tabele ;

crea si sterge baze de date ! ;

modifica securitatea datelor .

TIPURI DE INTEROGARI

2.3.1 TIPUL SELECT ( 'Select Query' )

Tipul de interogare folosit in aceasta lucrare despre SQL este SELECT.

Observatie : Forma ei generala este:

SELECT numeCamp

FROM numeTabel

[WHERE ( Conditie) ;]

Daca citim aceasta instructiune SQL obtinem, in limbaj natural :

" Selecteaza campul 'numeCamp' din tabelul 'numeTabel' care indeplineste conditia 'Conditie' " .

Clauza WHERE a fost inclusa intre paranteze drepte, deoarece este optionala.

Insa aceasta clauza ne va fi de foarte mare folos atunci cand vom proiecta o interogare Access. In lipsa ei, interogarea anterioara devine, in limbaj natural :

" Selecteaza campul 'numeCamp' din tabelul 'numeTabel' " ;

se vor afisa efectiv valorile cu care am completat acest camp din tabelul 'numeTabel'.

2.3.2 TIPUL TOTAL ( 'Total Query' )

2.3.2.1 UTILIZAREA INTEROGARILOR DE TIP TOTAL

Oriunde avem nevoie de sumari si comparari ale campurilor unei inregistrari dintr un tabel, vom folosi un asemenea tip de interogare. O astfel de interogare lucreaza pe grupuri, folosind functii de grup ( numite si functii agregat).

Exemple :

Iata cateva exemple de intrebari, puse pe baza tiparului unei interogari Total

"Care sunt rezultatele fiecarui jucator din lot, pe parcursul acestui campionat?"

( Apare cuvantul fiecare . Ne gandim la o sinteza

sau :

" Ce vanzari a avut firma in ultimul trimestru, comparativ cu acelasi trimestru al anului trecut ?" ( Aici este vorba de sumare si comparare, in vederea unei sinteze a activitatii firmei) .

sau:

" Cate lucrari s-au facut de la inceputul anului ( pana in prezent)

( Aici este vorba de o sumare).

- Codul SQL al interogarilor de tip 'Total' ( 'Total queries') este :

SELECT numeCampuri

FROM numeTabele

[WHERE .]

[GROUP BY.]

[HAVING.]

[ORDER BY.];

Toate clauzele ( sau cuvintele-cheie) incadrate intre paranteze drepte semnifica faptul ca sunt optionale.

2.3.3 TIPUL TABEL INCRUCISAT ( 'Crosstab Query' )

Instructiunea SQL folosita de ACCESS pentru o astfel de interogare este nestandard.

Ce este o interogare tabel-incrucisat ?

In cazul in care intr-o interogare Total alegem mai multe campuri cu optiunea 'Group By', rezultatul este crearea unor grupuri in grupuri.

Acelasi lucru in cazul unei interogari Crosstab este altfel interpretat: unul dintre campurile 'Group By' este preluat obligat drept cap de coloana

( 'column heading') si valorile acelui camp apar in raspunsul la interogare drept titluri de coloana. In afara de capul de coloana exista unul sau mai multe capete de rand, iar la intersectia randurilor cu coloanele se executa o operatie de grup asupra unui tert camp, care face parte si el din tabelul ( sau tabelele) care furnizeaza toate valorile acestor campuri ( pentru capete si pentru calcul). Vom vedea exact despre ceea ce este vorba.

Reguli ale interogarii tabel incrucisat :

O interogare de tip tabel incrucisat are nevoie OBLIGAT de urmatoarele trei campuri :

un camp ofera valorile sale ( pe care le tine acest camp in tabelul din care il preiau) drept capete de coloana ( 'column heading'). Nu pot exista mai multe capete de coloana ! ;

un camp furnizeaza valorile sale ( pe care le tine acest camp in tabelul din care il preiau) drept capete de rand ( 'row heading'). Pot exista mai multe capete de rand, avand semnificatia de grup in grup, ca la interogarile Total ;

un camp asupra caruia se aplica una din functiile de grup. Nu pot exista mai multe campuri de acest tip ! Acesta apare la intersectia intre capetele de rand si capetele de coloana.

Este preferabil ca acel camp care furnizeaza valorile de coloana sa contina un numar limitat de valori. De ce ? Ganditi-va la un tabel ale carui coloane sunt intr-un numar foarte mare. Raspunsul intrebarii intr-un asemenea caz este dificil de citit si greoi de interpretat. De aceea este bine sa aducem drept capete de coloana acele campuri care au o multime redusa de valori, a.i. tabelul sa aiba o latime acceptabila. In schimb, in ceea ce priveste capetele de rand, aici putem aduce acele campuri ce au multimi numeroase de valori, deoarece interpretarea unui tabel in aceasta structura ( adica multe randuri) este mai naturala si deci mai usoara.

Exemple de intrebari :

Prezentam cateva intrebari create pe baza tiparului interogarii tabel incrucisat :

"Ce rezultate a obtinut fiecare dintre studenti la examenele din sesiunea aceasta ?"

( Aici campul 'NumeStudent' isi ofera valorile drept capete de rand, campul 'numeExamen' pe cele ale coloanei, iar functia de grup este 'Min' asupra unui camp de note de exemplu, pentru a vedea care student nu a reusit sa ia un anume examen. Operatia apare la intersectia randurilor cu coloanele tabelului-raspuns al intrebarii) .

sau :

" Care jucator de biliard a obtinut cel mai bun scor intr-un numar de partide

disputate ?"

( Aici campul de nume al jucatorului ofera valorile drept capete de rand, campul de nume al partidei ofera capetele de coloana, iar campul valoare

- de la intersectia fiecarui rand cu fiecare coloana - este cel al scorurilor, asupra caruia se face operatia de grup Max) .

sau:

" Ce incasari au avut cele doua firme de calculatoare rivale, in fiecare dintre ultimele x luni ?"

( Aici valorile pentru capetele de rand sunt furnizate de numele firmelor, cele de coloana sunt date de numele lunilor, iar la intersectia fiecaruia dintre randuri cu fiecare dintre coloane operatia este, sa spunem, o medie asupra campului de incasari - care este un camp numeric) .

- Forma SQL nestandard a unei interogari tabel incrucisat este :

TRANSFORM functieDeGrup

SELECT_obisnuit

PIVOT capColoana

[IN ( listaValori)] ;

Descrierea codului SQL :

In instructiunea TRANSFORM apare functia de grup pe care am stabilit-o in grila QBE pe randul Total, pentru campul ( dealtfel unic) ce are proprietatea 'Value' in randul 'Crosstab'.

Urmeaza o instructiune SELECT obisnuita, in care se cer acele campuri care in grila QBE apar drept capete de coloana.

Dupa cuvantul-cheie PIVOT este dat numele campului care pe grila QBE are proprietatea 'Column Heading' in randul 'Crosstab' .

OBSERVATIE : In cazul in care folositi o interogare tip tabel-incrucisat impreuna cu un raport, numarul de coloane poate varia functie de valorile campului ce furnizeaza capetele de coloana. Aceste valori pot varia de la o interogare la alta, si de aceea Access permite stabilirea unei liste de valori de folosit pentru titlurile de coloana chiar daca nu exista valori pentru acele noi coloane. Aceasta lista se poate da usor folosind fereastra 'Properties' pentru o interogare ( clic-dreapta in jumatatea de sus a grilei QBE, apoi Properties). Aici optiunile pe care le dorim sunt date pe randul 'ColumnHeadings'. Fiecare noua valoare trebuie incadrata intre ghilimele si separata de cea anterioara prin virgula.

DESFASURAREA LUCRARII

Fiecare subgrup de studenti de la calculatoarele 1, 2, 3 si 4, folosind baza de date creata in lucrarile 1, 2 si 3 vor realiza trei interogari de fiecare tip : SELECT, TOTAL si TABEL INCRUCISAT, bazandu-se pe exemplul prezentat in lucrare.

CREAREA UNEI NOI INTEROGARI

3.1.1 PASII UNEI NOI INTEROGARI

Procesul de creare al unei noi interogari, ca si cel de creare al unui tabel sau al unei baze de date, se realizeaza intr-o succesiune de pasi.

Pasul 1 :

Din fereastra prezentata in figura 1 se selecteaza eticheta 'Queries' .

Apoi se apasa butonul 'New', dealtfel singurul activ in acest moment.

Pasul este identic cu cel de la crearea unui nou tabel. Ceea ce urmeaza este deasemenea asemanator ( ca mod de lucru, evident) cu ceea ce s-a prezentat la crearea unui tabel. Astfel, exista cu posibilitatea de a alege intre:

ajutorul unui 'Query Wizard'( program oferit de mediul Access 95 pentru asistarea crearii unei interogari) ;

libertatea user-ului de a-si crea propria interogare .

Fig.1 - Fereastra de pornire pentru crearea unei noi interogari .

Dupa apasarea butonului 'New', apare fereastra din figura 2, unde puteti alege, asa cum am spus, intre mai multe moduri de a crea o interogare.

Fig. 2 - Modurile oferite de Access 95 la crearea unei noi interogari.

Observati aceste posibilitati :

- 'Design View' - este cea pe care o folosim in acest moment ( si pe care dealtfel o vom folosi-o cel mai des); aceasta ne da posibilitatea de a ne particulariza ( sau personaliza ) interogarea nou creata ;

- 'Simple Query Wizard' - modul 'vrajitor' de creare a unei interogari simple, oferit de Access 95 ;

- 'Crosstab Query Wizard' - acesta este un mod special de interogare, in care intrebarile sunt puse pe tabele incrucisate ;

Pasul 2 :

Dupa ce am ales unul dintre modurile de creare a unei interogari, mediul Access ne aduce in fereastra din figura 3.

Fig. 3 - Fereastra de start in proiectarea unei noi interogari . Pe fundal, grila QBE

Aici sunt prezente de fapt doua ferestre:

cea vizibila - 'Show Table'- din care ne alegem tabelul( sau tabelele) pe care ne vom construi interogarea. Alegerea se face :

fie prin selectarea a cate unui tabel si apasarea butonului 'Add';

fie prin dublu-clic stanga pe numele tabelului dorit .

Adaugarea ulterioara a unui alt tabel pe grila QBE se poate face printr-un clic stanga pe iconita 'Show Table' din toolbar :

Observatie : Aceasta iconita este prezenta in toolbar doar pe durata cat se lucreaza cu grila QBE( adica modul Design al unei interogari). Daca doriti o prezenta permanenta trebuie sa faceti un meniu 'custom', folosind : View -> Toolbars -> Customize, unde selectati meniurile dorite . Tot aici puteti stabili si alte optiuni .

cea de fundal - 'Select Query' ( care devine activa dupa inchiderea celei anterioare) - este de fapt locul unde ne 'construim' o interogare.

Aceasta fereastra poarta numele de fereastra ( sau grila) QBE ( Query By Example, adica Interogare prin Exemplu). Este prezentata in figura 4 .

Pasul 3 :

In grila QBE, odata tabelele alese, trebuie sa alegem si campurile

dorite in intrebarea noastra. Observati ca grila are doua portiuni :

cea din partea de sus, unde sunt prezente tabelele;

si cea din partea de jos, unde apare un tabel, cu randuri si coloane .

Pe randuri apar cateva denumiri ( fig. 4), si anume :

'Field' - aici trebuie completat cu acel camp( sau acele campuri) pe care il dorim selectat dintr-unul sau mai multe dintre tabelele prezente in partea de sus a ferestrei QBE.

Fig. 4 - Grila QBE in detaliu. Observati cele doua parti ale acesteia :

partea ce contine tabelele ( jumatatea de sus) ;

partea in care ne alegem campurile si tablele ce vor face parte din interogare si unde stabilim optiunile unei interogari ( jumatatea de jos) .

Completarea randului Field'

Aducerea campului dorit in randul 'Field' se poate face :

prin executarea unui dublu-clic stanga pe numele lui, din tabelul dorit aflat in jumatatea de sus a grilei QBE;

prin drag-and-drop al acelui camp dinspre tabel spre locul dorit ;

prin selectarea lui din fereastra derulanta ce apare atunci cand se executa clic pe randul 'Field', pe pozitia unde dorim sa aducem campul

( v. fig. 5) .

Astfel se procedeaza de fiecare data cand avem nevoie de selectarea campurilor de interes pentru orice interogare. In cazul mai multor tabele necesare unei interogari, randul 'Field' se completeaza in acelasi mod.

Fig. 5 - Unul din modurile de selectie a campului dorit in interogare .

Daca se doreste afisarea tuturor campurilor unui tabel ca raspuns al unei interogari se poate folosi caracterul '*' ( asterisc) in locul unde de obicei aveam un singur camp ( fig. 6) .

Fig. 6 - Folosirea caracterului asterisc in cazul in care se doreste selectarea tuturor campurilor unui tabel ca raspuns al unei interogari .

Trebuie sa fiti atenti la folosirea caracterului '*', deoarece acesta poate duce la efecte ascunse. De exemplu, daca s-a folosit intr-o interogare un tabel, iar pe randul 'Field' din grila QBE apare caracterul '*', atunci toate campurile acelui tabel sunt folosite de acea interogare . Aceasta intrebare va rula mai incet decat o alta mai bine gandita, care nu foloseste '*' drept selectie a campurilor.

Observatie : Criteriile de sortare si selectie nu sunt aplicabile in cazul caracterelor '*' .

Daca intr-o interogare nu mai aveti nevoie de un tabel, atunci puteti renunta la acesta facand clic-stanga pe el, apoi apasand tasta Del . Sau clic-dreapta, apoi 'Remove table'.

LUCRUL ASUPRA TABELELOR IN GRILA QBE

In caz ca interogarea se face pe mai multe tabele( legate intre ele prin relatii), in jumatatea de sus a grilei QBE va sunt aratate tabelele, precum si relatiile

( reprezentate prin linii) stabilite de dvs. pana in momentul inceperii proiectarii unei interogari. Puteti sa editati atributele oricarei asocieri existente intre tabele prin pozitionarea cu mouse-ul pe una dintre liniile vizibile, si executarea unui clic-dreapta. In ferestra care apare ( fig. 7) alegeti 'Join Properties'.

Fig. 7 - Modificarea tipului asocierii, pe grila QBE .

Apare apoi fereastra 'Join Properties' din figura 8.

O alta varianta ( cu acelasi rezultat) este executarea unui dublu-clic stanga pe linia ce semnifica relatia intre tabele. Va fi afisata fereastra 'Join Properties', ca in figura 8 .

Fig. 8 - A doua varianta de modificare a atributelor asocierilor.

DESCRIEREA RANDURILOR GRILEI QBE

3.1.3.1 TIPUL SELECT

Pe grila QBE apar urmatoarele randuri :

randul 'Field' - aici completati cu campurile pe care le doriti afisate ca raspunsul intrebarii, ca si campurile care vor da conditia interogarii ;

randul 'Tables' - bineinteles, aici apare numele tabelului de care apartine campul pe care l-ati introdus in randul de deasupra . Este posibil ca acest camp sa nu apara. Il puteti afisa astfel : clic-dreapta oriunde in jumatatea de jos a grilei, dupa care, in fereastra care apare alegeti 'Table names' ( v. fig. 9) ;

Fig. 9 - In caz ca randul Tables nu apare, il puteti afisa dand un clic-dreapta in jumatatea de jos a grilei QBE si alegand optiunea 'Table names' .

randul 'Sort' - acest rand va ofera posibilitatea de a aranja in mod ascendent sau descendent rezultatele pentru un anume camp, ca raspuns la o intrebare. Odata pozitionati pe acest rand, putem face un clic-stanga pe sageata in jos si de acolo alegem una dintre optiunile prezentate ( fig. 9) : 'Ascending', daca dorim o afisare a rezultatelor in ordine alfabetica de la A la Z, si 'Descending' pentru o sortare incepand cu litera Z ( invers alfabetic);

Fig. 9 - Optiunile de sortare .

randul 'Show' - observati ca acest rand contine o validare : daca marcati casuta ( prin clic-stanga) atunci in raspunsul intrebarii va fi afisat si acel camp, altfel el nu este aratat in raspuns dar poate fi folosit in criteriile de selectie .

Observatie : O interogare trebuie sa aiba cel putin un camp cu atributul 'Show' pe care sa-l intoarca in raspuns .

O sortare pe mai multe campuri inseamna o sortare secventiala de la stanga la dreapta . Ce inseamna aceasta ? De exemplu, aveti un tabel Carti in care tineti campurile de Domeniu, Nume, Editura, An_de_Aparitie.

Daca doriti sortarea tuturor acestor campuri ( presupunem 'Ascending'), atunci Access va afiseaza un raspuns in care cartile apar in ordine alfabetica( dupa Domeniu). Apoi, in cadrul unui domeniu : campurile Nume si Editura sunt afisate de asemenea alfabetic, iar campul An_de_Aparitie este si el afisat incepand cu anul cel mai putin recent( si invers in caz de sortare 'Descending') .

randul 'Criteria' - aici sunt impuse conditiile de selectie ale interogarii, adica acei parametri dupa care SGBD incearca sa obtina raspunsul, folosind baza de date pe care lucrati. Este posibil sa nu obtineti nici un raspuns, caz in care :

fie nu exista unul, adica baza de date a dvs. pana in acel moment nu contine combinatia ceruta ;

fie exista o eroare in completarea bazei de date .

Trecerea din modul 'Design View' al unei interogari in modul 'SQL View' se face oricand folosindu-ne de meniul View, de unde alegem cand una dintre optiuni, cand cealalta ( fig. 10) .

Fig. 10 - Meniul View permite trecerea din modul 'Design' in mod 'SQL View' pentru o interogare .

In cazul in care dorim mai multe criterii de selectie( combinatii de criterii simple) atunci avem optiunile :

Operatorul 'OR' ( SAU logic) : pentru aceasta introducem in randul 'Criteria' mai multe conditii, pe care le legam cu 'Or'; sau introducem fiecare conditie pe cate un rand Criteria separat( unul sub altul) ( fig. 11).

Fig. 11 - Stabilirea unei combinatii SAU logic intre doua conditii : aici se cer numele departamentelor care au operatori cu prenumele Radu 'SAU' Ioan .

Operatorul 'AND' ( SI logic) : conditiile se scriu in acelasi rand 'Criteria' separate prin 'And' ; sau repetam intreaga coloana a campului prin Copy-Paste, iar randul 'Criteria' este completat cu o noua conditie ( fig. 12) .

Fig. 12 - Realizarea unei combinatii SI logic intre doua conditii : aici se cere

numele departamentelor care au operatori cu numele Radu 'SI' Ioan .

Operatorul 'NOT'( NU logic) : conditia se scrie in randul 'Criteria'; tipul de data Yes/No este reprezentat intern de catre Access drept : Yes = -1, No = 0 .

Fig. 13 - Realizarea unei conditii logice folosind operatorul NOT : se cer numele calculatoarelor care au capacitate multimedia.

Operatorii Access de comparare pentru stabilirea criteriilor de selectie

Observatie : Access interpreteaza automat datele care apar in criterii !

Astfel :

daca includeti intr-un criteriu o conditie ce contine '*', Access va adauga operatorul 'Like' ;

operatorul 'Is' este adaugat automat la referirile ce implica valoarea 'Null' ;

semnul '#' este adaugat automat datelor calendaristice .

( ex.: #10/3/89# inseamna data de 3 Octombrie 1989 in formatul american de data, sau 10 Aprilie 1989 in formatul nostru. Deci atentie si aici !) ;

de o parte si de alta a unui text sunt adaugate ghilimele duble ;

daca va referiti la nume de campuri, atunci acele nume trebuie inchise intre paranteze drepte .

Exemplu

Sa configuram grila QBE pentru a raspunde la intrebarea :

" Care sunt calculatoarele care au un hard-disk mai mare de o anumita valoare ( 400 MB) ?"

Avem nevoie aici de un singur tabel : tblCalculator'. Pasii pe care trebuie sa-i urmam sunt :

Creati o noua interogare ( conform pasului 1 din descrierea teoretica ) ;

Alegeti modul Design ;

Pe grila QBE selectati tabelul 'tblCalculator' ;

Selectati in randul Field campul cerut in interogare: numele calculatorului ;

Intr-o noua coloana selectati tabelul 'tblCalculator' si in randul Field alegeti campul pentru care impunem conditia din interogare.

In randul Criteria pe coloana a doua se pune conditia : > 400.

Obtinem ferestra din figura 13.a :

Fig. 13.a - Grila QBE pentru interogarea din exemplu.

Raspunsul intrebarii se obtine prin apasarea iconitei din bara cu instrumente.

Ceea ce se obtine este prezentat in figura 13.b .

Fig. 13.b - Raspunsul la interogarea din exemplu.

Codul SQL al interogarii este cel din figura 13.c :

Fig. 13.c - Codul SQL al interogarii din exemplu

TIPUL TOTAL

Pe langa randurile utilizate la tipul SELECT, tipul TOTAL mai foloseste si randul 'Total'.

cu eticheta 'Queries' activa in ferestra unei BD, alegem 'New . Selectam modul 'Design View si ajungem in grila QBE. In momentul de fata suntem intr-o interogare Select' obisnuita. Din bara cu instrumente apasam pe iconita avand semnul Σ ( 'sigma') :

Fig. 14 - Iconita 'Totals'. Cu ajutorul ei spunem ca o interogare este de tipul 'Total' .

Observam in grila QBE aparitia unui nou rand : randul 'Totals' ( fig. 15). Acesta individualizeaza o interogare Total.

Fig.15 - Grila QBE este pregatita pentru o interogare tip Total. Randul 'Total' apare in urma selectarii sale prin iconita Totals din bara de instrumente.

In momentul de fata putem sa incepem constructia interogarii.

Odata adus pe grila randul Total, ne folosim de puterea acestuia pentru a rezolva noile probleme pe care ni le punem.

Exista o serie de optiuni de grup si functii agregat pe care Access ni le pune la dispozitie. Le vom descrie pe fiecare dintre acestea .

a) b)

Fig. 16 - Optiunile posibile oferite de Access pentru completarea randului 'Total'.

Randul 'Total' se completeaza la un moment dat cu una din urmatoarele valori posibile ( fig. 16.a si 16.b) :

optiunea Group By determina gruparea tuturor inregistrarilor tabelului dupa campul din randul 'Field' pentru care avem Group By ;

optiunea Sum : aceasta determina Access sa realizeze sumarea

inregistrarilor pentru fiecare din campurile pentru care s-a specificat 'Group By' . Access - dupa ce ruleaza interogarea - adauga prefixul 'SumOf' la denumirea campului pentru care s-a specificat aceasta optiune. Daca doriti un alt nume, atunci :

fie introduceti numele dorit, urmat de doua puncte, in randul 'Field' ( in fata numelui campului care exista acolo) ( vezi fig. 17);

fie alegeti 'SQL View' si folositi cuvantul-cheie 'AS' in fata numelui pentru care executati functia de grup 'Sum' ( vezi fig. 18);

Fig. 17 - Redenumirea unui camp pe grila QBE : observati ca 'NumeDorit' este noua denumire pentru

campul 'ProfesorID'. Acest nou nume trebuie sa fie urmat de doua puncte. Totul se srie pe randul Field

al grilei QBE.

Fig. 18 - Redenumirea unui camp in cod SQL( partea selectata). Se foloseste urmatoarea sintaxa:

NumeCamp AS NumeNou, adica se scrie mai intai numele vechi al campului ( aici

profesori.CodFacult), urmeaza cuvantul-cheie AS, iar in final noua denumire ( aici

'NumeOarecare').

optiunea Count : intoarce numarul de inregistrari dintr-o grupare . De

exemplu dorim sa stim numarul de carti imprumutate de fiecare cititor. Atunci alegem campul 'cititorID' si selectam in randul 'Total' optiunea 'Group By', dupa care alegem campul 'carteID' si selectam pentru el optiunea 'Count'. Access va numara pentru fiecare dintre cititori numarul de inregistrari in care acel cititor apare cu o carte imprumutata.

Access adauga prefixul 'CountOf' inaintea numelui campului pentru care se face aceasta operatie.

Observatie : Optiunea 'Count' poate fi folosita si pentru cautarea duplicatelor. Daca doriti acest lucru, atunci puteti folosi un camp identificator( de tip cheie primara) de doua ori : odata avand optiunea 'Group By', si a doua oara avand optiunea 'Count'. Punand conditia ca rezultatul numararii sa fie >1, obtinem acele inregistrari care apar de cel putin doua ori intr-un tabel. Cazul este exclus in Access, deoarece aici exista cheia primara, deci fiecare inregistrare este unica ! Insa exista baze de date care nu se ingrijesc de cheia primara asa cum face Access, astfel ca atunci cand importati un asemenea tabel puteti intampina aceasta problema.

optiunea Avg ( average) : aceasta este o functie statistica ce intoarce valoarea medie a tuturor campurilor pentru care se aplica( evident, tinand cont de campul cu optiunea 'Group By' !) . Inregistrarile care contin o valoare nula( 'Null') nu sunt luate in considerare in calculul mediei

( sumare cu 0 si impartire la numarul total de campuri din care se scade numarul campurilor necompletate). Exemplu: media campurilor cu valori 5,7,0 este ( 5+7+0)/(3) = 4, dar media campurilor de valori: Null, Null, 3, 4 este : ( 3+4)/(2)=3,5 . Deci campurile 'Null' nu se considera;

optiunea StDev sau Var : functii statistice ce semnifica raspandirea datelor in raport cu valoarea medie in intevalul pe care se lucreaza( mai apropiate sau mai departate de valoarea medie) ;

optiunile Min, Max, First, Last : se intorc, respectiv, valorile : cea mai mica, cea mai mare, prima conform ordinii de sortare curente, ultima conform ordinii de sortare curente. Toate aceste functii ignora campurile 'Null' !.

'Max'( 'Min') se pot aplica nu numai campurilor de tip numeric, ci si asupra celor de tip text sau data calendaristica( care intern este un numar in virgula mobila). Asupra unui text are ca rezultat sirul care este cel mai apropiat de inceputul alfabetului ( respectiv de sfarsitul lui). Asupra datei rezultatul este data cea mai recenta( respectiv cea mai indepartata) relativ la momentul actual. Optiunile 'First'( 'Last') sunt afectate de ordinea de sortare, astfel ca, de exemplu 'First' asupra unei date calendaristice ne returneaza prima data functie de eventuala ordine de sortare !

optiunea Expression : este folosita in cazul unui camp calculat care intoace un singur rezultat pentru grupare.

optiunea Where : apare in cazul in care acel camp pentru care este aleasa nu contine o functie de grup. Odata aleasa aceasta optiune, caseta de validare din randul 'Show' al grilei QBE se deselecteaza automat. Deci nu pot sa am in acelasi timp si optiunea 'Where' valabila, si caseta 'Show' activata ( regula Access) .

Observatie : ( Regula SQL)

Limbajul SQL INTERZICE folosirea clauzei 'WHERE' pentru campurile pentru care se executa o functie agregat !

Reguli Access :

Interogarile total nu accepta asteriscul ('*') decat in campurile calculate!

Interogarile Total pot fi create si pe doua tabele aflate in relatia 1-la-n. In acest caz ORICE camp din tabelul de parte "unu" a relatiei este automat trecut pe grila QBE cu optiunea 'Group By' !

Access are o regula de aplicare a criteriilor :

pentru campurile pentru care s-a specificat una dintre optiunile 'Where' sau 'Group By', criteriile eventuale de pe grila QBE sunt APLICATE INAINTE de grupare.

pentru celelalte optiuni criteriile sunt aplicate dupa grupare .

Exemplu :

Sa construim grila QBE a intrebarii urmatoare :

"Cate pachete software ruleaza pe calculatorul cu ID=2 din departamentul cu ID=1?"

Avem nevoie de doua tabele aflate intr-o legatura n-la-n : 'tblCalculator' si 'tblSoftware', precum si de tabelul 'tblDepartament'. Pentru relatia n-la-n se foloseste tabelul de legatura 'tblSWCalc' .

1. Gruparea ( optiunea Group By) se face dupa campurile 'calculatorID' din 'tblSWCalc' si 'departamentID' din 'tblDepartament' ;

2. Operatia de grup care trebuie efectuata este o numaratoare ( deci vom folosi functia de grup 'Count') asupra campului 'swID' din tabelul 'tblSWCalc' ;

In randul 'Criteria' punem doua conditii ( legate intre ele prin operatorul AND) :

Pentru campul 'calculatorID' impunem valoarea 2, iar pentru campul 'departamentID' impunem valoarea 1. Putem folosi si parametri pentru aceste conditii, care sunt ceruti de Access in momentul rularii .

Grila QBE care raspunde intrebarii puse arata ca in figura 19.a. Raspunsul la intrebare este prezentat in figura 19.b, iar codul SQL este prezentat in figura 19.c.

Fig. 19.a - Grila QBE asociata exemplului .

Fig. 19.b - Raspunsul la interogarea din exemplu.

Fig. 19.c - Codul SQL al grilei QBE din figura 19.a. Se observa ca in cazul in care o interogare are parametri acestia sunt declarati la inceput folosind cuvantul-cheie PARAMETERS. Se dau numele si tipul parametrilor.

3.1.3.3 TIPUL TABEL INCRUCISAT ( Crosstab Query)

Codul SQL al unei interogari tabel incrucisat

Instructiunea SQL folosita de ACCESS pentru o astfel de interogare este nestandard.

- Forma ei este :

TRANSFORM functieDeGrup

SELECT_obisnuit

PIVOT capColoana

[IN ( listaValori)] ;

Descrierea codului SQL :

In instructiunea TRANSFORM apare functia de grup pe care am stabilit-o in grila QBE pe randul Total, pentru campul ( dealtfel unic) ce are proprietatea 'Value' in randul 'Crosstab'.

Urmeaza o instructiune SELECT obisnuita, in care se cer acele campuri care in grila QBE apar drept capet de coloana.

Dupa cuvantul-cheie PIVOT este dat numele campului care pe grila QBE are proprietatea 'Column Heading' in randul 'Crosstab' .

OBS. : In cazul in care folositi o interogare tip tabel-incrucisat impreuna cu un raport, numarul de coloane poate varia functie de valorile campului ce furnizeaza capetele de coloana. Aceste valori pot varia de la o interogare la alta, si de aceea Access permite stabilirea unei liste de valori de folosit pentru titlurile de coloana chiar daca nu exista valori pentru acele noi coloane. Aceasta lista se poate da usor folosind fereastra 'Properties' pentru o interogare ( clic-dreapta in jumatatea de sus a grilei QBE, apoi Properties). Aici optiunile pe care le dorim sunt date in randul 'ColumnHeadings'. Fiecare noua valoare trebuie incadrata intre ghilimele si separata de cea anterioara prin virgula.

CONSTRUCTIA UNEI INTEROGARI TIP TABEL-INCRUCISAT

Exista aici doua modalitati de creare a unei interogari Crosstab :

varianta cu wizard ;

varianta neautomatizata, plecand de pe grila QBE.

VARIANTA CU WIZARD

- Pasul 1 :

Din fereastra unei BD se face clic-stanga pe eticheta 'Queries'. Apoi se incepe crearea interogarii prin apasarea butonului 'New' . Din fereastra prezentata in figura 20 se alege 'Crosstab Wizard' .

Fig. 20 - Cu ajutorul optiunii 'Crosstab Query Wizard' puteti sa intelegeti modul de lucru al unei astfel de interogari.

- Pasul 2 - Lucrul cu programul Wizard

In continuare sunt prezentate si descrise etapele prin care trece programul wizard ( figurile 21-a, 21-b, 21-c, 21-d, 21-d.1 si 21-e).

Observatie : Este bine ca prima oara ( dar nu numai) cand se lucreaza cu o interogare tip tabel-incrucisat, sa se inceapa cu programul wizard, deoarece astfel se poate observa modul de constructie al acesteia si felul in care va fi

afisat raspunsul. De aici se poate reflecta mai usor la semnificatia datelor cuprinse in raspuns.

Fig. 21-a - Prima etapa din cadrul programului wizard pentru interogari Crosstab: alegerea tabelului care va furniza campurile cap de rand si cap de coloana.

Fig. 21-b - A doua etapa : alegerea campului ale carui valori dau capetele de rand. Se selecteaza campul din lista valabila de campuri si se apasa butonul '>' .

Fig. 21-c - A treia etapa : alegerea campului ale carui valori dau capetele de coloana. Acest camp este bine sa aiba un numar limitat de valori.

Fig. 21-d - Etapa a patra : stabilirea campului asupra caruia se executa operatia de grup, precum si a functiei de grup dorite. De exemplu, in aceasta figura se executa operatia de grup 'Count' asupra campului 'Adresa' .

Fig. 21-d.1 - Optional se poate alege pentru o sumare a randurilor, adica o noua

coloana in care apare suma valorilor pe fiecare rand .

Fig. 21-e - Final : fereastra de final a wizard-ului.

In figura 21-e, dupa ce se alege numele sub care va fi cunoscuta aceasta interogare

( si care se poate modifica ulterior), se poate opta intre :

a vedea raspunsul la interogare( optiunea 'View the query') ;

si a intra in modul Design al interogarii tocmai construite( optiunea 'Modify the design').

Se poate alege si un help dinamic asupra lucrului cu interogarile de tip tabel incrucisat ( optiunea din partea de jos, 'Display Help on working with the Crosstab Query').

VARIANTA FARA WIZARD

Aceasta varianta pleaca de pe grila QBE. Asadar trebuie mai intai ajuns pe grila.

- Pasul 1 - Grila QBE:

Din fereastra unei BD, selectand eticheta 'Queries' si apasnad butonul 'New' ajungem in fereastra din figura 22. Aici alegem modul 'Design View' si confirmam cu OK. Urmeaza sa alegem din fereastra 'Show Table' ( figura 23) -care se lanseaza automat- care tabel sau interogare o dorim pentru a ne furniza campurile necesare.

Fig. 22 - Alegere modului 'Design View' pentru o interogare ne duce pe grila QBE .

Fig. 23 - Fereastra 'Show Table'.

Fereastra 'Show Table' apare automat dupa selectarea modului 'Design View'.De aici ne alegem tabelele sau tabelele si inteogarile pe care le dorim pentru a ne furniza campurile necesare interogarii tip tabel-incrucisat.

Pasul 2 : -Lucrul efectiv

Odata tabelul ales facem un clic-dreapta in jumatatea de sus a grilei QBE si alegem 'Query Type'( Tipul de interogare dorit) . Figura 24 arata acest lucru.

Din optiunile posibile de tip de interogare( si de aici rezulta ca aceasta metoda de creare a unei interogari Crosstab merge la toate tipurile de interogari care fac parte din aceste optiuni) alegem 'Crosstab Query'. Pe grila QBE va aparea randul 'Crosstab' , in jumatatea de jos ( figura 25).

Fig. 24 - Startul unei interogari Crosstab pornind de pe grila QBE. Se face clic-dreapta in jumatatea de sus a grilei si apoi din meniul care apare alegem 'Query Type'->'Crosstab Query' .

Dupa ce am ales tipul 'Crosstab Query' ajungem in grila QBE, pe care a aparut randul 'Crosstab'. La fel se intampla in cazul interogarilor Total, dar acolo apare un rand 'Total'.

Observatie : Randul 'Total' exista acum implicit in cazul interogarii Crosstab. Aceasta deoarece o interogare Crosstab face apel la functiile de grup, accesibile doar prin intermediul randului Total.

Optiunile posibile pe care la aduce randul Crosstab sunt :

'Row Heading' : aceasta apare in dreptul acelor campuri pe care la dorim drept capete de rand. Dupa cum am aratat putem opta pentru mai multe capete de rand.

Obs. : Pentru aceasta optiune, in randul Total putem avea 'Group By' si orice functie de grup, dar nu 'Where' !

'Column Heading' : ne spune ca acel camp pentru care am ales-o este un cap de coloana. Putem avea un singur cap de coloana intr-o interogare Crosstab.

Obs. : Pentru aceasta optiune putem avea in randul 'Total' doar optiunea 'Group By'.

'Not Shown' : este echivalentul casutei 'Show' dintr-o interogare Select.

Alegerea ( nealegerea) ei spune ca acel camp pentru care aceasta optiune este activata ( dezactivata) va fi afisat ( nu va fi afisat) ca raspuns al interogarii.

Obs. : In cazul in care specificam in randul Total optiunea 'Where', atunci OBLIGAT in randul Crosstab punem 'Not Shown'. Este acelasi lucru cu cazul unei interogari Total in care optiunea 'Where' in randul 'Total' deselecta automat caseta de validare 'Show' !

Exemplu :

Vom construi grila QBE pentru urmatoarea interogare tabel incrucisat :

Cate pachete software ruleaza fiecare calculator din fiecare departament ?"

Avem nevoie de tabelele : 'tblDepartament', tblCalculator' si 'tblSWCalc'. Acesta este setul minimal de tabele care ne este necesar pentru a putea gasi raspunsul dorit.

Stabilim drept cap de rand ( in randul Crosstab se alege 'Row Heading') campul 'departament' din 'tblDepartament'. In randul Total punem 'Group By' ;

Cap de coloana ( in randul Crosstab se alege 'Column Heading') alegem campul 'calculatorID' din 'tblSWCalc'. In randul Total punem 'Group By' ;

Functia de grup impusa este 'Count' si se executa pentru campul 'swID' din 'tblSWCalc'. Pe randul Crosstab pentru acest camp alegem Value.

Grila QBE este prezentata in figura 25.a, raspunsul in figura 25.b si codul SQL in figura 25.c .

Fig. 25.a - Grila QBE pentru interogarea din exemplu.

Fig.25.b - Raspunsul la interogare.

Fig. 25.c - Codul SQL al interogarii.

3.1.4. PROPRIETATILE INTEROGARILOR

Atat interogarea propriu-zisa, cat si campurile unei interogari pot avea anumite proprietati, asa cum si campurile unui tabel aveau un set de atribute.

La acestea se ajunge prin intermediul iconitei 'Properties' din toolbar

( fig. 26-a), sau din meniul 'View' ( fig. 26-b) .

a) b)

Fig. 26 - Cele doua posibilitati de a stabili proprietatile unei interogari :

a) Iconita 'Properties' din toolbar ;

b) Meniul View, optiunea 'Properties' .

Campurile tabelelor pastreaza proprietatile stabilite in cadrul ferestrei 'Design' a unui tabel ( vezi). Odata stabilite acolo, foarte rar este nevoie sa le modificati. Totusi, daca doriti vreo schimbare, Access va ofera urmatoarele ( fig. 27) :

un format ( 'Format');

o masca de intrare ( 'Input mask') ;

un titlu ('Caption') ;

o descriere a barei de stare ( 'Description') ;

a)                                                  b)

Fig. 27 - Fereastra proprietatilor de camp, apelata dintr-o interogare :

-a) Eticheta 'General'; -b) Eticheta 'Lookup' ;

La proprietatile unei interogari se poate ajunge printr-un clic-dreapta oriunde in jumatatea de sus a grilei QBE. Ce apare este aratat in figura 28. Alegeti apoi optiunea 'Properties' si ajungeti in fereastra din figura 29.

Fig. 28 - Primul pas catre proprietatile unei interogari . Meniul ce apare dupa clic-dreapta in partea superioara a grilei QBE .

Fig.29 - Fereastra de proprietati ale unei interogari .

Aici vedeti toate proprietatile unei interogari, care vor fi descrise pe scurt.

'Description' ( Descriere) - pentru informatii despre formularele, rapoartele sau casetele de combinate care folosesc aceasta interogare;

'Output all fields' ( Adauga toate campurile tabelelor grilei QBE) - daca este 'Yes' atunci Access introduce caracterul asterisc '*' in instructiunea SQL, astfel ca toate campurile din toate tabelele sunt incluse in raspunsul unei interogari ;

'TopValues' ( Valorile de sus) - este pe valoare adefault 'All'; daca se specifica insa o valoare intreaga, atunci se va afisa doar numarul specificat de inregistrari conforme cu conditiile pe care le-ati pus in proiectarea interogarii. Se poate da si o valoare procentuala ( ex. 15%), caz in care se vor afisa doar 15 la suta dintre inregistrari( in caz ca ele ar fi cu totul mai multe). Daca se doreste afisarea valorilor din partea de jos a listei, atunci se modifica criteriul de sortare la 'Descending' .

'Unique values' si 'Unique records'( Valori unice si inregistrari unice) - valorile implicite sunt :

-> 'Unique values' = No ( echivalent cu SELECT DISTINCT) ;

-> 'Unique records' = Yes (echivalent cu SELECT DISTINCTROW).

Daca ambele sunt pe 'No', atunci se vor afisa toate randurile dintr-un tabel ( deci se ignora unicitatea) . Daca apar randuri duplicat in raspunsul interogarii si se doreste eliminarea lor, atunci se stabileste optiunea 'Unique values' la 'Yes'.

Observatie : Duplicatele din raspunsul unei interogari nu sunt neaparat duplicate si in tabelele care au furnizat campurile !

Optiunea 'Unique records' elimina doar inregistrarile care sunt duplicate din tabelele de baza ( asa ceva nu ar trebui sa existe, daca tabelele au fost corect completate) .

'Run permissions' ( Permisiuni de rulare) - in caz ca o BD a fost securizata

( parolata) atunci exista doua astfel de permisiuni :

Owner's ( ale proprietarului) - Access completeaza la sfarsit instructiunea SQL cu WITH OWNERACCESS OPTION ;

User's ( ale celui ce foloseste BD). Aceasta este si valoarea implicita. Ea se poate modifica prin : Tools -> Options -> Tables/Queries .

- Optiunile 'SourceDatabase'( baza de date sursa), 'ConnectString'( Sir de conectare) si 'ODBCTimeout'( durata de timp permisa pentru ODBC) se refera la tabelele atasate unei BD .

Pe langa toate aceste proprietati, Access stocheaza si informatii despre modul in care o interogare da raspunsul : fonturile, stilurile de grila, latimile coloanelor si inaltimea randurilor, coloanele care trebuie ascunse ( optiunea 'Show' inactiva).

INTREBARI

Care este limbajul din care provine SQL ?

Interogarea tip SELECT se poate referi la grupuri de obiecte ?

Pe ce linie din grila QBE si cand se utilizeaza instructiunea 'Group By' ?

Pe ce linie din grila QBE si cand se utilizeaza operatorul de grup 'Count' ?

Pe ce linie din grila QBE si cand se utilizeaza operatorul de grup 'Avg' ?

Se pot utiliza intr-o interogare tip tabel incrucisat mai multe capete de coloana ?

Se pot utiliza intr-o interogare tip tabel incrucisat mai multe capete de rand ?

Pe ce rand din grila QBE si in ce tip de interogare se foloseste optiunea 'Value' ?

Poate fi aceasta optiune inlocuita cu o alta ?

Instructiuea 'Where' poate fi folosita in asociere cu functiile de grup ? Daca nu, 

care este instructiunea care ii tine locul ?