Baze de Date si Sisteme Informatice

AMS III CP6 BAZE DE DATE I SISTEME INFORMATICE ADMINISTRATIVE

6.Interogri (Queries)

6.1. Generaliti

Interogarea const n extragerea datelor dintr-o tabel (tabele), dintr-o interogare anterioar sau din ambele, prelucrarea acestora ntr-o form mai mult sau mai puin complex i furnizarea informaiilor ctre utilizatori.

Rezultatele interogrilor pot fi folosite ca atare sau pot constitui surs de nregistrri pentru crearea formularelor i rapoartelor.

Principalele operaii care se pot realiza cu ajutorul interogrilor sunt:

extragerea din tabele numai a cmpurilor relevante pentru utilizatori;

extragerea nregistrrilor din tabele prin specificarea unor criterii de selecie;

regsirea i ordonarea datelor dup anumite criterii;

crearea de cmpuri calculate;

extragerea datelor din una sau mai multe tabele i realizarea unor informaii sintetice;

crearea de tabele, adugarea nregistrrilor n tabele, tergerea nregistrrilor din tabele i actualizarea datelor;

combinarea i compararea ieirilor prin realizarea mai multor interogri n acelai timp;

interogarea i a altor baze de date (FoxPro, SQL Server);

pregtirea datelor n vederea afirii lor n formulare sau rapoarte.

n Microsoft Access se pot crea urmtoarele tipuri de interogri:

interogri de selecie;

interogri parametrice;

interogri ncruciate;

interogri de aciune.

Interogrile de selecie extrag informaii din unul sau mai multe tabele i le afieaz sub form de list. Sunt cel mai uor de creat i au avantajul c pot afia un numr redus de date dintr-un tabel de mare capacitate (datele care ndeplinesc condiiile specificate). Ele permit i modificarea rezultatului afiat, modificare ce va fi vzut i n tabelul surs. De asemenea, permit i folosirea de parametri, cum este reuniunea de cmpuri din tabele ntre care nu exist nici o legtur precum i efectuarea de calcule.Interogrile parAMSetrice nu sunt un tip special de interogri, o funcie parametru putnd fi folosit pentru toate celelalte interogri prezentate mai sus; ele folosesc n mod repetat o interogare, efectund modificri n criteriile de selecie.

Interogrile tip tabel ncruciat centralizeaz n formatul unei foi de calcul tabelar datele din unul sau mai multe tabele. Datele rezultate dup execuia unei astfel de interogri sunt prezentate ntr-un format potrivit pentru analiza datelor i crearea de grafice.

Interogrile de aciune creeaz un nou tabel n baza de date sau realizeaz modificri majore ale unui tabel existent. n general, toate interogrile de aciune pot fi realizate pe baza unei interogri de selecie. Ele permit adugarea, modificarea sau tergerea de nregistrri ntr-un tabel.

Exist patru tipuri de interogri de aciune: interogri de generare a unui nou tabel din datele coninute n setul de rezultate al interogrii;

interogri de adugare a noi nregistrri ntr-un tabel;

interogri de tergere a unor nregistrri dintr-un tabel;

interogri de actualizare a unor nregistrri dintr-un tabel, conform cu o condiie ce trebuie ndeplinit.

Aciunile acestora sunt ireversibile asupra datelor din tabelele surs, iar n cazul ultimelor trei dintre ele, trebuie urmrit pstrarea integritii refereniale atunci cnd prin intermediul lor se acioneaz asupra mai multor tabele legate.

Interogarea datelor din tabele se realizeaz n dou moduri:

n mod grafic prin interfaa Query By Example (QBE) - interogare prin exemplu;

prin limbajul SQL sub form de blocuri de cerere.

Access ofer trei posibiliti pentru definirea interogrii i afiarea rezultatelor acesteia.

Design View fereastr sub forma unei grile de interogare, n care se definete interogarea;

Datasheet View fereastr n care se afieaz rezultatele interogrii;

SQL View fereastr n care Access genereaz automat codul SQL al interogrii QBE; aceeai fereastr este folosit i pentru scrierea direct a unei interogri cu ajutorul instruciunilor SQL.6.2. Interogarea prin selecie6.2.1. Crearea unui obiect de tip interogare

Iniierea crerii cererii se realizeaz n fereastra Database prin activarea fiei Query (Interogri) i apoi apsarea butonului New, sau selectnd din meniul Insert opiunea Query. Access ofer mai multe modaliti de creare a cererilor.

Pentru crearea unei cereri de selecie se alege opiunea Design View din caseta New Query. Fereastra Show Table ofer posibilitatea precizrii sursei de date pentru construirea cererii. Sursa de date pentru o cerere poate fi reprezentat de:

una sau mai multe tabele;

una sau mai multe interogri;

tabele i interogri.

Se vor selecta tabela/tabelele i/sau cererile surs i se va aciona butonul Add pentru a realiza aducerea acestora n fereastra de proiectare a cererii.

Fereastra Select Query este mprit n dou zone:

zona superioar, n care se vizualizeaz tabelele/interogrile surs de date precum i relaiile definite ntre acestea;

grila Query Design structurat astfel:

linia Field: rezervat pentru selectarea unui cmp;

linia Table: destinat precizrii sursei de date;

linia Sort: permite precizarea sensului sortrii pentru atributul din coloana respectiv;

linia Show: permite inhibarea afirii realizrilor cmpului respectiv;

linia Criteria: ofer posibilitatea precizrii criteriilor de selecie prin introducerea expresiilor

Access corespunztoare;

liniile or: permite precizarea mai multor criterii de selecie n cazul expresiilor Access

utiliznd operatorul OR.

Definirea interogrii de selecie presupune parcurgerea urmtorilor pai:

1. Precizarea cmpurilor ale cror realizri urmeaz s le returneze cererea. Numele acestor

cmpuri se vor preciza n grila Query Design n rndul Field.

n mod implicit, antetul coloanelor tabelului rezultat n urma interogrii este reprezentat de numele cmpului, cu excepia cazului n care la crearea tabelei ai precizat o alt etichet prin intermediul proprietii Caption. Dac dorii afiarea n tabelul rezultat n urma interogrii a unei noi etichete pentru un cmp plasai mouse-ul n linia Field naintea numelui cmpului, tastai eticheta dorit urmat de caracterul :.

Observaie: Dup aducerea cmpurilor din tabele sau interogri pe grila de interogare sunt

posibile operaii de inserare a unui cmp, tergerea unui cmp, deplasarea unui cmp ntr-o alt

poziie, modificarea dimensiunilor unei coloane i modificarea numelui unui cmp.

2. Se precizeaz criteriul de selecie (n mod implicit se returneaz realizrile tuturor tuplurilor

pentru cmpurile specificate) prin introducerea unei expresii Access valide n rndul Criteria (eventual i rndul OR). Introducerea expresiei Access se face prin tastare sau se construiete prin intermediul generatorului de expresii (Expression Builder) a crui fereastr se deschide selectnd opiunea Build a meniului pe care l activai printr-un click dreapta de mouse n rndul Field.3. Se precizeaz, dac este necesar, cmpul/cmpurile dup care se dorete o eventual sortare i sensul acesteia n linia Sort.6.2.2. Utilizarea operatorilor

Pentru a construi expresii pe rndul Criteria se utilizeaz operatorii:

aritmetici: adunare (+); scdere (), nmulire (*), mprire (/), ridicare la putere (^), mprirea a dou numere cu returnarea unui ntreg (\), mprirea a dou numere cu returnarea restului mpririi (MOD).

de comparaie: , =, =. Aceti operatori returneaz valorile logice True i False. Excepie reprezint cazul n care unul dintre operatori are valoarea NULL i deci orice comparare va returna valoarea NULL.

asociai operatorilor de comparare:

IS NULL, IS NOT NULL o valoare NULL (cmp necompletat) nu este nici TRUE nici FALSE. nregistrrile care au valoarea NULL n cmpurile selectate nu apar ca rezultate ale interogrii;

LIKE se folosete mpreun cu caracterele de nlocuire * i ? pentru a stabili dac o valoare ncepe cu unul sau mai multe caractere; caracterul * poate nlocui orice numr de caractere; caracterul ? nlocuiete numai un caracter;

IN stabilete dac o valoare este cuprins ntr-o list;

BETWEEN stabilete dac o valoare aparine unui interval specificat.

logici:

NOT negaia;

AND pentru conjuncia a dou valori;

OR pentru disjuncia a dou valori;

XOR pentru disjuncia exclusiv a dou valori;

Eqv verific echivalena a dou valori.

de concatenare a irurilor de caractere: + i &.

de identificare: ! i . . Aceste dou caractere sunt utilizate ca separatori, astfel:

Combin numele coleciilor de obiecte i numele obiectelor pentru a selecta un anumit obiect

sau proprietate a lui: Forms! [Clieni]

Identific atribute aparinnd unei tabele: Clieni! [Localitate]

Deosebete numele obiectelor de cele ale proprietilor: TextBox1.FontSize=12 unde: TextBox reprezint un obiect de control iar FontSize reprezint o proprietate (stabilete dimensiunea fontului)

constante: constantele utilizate n construirea expresiilor Access pot fi de natur

numeric (ex: 1200,5,0);

text (123, Toma Ion, str. Viilor 15);

dat calendaristic (ex: #12.31.01# ceea ce indic data de 31 decembrie 2001).

Identificatorii: sunt nume de obiecte Access (tabele, atribute, formulare, etc.)

Funciile: pot fi de natur:

Dat calendaristic : Date(), Month(), Year (), etc.

Exemple: Date() returneaz data curent; Month(Date()) returneaz numrul lunii calendaristice curente. Year(Date()) returneaz anul curent.

De tip text: Len() returneaz lungimea unui ir; Trim() elimin spaiile de la nceputul i de la sfritul unui ir; Left() returneaz primele n caractere de la nceputul unui ir, etc.

Matematice i trigonometrice: ABS() returneaz valoarea absolut a unui numr; INT() returneaz partea ntreag dintr-o valoare numeric, ROUND() rotunjete o valoare cu un anumit numr de zecimale; SUM() calculeaz suma; AVG() calculeaz media, etc.

Financiare: PV() returneaz valoarea actual a unei anuiti pltite n rate periodice egale; SLN() returneaz valoarea AMSortizrii unui mijloc fix dup o anumit perioad (AMSortizare liniar) etc.

Funcii diverse: ISNUMERIC(), ISNULL(), etc.

Reguli de formare a expresiilor introduse pe cmpul Criteria:

datele de tip Text se tasteaz ca atare, iar Access adaug automat ghilimele;

pentru datele de tip Number i Currency se tasteaz cifrele i eventual simbolul zecimal, fr simbolul monetar sau separatorul de mii;

referirile la numele de cmpuri trebuie incluse ntre paranteze drepte, altfel se adaug automat ghilimele, considerndu-se text;

formatul internaional de dat calendaristic este mm/dd/yy. Access adaug automat delimitatorul # ;

Access adaug automat IS la referirile care implic valoarea NULL.

Pe rndul Criteria din grila de interogare se poate introduce un singur criteriu de selecie sub un cmp sau mai multe criterii sub cmpuri diferite. Dac criteriile de selecie se introduc pe un singur rnd Criteria, se extrag nregistrrile care ndeplinesc toate condiiile (operatorul logic AND), iar dac se introduc pe rnduri diferite se includ n rspuns doar nregistrrile care ndeplinesc oricare dintre criteriile menionate (operatorul logic OR).

APLICAII

Se consider baza de date STUD. S se creeze tabela DATE cu structura urmtoare:

NRMAT - text(3) (numr matricol ex. 100, 101,102.)

NP text(30) (nume i prenume ex. Pop Liviu)

DN - dat calendaristic (data naterii ex. 03/04/81)

SCT - text(2) - (sectia ex. AMS si DAdm)

AN numeric, ntreg (anul de studiu ex. 1, 2 ,3 sau 4)

BRS da/nu (bursier ex. DA, NU)

SEX text(1) (sexul f, m)

TEL text(15) (telefon ex. 094123456)

ADR text(25) (adresa ex. str. Gorunului nr. 5)

OR text(15) (oraul ex. Craiova,Severin, .)

JUD - text(2) (judeul ex. Dolj-DJ, Mehedinti - MH)

S se introduc 10 nregistrri n tabela DATE astfel nct s se respecte urmtoarele cerine:

a) S existe studeni al cror domiciliu s nu fie Severin.

b) S existe studeni de la ambele secii AMS i DAdm.

c) S existe studeni cu vrste mai mici de 20.

d) S existe studeni n diferii ani de studiu.

e) S se defineasc regulile de validare pentru cmpul AN astfel nct s se poat introduce n tabel doar valorile 1, 2, 3 sau 4.

f) S se defineasc regulile de validare pentru cmpul SCT astfel nct s fie permise doar valorile AMS sau DAdm. Pentru cmpul SEX valorile permise vor fi F sau M.

g) S se defineasc urmtoarele interogri pentru tabela DATE. Interogrile se vor salva cu numele: inter1, inter2,, inter n. OBSERVAIE ! Se va utiliza n exclusivitate modul de lucru Design View.

S se creeze:

1. o list cu numele studenilor care au domiciliul n Severin, ordonai descresctor dup numrul matricol.

2. o list cu numele studenilor care au domiciliul n Severin, ordonai cresctor dup numrul matricol.

3. o list cu numele studenilor din Severin, ordonai cresctor dup vrst.

4. o list cu numele studenilor bursieri din Severin.

5. o list cu numele studenilor a cror vrst este mai mare de 20 de ani i nu sunt bursieri.

6. o list cu numele studentelor bursiere care au domiciliul n Severin.

7. o list cu numele studenilor de sex masculin care au vrsta de 21 de ani i nu sunt bursieri.

8. o list cu numele studenilor nscui dup data de 03/06/80.

9. o list cu numele studenilor nebursieri.

10. o list cu numele studenilor bursieri.

11. o list cu numele i localitatea studenilor care nu domiciliaz n Severin, ordonai dup cmpul secie.

12. lista studentelor de la secia DAdm.

13. lista studenilor de la DAdm i AMS.

14. o list cu numele studenilor de la DAdm care nu sunt bursieri.

15. o list cu numele studenilor de la ambele secii care sunt bursieri i nu domiciliaz n Severin.

16. o list cu numele studentelor de la DAdm ordonate descresctor dup vrst.

17. o list cu numele studenilor de la DAsm ordonai cresctor dup vrst.

18. lista studenilor care nu au domiciliul n judeul Cluj i nu sunt bursieri, ordonai alfabetic dup nume.

19. o interogare care s afieze numele studentului care are numrul de telefon 0722123456.

20. o list cu numele studenilor care au domiciliul n Severin i sunt bursieri.

21. lista studentelor fr burs din anul 2 de la secia DAdm.

22. lista studenilor bursieri, de sex masculin, din anul 2, de la sectia DAdm, care domiciliaz n Severin.

23. lista studenilor de la ambele secii care nu sunt n anul 2.

24. lista cu numele studenilor bursieri de la secia DAdm din anul 2 care au numrul matricol mai mare dect 102.

25. lista studenilor ordonat alfabetic dup nume i prenume.

26. lista tuturor studenilor seciei AMS, ordonai alfabetic dup nume i prenume.

27. lista studentelor seciei DAdm care nu sunt bursiere i nu sunt din Severin.

PAGE 5