Laborator Access II.doc

Academia Forţelor Terestre octombrie 2007CURS – Gestiunea informatizată a bazelor de date

LABORATOR II

1. Crearea interogărilor de selecţie

Pentru lucrul cu interogări avem următoarele două posibilităţi :1) Prin intermediul modului de lucru Design View. În secţiunea Objects se va alege opţiunea Queries şi apoi Create query in Design view. Pe ecran va apare fereastra Show Table în care utilizatorul va alege tabelele cu care va lucra. Pentru a adăuga un tabel se selectează tabelul dorit din listă după care se execută clic pe butonul Add. Fereastra Show Table se închide prin activarea butonului Close.Se execută apoi următorii paşi:- se aleg câmpurile necesare interogării;- se specifică condiţiile de selecţie;- se precizează criteriile de filtrare impuse de cerinţa interogării;- se stabileşte modul de grupare;- se stabileşte modul de ordonare.

Lector univ. dr. ing. Luminiţa Giurgiu - 1 -


2) Cu ajutorul wizard-ului de interogări. Access generează codul SQL pentru fiecare interogare, fie că este realizată cu Design View sau cu wizard-ul. Codul SQL poate fi văzut prin selectarea opţiunii SQL View din meniul View. Se poate vizualiza astfel clauza select – from – where corespunzătoare interogării.De asemenea este posibilă scrierea unei interogări într-o astfel de fereastră, acest lucru presupunând cunoaşterea temeinică a sintaxei SQL. O frază SQL se termină cu punct şi virgulă.

2. Criterii de selecţieCriteriile de selecţie se introduc în celula aflată la intersecţia coloanei câmpului (asupra valorilor căruia se va impune criteriul) cu linia Criteria din grila de interogare.Aceste criterii pot fi simple sau compuse (cu ajutorul operatorilor AND/OR) şi pot de asemenea utiliza o serie de cuvinte rezervate şi expresii definite de utilizatori.Principalele criterii simple sunt :

- apartenenţa la un interval de valori : BETWEEN valoare_inferioară AND valoare_superioară;

- apartenenţa la o listă de valori : IN (valoare 1, valoare2, …., valoare_n);

- utilizarea operatorilor de comparaţii : <, >, <=, >=, <>, = ;

- utilizarea operatorilor de negaţie : NOT valoare;

- selecţia înregistrărilor care conţin sau nu valori :NOT NULL, IS NOT NULL sau NULL, IS NULL.

Se pot specifica însă şi criterii de selecţie după un anumit text care, dacă va conţine spaţii, trebuie pus în ghilimele. În interiorul textului se pot folosi caractere generice "?" (orice în poziţia încare apare) şi "*" (orice în poziţia în care apare şi în următoarele).



2. Exemple şi exerciţii2.1 Având la dispoziţie baza de date Clienţi cu tabelele Infoclienţi şi Plăţi se cere obţinerea situaţiei plăţilor tuturor clienţilor dintr-un anumit oraş, de exemplu oraşul Sibiu. Se va proceda astfel:- se deschide în model Design o nouă interogare şi se adaugă în partea superioara a ferestrei interogării cele două tabele ale bazei Infoclienţi şi Plăţi:

- din lista câmpurilor celor două tabele se vor alege câmpurile care vor fi figura în grila de interogare:



- în urma rulării interogării se va obţine un set de rezultate cu numele. prenumele tuturor clienţilor din Sibiu împreună cu situaţia plăţilor:data plăţii, suma plătită, data scadentă.

2.2 Lucru independentI. Având la dispoziţie baza de date Curs cu tabelele Cursanţi, Profesori, Studii şi Teme se cere proiectarea unor interogări de selecţie pentru obţinerea următoarelor situaţii:

a. situaţia cu toţi cursanţii absolvenţi ai A.F.T. din anul 2001 care au fost îndrumaţi de profesorul Giurgiu

Indicaţie 1 (fereastra Relationships):

Indicaţie 2 Atunci când sunt prezente mai multe criterii pe aceeaşi coloană în linii diferite, între ele se aplică SAU logic. Dacă se introduce un crieriu în acelaşi rând cu unul introdus anterior vor fi selectate pentru afişare numai acele înregistrări care respectă ambele criterii (între ele se aplică ŞI logic).



Indicaţie 3 (grila de proiectare):

b. situaţia cu toate titlurile temelor îndrumate-corectate de către profesorul Giurgiu împreună cu numele cursanţilor care le-au realizat.

c. situaţia cu studiile, temele şi profesorii îndrumători ai unui anumit cursant.

2.3 Exemple de criteriiUrmătorul tabel afişează unele exemple de criterii şi explică efectul pe care îl au asupra unei înregistrări.

Criteriu Efect

> 234 Returnează toate numerele mai mari ca 234. Pentru a găsi toate numerele mai mici de 234, utilizaţi < 234.

>= "Callahan"Returnează toate înregistrările de la Callahan până la sfârşitul alfabetului.

Between #2/2/2007# And #12/1/2007#

Returnează datele de la 2-Feb-07 până la 1-Dec-07 (ANSI-89). Dacă baza de date utilizează metacaractere ANSI-92, utilizaţi apostrofuri (') în loc de caracterele diez. Exemplu: Between '2/2/2007' And '12/1/2007'.

Not "Germania" Găseşte toate înregistrările unde conţinutul exact al



câmpului nu sunt exact egale cu "Germania". Criteriul va returna doar înregistrările ce conţin caractere în plus faţă de "Germania," cum ar fi "Germania (euro)" sau "Europa (Germania)".

Not "T*"

Găseşte toate înregistrările cu excepţia celor care încep cu T. Dacă baza de date utilizează setul de metacaractere ANSI-92, utilizaţi caracterul procent (%) în locul caracterului asterisc (*).

Not "*t"

Găseşte toate înregistrările care nu se termină în t. Dacă baza de date utilizează setul de metacaractere ANSI-92, utilizaţi caracterul procent în locul caracterului asterisc.

In(Canada,UK)Într-o listă, găseşte toate înregistrările care conţin Canada sau UK.

Like "[A-D]*"

Într-un câmp text, găseşte toate înregistrările care încep cu literele de la A la D. Dacă baza de date utilizează setul de metacaractere ANSI-92, utilizaţi caracterul procent în locul caracterului asterisc.

Like "*ar*"

Găseşte toate înregistrările care includ secvenţa de litere "ar". Dacă baza de date utilizează setul de metacaractere ANSI-92, utilizaţi caracterul procent în locul caracterului asterisc.

Like "Maison Dewe?"

Găseşte toate înregistrările care încep cu "Maison" şi conţin de asemenea un al doilea şir de 5 litere din care primele 4 sunt "Dewe" şi ultimul caracter este necunoscut (indicat printr-un semn de întrebare). Dacă baza de date utilizează setul de metacaractere ANSI-92, utilizaşi caracterul underscore (_) în locul semnului de întrebare.

#02.02.07#

Găseşte toate înregistrările pentru 2 Februarie, 2007. Dacă baza de date utilizează setul de metacaractere ANSI-92, încadraţi data cu caractere apostrof în loc de caractere diez (#). Exemplu: '2/2/2007'.

< Date() - 30 Returnează toate datele mai vechi de 30 de zile. Date() Returnează toate înregistrările ce conţin data de azi. Between Date() And Returnează toate înregistrările dintre data de azi şi



DateAdd("M", 3, Date())

data de peste trei luni începând de azi.

Is Null Returnează toate înregistrările ce conţin o valoare null (necompletată sau nedefinită).

Is Not Null Returnează toate înregistrările ce conţin o valoare.

""

Returnează toate înregistrările ce conţin un şir de lungime zero. Se utilizează şirurile cu lungime zero când este necesară adăugarea unei valori la un câmp necesar, dar nu se cunoaşte încă acea valoare. De exemplu, un câmp poate conţine un număr de fax, dar este posibil ca unii clienţi să nu deţină aparate fax. În acest caz, se introduce o pereche de ghilimele fără spaţiu între ele în locul unui număr.

3. Crearea interogărilor parametrice (dinamice)Criteriul de selectare a înregistrărilor este determinat în momentul executării interogării, Access deschizând o casetă de dialog în care trebuie furnizată valoarea parametrului.Se va descrie în continuare modul de crearea a unei interogări parametrice folosind baza de date Clienţi cu tabelele Infoclienţi şi Plăţi.

- Se va cere spre exemplificare obţinerea situaţiei plăţilor tuturor clienţilor dintr-un anumit oraş, de această dată valoarea câmpului oraş fiind chiar parametrul.- La fiecare execuţie a interogării se va deschide caseta de dialog care va cere furnizarea numelui oraşului, astfel că de fiecare dată se va putea tasta un alt oraş, iar setul de rezultate (înregistrări obţinute în urma rulării interogării) va fi de fiecare dată altul, din acest motiv interogarea numindu-se dinamică.



- Se va deschide o nouă interogare în modul Design şi se va proiecta conform modelului dat mai jos:

- La rularea interogării se va obţine căsuţa de dialog unde se tastează numele oraşului:

- De remarcat modul în care este specificat parametrul în grila de proiectare a interogării: în câmpul localitatea al tabelului infoclienţi, rubrica Criteria conţine criteriul de selecţie care este în acest caz parametrul dat între paranteze drepte; în interiorul parantezelor drepte se va da textul care apare ca mesaj în casuţa de dialog, pentru ca utilizatorul să ştie ce anume trebuie să tasteze în acea căsuţă.

3.1 Lucru independent



- Folosind baza de date Clienţi cu tabelele Infoclienţi şi Plăţi , creaţi o interogare parametrică pentru a obţine situaţia plăţilor unui anumit client, al cărui nume să constituie valoarea parametrului şi să poată fi dat aproximativ (se va folosi clauza Like);- Folosind baza de date Curs cu tabelele Cursanţi, Profesori, Studii şi Teme se cere proiectarea unei interogări parametrice pentru obţinerea situaţiei tuturor cursanţilor - cu studii, teme şi profesorii îndrumători - proveniţi de la o anumită instituţie care să constituie valoarea parametrului.

4. Crearea interogărilor de ştergere4.1 Ştergerea înregistrărilor dintr-un tabel din partea „one” a relaţiei one-to-manyFolosind baza de date Clienţi cu tabelele Infoclienţi şi Plăţi se va exemplifica modul de crearea a unei interogări de ştergere care va şterge din tabelul Clienţi (aflat în partea one a relaţiei) toţi clienţii din oraşul Sibiu.

Fiind în modul Design (proiectare) al interogării, se va stabili tipul acesteia prin alegerea opţiunii Delete Query (fie din Query Type de pe bara de instrumente, fie din meniul Query);

Se va trage (drag) asterisk-ul (*) din lista câmpurilor tabelului Clienţi în grila de proiectare a interogării;

În celula Delete va apărea clauza From; Pentru a specifica criteriul după care se vor şterge înregistrările, se

trage în grilă câmpul localitatea asupra valorilor căruia se vor impune criteriul;

În celula Delete va apărea clauza Where; În celula Criteria a câmpului localitatea se va specifica criteriul

“Sibiu”; Pentru previzualizarea înregistrărilor care vor fi şterse se va

acţiona View de pe bara de instrumente; Se va acţiona Run de pe bara de instrumente pentru ştergerea propriu

zisă. Observaţie: se va verifica faptul că toate înregistrările din tabelul copil Plăţi corespunzătoare înregistrărilor din tabelul părinte Infoclienţi vor fi şterse automat în urma ştergerii înregistrărilor din tabelul părinte.



4.2 Ştergrea înregistrărilor folosind o interogare care include ambele tabele dintr-o relaţie one-to-many

Se va crea o nouă interogare care va conţine tabelele Infoclienţi şi Plăţi aflate în relaţie one-to-many;

Se va stabili din nou tipul interogării ca fiind Delete Query; Din tabelul cu înregistrări relaţionate din partea “many” a relaţiei

adică tabelul Plăţi se va trage asterisk-ul (*) din lista câmpurilor în grila de proiectare a interogării (acest lucru însemnând de fapt toate câmpurile);

În celula Delete va apărea clauza From; Pentru a specifica criteriul după care se vor şterge înregistrările, se va

trage în grilă câmpul nume din tabelul părinte, asupra valorilor căruia se va impune criteriul “toţi clienţii al căror nume începe cu A”;

În celula Delete va apărea clauza Where; În celula Criteria a câmpului nume se va specifica criteriul Like A*

(vezi figura de mai jos); Pentru previzualizarea înregistrărilor care vor fi şterse se va

acţiona View de pe bara de instrumente;



- Se va acţiona Run de pe bara de instrumente pentru ştergerea înergistrărilor din tabelul din partea “many” a relaţiei;

- Se va selecta lista cu cîmpuri pentru tabelul existent în partea “many” a relaţiei şi se va acţiona Delete pentru a-l înlătura din interogare (ca în figura alăturată);- Doar cu tabelul primar rămas în interogare şi cu câmpul asupra căruia se impun crietrii se va acţiona Run din nou pentru ştergerea înregistrărilor din tabelul aflat în partea „one” a relaţiei.



5. Crearea interogărilor generatoare de tabele (make-table)Se va exemplifica crearea unei interogări generatoare de tabel care va crea un nou tabel cu toţi clienţii care au avut ca suma de plată o anumită valoare introdusă ca parametru. 1. Se deschide interogarea în Design View2. Se alege Make-Table Query din meniu sau cu Query Type de pe bara cu instrumente.3. În caseta Table Name se introduce numele pentru tabela creată de interogare.

Tabela poate fi adăugată bazei de date curente sau într-o altă bază de date .4. După salvarea interogării, numele acesteia din fereastra Database va fi precedat de un semn de exclamare.5. Se poziţionează cursorul în coloana câmpului suma_platita care se consideră parametru, în primul rând de criterii şi se scrie mesajul care solicită criteriul încadrat de paranteze pătrate.



6. În urma rulării apare căsuţa de dialog care solicită introducerea parametrului.

7. În urma completării valorii solicitate va apărea mesajul:

8. Fereastra bazei de date va conţine la obiectul Table încă un tabel nou creat (cu două înregistrări), al cărui nume tabel_cu_clienti/suma, s-a stabilit la crearea interogării:



9. Fereastra bazei de date va conţine la obiectul Query, şi interogarea generatoare de tabel numită creare_tabel:



5. Crearea interogărilor de adăugare înregistrări (append)Se va exemplifica modul de creare a unei interogări de adăugare înregistrări, care va adăuga înregistrări în tabelul tabel_cu_clienti/suma din baza de date Clienti.

- Se selectează interogarea de acţiune în modul Design View, se stabileşte tipul interogării Append Query şi se specifică tabelul în care se vor introduce înregistrări;

- Se completează corespunzător grila de proiectare a interogării:

- Se rulează interogarea:



- Fereastra bazei de date va arăta astfel pentru obiectul Queries:

6. Crearea inerogărilor de actualizareSe va exemplifica crearea unei interogări de actualizare care să actualizeze câmpul cod_oras din tabelul Infoclienţi şi câmpul data_scadentă al tabelului Plăţi al bazei de date Clienţi astfel: toate înregistrările care au valoarea Sibiu în câmpul localitate vor fi actualizate prin modificarea valorii din câmpul cod_oras într-o nouă valoare specificată în celula (rândul) Update To, iar toate înregistrările relaţionate corespunzătoare din tabelul Plati vor fi actualizate prin modificarea valorii din câmpul data_scadenta într-o nouă valoare specificată în celula Update To.

- Se selectează tabelul Infoclienti în modul Design al noii interogări- Se converteşte interogarea în interogare de actualizare- Se selectează câmpul destinat pentru actualizare, respectiv

cod_oras şi câmpul localitate unde se va specifica criteriul, în grila de proiectare a interogării.

- În rîndul Update To al câmpului cod_oras se va completa expresia sau valoarea dorită pentru a schimba-actualiza câmpul respectiv.

- În rîndul Update To al câmpului data_scadenta se va completa expresia sau valoarea dorită pentru a schimba-actualiza câmpul respectiv.

- În rîndul Criteria al câmpului localitate se va completa criteriul după care se va face actualizarea.



În fereastra bazei de date vor exista acum următoarele interogări:


Laborator Access II.doc

Documents

Transcript of Laborator Access II.doc