Interogarile sunt folosite pentru a extrage date din baza de date. Rezultatul unei interogari este intotdeauna sub forma unei alte tabele; de exemplu, o interogare care gasește lista bonurilor emise azi este o tabela care conține detalii despre bonuri.
O intergoare se poate realiza fie:
Folosind interfața grafica -- Query Design;
Folosing limbajul SQL -- Structured Query Language (despre care vom discuta mai tarziu)
Cele doua metode sunt echivalente; de fapt, metoda grafica este o extensie a metodei SQL.
Sa analizam urmatoarea interogare:
Obțineți lista cu numele și prețurile produselor care au prețul mai mic de 10 RON.
Pentru a realiza interogarea, trebuie sa parcurgem urmatorii pași:
Sa determinam ce coloane trebuie afișate și din ce tabele provin aceste coloane;
Sa determinam condițiile pe care trebuie sa le indeplineasca respectivele coloane.
Atenție: pașii de mai sus trebuie reținuți deoarece se aplica pentru orice interogare. In cazul nostru, trebuie sa obținem:
Coloane: Numele, Pretul – din tabela Produs
Condiții: Pret < 10
Pentru implementare, din bara principala alegem CREATE Query Design. Apare fereastra cu tabelele disponibile, deoarece trebuie sa specificam din ce tabela provin coloanele pe care dorim sa le obținem in cadrul interogarii.
Selectam tabela Produs și apasam butonul OK.
In ecranul urmator, dam dublu-clic in tabela pe coloanele Nume si Pret, pentru a le include ca rezultat al interogarii. Ecranul trebuie sa arate așa:
Oservam urmatoarele:
In zona (1) se afișeaza tabelele din care extragem date pentru interogare
In zona (2) se afișeaza coloanele ce vor aparea in interogare și condițiile pe care trebuie sa le indeplineasca fiecare inregistrare pentru a fin inclusa in rezultat.
Acum trecem la Criteria, pe coloana Pret condiția <10, iar pe linia Sort alegem Descending pentru a ordona rezultatul descrescator.
Pentru a rula interogarea apasam butonul ! Run (stanga-sus):
Acum se afișeaza rezultatul interogarii:
Observam rezultatul interogarii: o lista cu numele și prețurile produselor, ordonata descrescator dupa preț.
Sa presupunem ca dorim sa afișam produsele cu prețul mai mare de 10 RON. Deci trebuie sa navigam din ecranul curent cu rezultatul interogarii, in ecranul anterior, unde am definit interogarea. Avem ca de obicei mai multe posibilitați:
Apasam View și alegem Design sau
Folosim butoanele din dreapta-jos
Intrați in modul design și modificați condiția astfel incat sa fie >10 și rulați interogarea. Veți observa rezultatele aferente. Navigați inapoi in Design și schimbați la varianta inițiala, <10.
Vom salva interogarea apasand butonul de inchidere X (1), și trecem denumirea „01 – Produse cu pretul mai mic de 10” (2):
Interogarea trebuie sa apara in zona unde se listeaza obiectele:
Dați dublu-clic pe interogare și aceasta se deschide, afișand rezultatele. Putem trece in modul de editare al interogarii, salva modificarile, etc. La final, inchideți interogarea.
Acum dorim sa cream o noua interogare, similara cu cea anterioara:
Sa se afișeze codurile bonurilor care conțin produse cu prețul mai mic de 10, in cantitate mai mica de 5 bucați.
Observam ca in plus fața de coloanele anterioare, trebuie sa adaugam coloanele CodBon și Cantitate care provin din tabela ContinutBon. Urmam pașii descriși in continuare:
Din lista de obiecte, clic dreapta pe interogarea anterioara (01 – Produse…) apoi Copy
Tot in lista de obiecte, clic dreapta și Paste. Modificam denumirea astfel incat sa fie „02 – Bonuri cu produse cu prețul mai mic de 10, sub 5 buc”. Am facut acești pași pentru a duplica interogarea, deoarece este similara cu prima;
Intram in modul de editare al noii interogari, dand clic-dreapta pe noua interogare, apoi Design View:
Prin drag-drop tragem tabela ContinutBon in zona interogarii;
In tabela ContinutBon dam dublu-clic pe CodBon și ConținutBon pentru a le adauga in zona de rezultate;
Selectam coloana CodBon dand clic pe zona gri de sus (3) și apoi o mutam in stanga coloanei Produs
La Cantitate, punem condiția < 5
Rulam interogarea (butonul ! Run). Este posibil sa avem coduri de bon care apar de mai multe ori, aceasta se intampla deoarece un bon poate conține mai multe produse cu pretul mai mic de 10 și in cantitate mai mica de 5. Pentru a obține valori unice pentru CodBon:
Clic in zona de tabele pentru a activa proprietațile interogarii (Property Sheet)
Alegem Unique Values
Trebuie sa debifam Show pentru coloanele din tabela Produs (deoarece aceastea cauzeaza duplicarea datelor) și sa scoatem ordonarea dupa Pret (de la Sort).
Rulam interogarea și observam ca acum se afișeaza valori unice pentru codurile de bonuri. Salvam interogarea și inchidem.
Felicitari! Acum este randul Dvs.!
In continuare creați interogari pentru a afișa date conform cerințelor de mai jos. Salvați introgarile folosind nume relevante, sub forma „## - Descriere interogare” Observație: Prefixarea interogarilor cu numere ne asigura ca acestea se afișeaza in ordinea dorita de noi. In plus, este intotdeauna o idee buna sa dam nume relevante interogarilor, astfel incat colegii care lucreaza la același proiect--și profii care evalueaza exercițiile—sa poata ințelege ușor menirea interogarii respective! Și acum, interogarile de rezolvat:
Numele magazinului unde s-a emis bonul 24 – tabelele Magazin și Bon, condiție pe CodBon;
Numerele bonurilor emise in magazinul cu Cod Magazin 3;
Numele marfurilor vandute in anul curent--folosiți ca și criteriu Year([DataBon]) = Year(Now()). Observație 1: am folosit paranteze patrate pentru numele coloanei! Observație 2: de obicei in campul Criteria punem un operator de comparație (> < =) urmat de o valoare. In cazul de fața am folosit o egalitate; sa reținem aceasta posibilitate!
Magazinele unde s-au vandut produse de panificație – folosiți criteriul LIKE '*Paine*'. Explicație: dorim sa gasim toate produsele al caror nume conține textul „Paine”, de aceea folosim LIKE și abrevierea *, care inlocuiește orice caracter. Alt exemplu: daca dorim produse care incep cu textul „Paine…” vom folosi LIKE 'Paine*' – acum caracterul * este doar dupa text.
Magazinele care au emis bonuri intre iulie și septembrie 2013 – datele calendaristice se izoleaza folosind caracterul diez, in felul urmator: BETWEEN #7/1/2013# AND #9/30/2013#
Produse cu prețul cuprins intre 5 și 10 RON sau 20 și 30 RON. Explicație: aici avem doua intervale, iar prețul produsului trebuie sa se situeze in primul SAU al doile interval. Deci, la Pret vom folosi criteriul BETWEEN 5 AND 10, și pe linia or: (imediar sub Criteria) adaugam al doilea interval.
Aflați codurile bonurilor emise in ultimele doua luni. Atenție: acest gen de interogare se refera la ultimele doua luni de la data execuției interogarii! Deci trebuie sa luam ca punct de referința data curenta:
Așadar, datele pe care le cautam sunt mai mari decat Now() – 2 luni. Totuși, nu putem pune condiția Month([DataBon]) > Month(Now())-2. Daca suntem in luna Februarie, condiția va evalua daca DataBon > 0 și va fi intotdeauna adevarata. In consecința toate bonurile vor satisface aceasta condiție și vor aparea in lista de rezultate (incluzand bonurile emise dupa februarie!) Soluția este sa folosim o funcție care returneaza data calendaristica precisa a momentului cu 2 luni in urma. Funcția este DateAdd(interval, numar, data) – uitați-va in help sau Google la descrierea funcției, pentru a ințelege cum funcționeaza (important pt. examen). Condiția pe care o folosim pentru data bonului va fi: > DateAdd('m'; -2; Now()). Modificați apoi interogarea pentru a afișa bonurile din ultima luna, și observați ca avem mai puține rezultate.
Observații:
Textele se includ in ghilimele
Datele se includ folosind caracterul diez #
Numerele se scriu direct, fara a folosi caractere pentru a le include
Numele de coloane se izoleaza folosind paranteze patrate: [Coloana]
Observație: in campul de criterii putem adauga oricate condiții legate prin operatorii logici AND și OR. Ordinea de verficare a condițiilor poate fi influențata prin folosirea parantezelor.
Daca putem folosi funcții in zona de criterii, rezulta ca vom putea folosi și operații matematice. Ne putem convinge creand o interoare simpla pe tabela Produs, cu condiția Pret > 5+5. Putem introduce operații oricat de complexe, influențand ordinea evaluarii prin paranteze.
Operațiile pot fi folosite și pentru a adauga in rezultatul interogarii coloane noi, care nu exista in tabelele originale. De pilda, vrem sa afișam produsele și o coloana care sa conțina prețul produsului majorat cu 10%. Cream o interogare bazata pe tabela Produs, dupa cum urmeaza:
Am introdus coloanele Nume și Pret, apoi in a treia coloana am intrdous formula:
PretMajorat: [Pret]*1,1
Cuvantul PretMajorat reprezinta numele noii coloane, și este desparțit de formula propriu-zisa prin simbolul „:” (doua puncte). Putem folosi orice denumiri dorim pentru noua coloana, dar in general este de preferat sa folosim „notația camila”, ca și in cazul celorlalte nume de coloane sau variabile.
Rulam interogarea și rezultatul este:
Observați a treia colana, care are ca rezultat operația [Pret] * 1.1. Dorim sa formatam aceasta coloana ca și valuta, astfel incan valorile sa fie prefixate cu simbolul aferent (In cazul de fața „$”, dar acesta poate fi determinat de setarile regionale ale calculatorului). Ne intoarcem in Design View și:
Activam Property Sheet (este posibil sa fi fost deja activat).
Selectam coloana PretMajorat, pentru a-i modifica proprietațile.
La Format alegem valuta relevanta. De asemenea, putem modifica celelalte proprietați pentru a modifica numarul de decimale etc.
Evident, in expresiile de calcul ale coloanelor putem combina mai multe coloane; vom exemplifica mai tarziu cand vom inmulți prețul cu cantitatea produselor pentru a calcula totalul unui bon.
Funcțiile de agregare sunt folosite pentru a sumariza informațiile dintr-un set de rezultate. In esența, informația disponibila pe mai multe linii ale rezultatului este sumarizata in mai puține linii.
Creați acum o interogare simpla, care afișeaza codurile tuturor produselor, și rulați-o. Rezultatul consta intr-o singura coloana:
In cazul de mai sus aveam in baza de date 5 produse, și au fost afișate codurile lor. Acum intoarceți-va in Design View și dați click pe Totals (1)
Observați ca in zona coloanelor (2) apare linia Total, care ne permite sa adaugam funcții de agregare pentru coloane. Din lista derulanta, alegeți Count. Rulați apoi interogarea. In imaginea de mai jos am afișat in stanga rezultatul anterior, fara Count, iar in dreapta am afișat rezultatul nou:
Observam ca funcția Count, aplicata coloanei CodProdus, a sintetizat informația returnand o singura linie cu numarul total de produse. De asemenea, observați ca numele coloanei a fost schimbat la CountOfCodProdus. Salvați acum interogarea folosind o denumire relevanta.
Exercițiu: creați o noua interogare in care sa gasiți suma prețurilor tuturor produselor. Folosim coloana Pret din tabela Produs și aplicam funcția de agregare Sum. In mod similar putem gasi valorile medii, minime, maxime, deviația standard etc.
Funcțiile de agregare sunt deosebit de folositoare pentru sinteza datelor, atunci cand le folosim pentru a afla valoarea unei proprietați pentru diferite grupuri. In exemplul urmator, vom gasi numarul total de produse de pe fiecare bon. Informația de care avem nevoie se gasește in tabela ContinutBon. Dați dublu-clic pe aceasta tabela pentru a observa inregistrarile:
Vom folosi coloanele:
CodBon, pe care aplicam funcția Group, pentru a crea „grupuri” in cadrul rezultatului. Pentru fiecare CodBon se creaza cate un grup. In imaginea de mai sus avem 3 grupuri, corespunzator celor 3 bonuri diferite (24, 25, și 26).
Cantitate, pe care aplicam funcția Sum, pentru a insuma numarul de produse din cadrul fiecarui grup creat pe CodBon. De exemplu, pentru bonul 24, numarul total de produse este 10 + 8 = 18.
Acum creați o noua interogare bazata pe tabela ConținutBon, cuprinzand campurile CodBon și Cantitate, și activați butonul Totals.
Alegeți Group By pentru CodBon și Sum pentru Cantitate. Rulați interogarea și observam rezultatul. In imaginea de mai jos am inclus și rezultatul anterior, in scop demonstrativ:
Observam ca pentru fiecare CodBon s-au insumat cantitațile de produse aferente. Salvați interogarea cu un nume relevant, și creați noi interogari pentru urmatoarele cerințe:
Observație: in interogarile de mai jos, folosiți doar doua coloane: una pentru grupare, și cealalta pentru funcții agregate—chiar daca aveți nevoie de mai multe tabele. Incepeți prin a determina tabelele și coloanele necesare. Salvați fiecare interogare folosind nume relevante.
Numarul de bonuri emis de fiecare magazin.
Numarul total de produse vandute in fiecare oraș.
Numarul total de unitați vandut din fiecare produs. Ordonați descrescator dupa numarul total de unitați, pentru a obține topul celor mai bine vandute produse.
Similar cu interogarea de mai sus, ordonați crescator pentru a afla cele mai slab vandute produse.
Numarul total de produse vandute in fiecare luna calendaristica (grupați dupa Month([DataBon]))
Sa revenim asupra interogarii in care calculam numarul total de produse vandut pe fiecare bon. Dorim sa gasim doar bonurile care conțin mai mult de 20 de produse, deci vom adauga aceasta condiție la Criteria:
Rezultatul va conține bonurile care indeplinesc aceasta condiție.
Acum dorim sa modificam interogarea noastra pentru a gasi pe fiecare bon, numarul total de produse care au prețul mai mare de 7. Intram in DesignView și:
Ștergem condiția >20 de la Cantitate.
Adaugam tabela Produs (drag-drop din lista de obiecte din stanga).
In tabela Produs, dam dublu-clic pe Pret pentru a-l adauga in rezultat.
Pe linia Total, in dreptul coloanei Produs, alegem din lista derulanta Where. Astfel, specificam ca aceasta coloana este folosita pentru o comparație (filtrare) și deci nu va aparea in rezultate. Observați ca Access de-bifeaza automat Show din dreptul coloanei.
La Criteria, introduceți condiția >7.
Rulam interogarea și rezultatul interogarii trebuie sa arate așa:
Observam ca acum avem mai puține bonuri, cu un numar mai mic de produse, deoarece prin filtrare am redus numarul de produse insumat pe fiecare bon.
In concluzie: cerințele interogarilor trebuie citite cu atenție, pentru a determina coloanele pe care punem condițiile. Daca aceste coloane nu sunt cuprinse in rezultat, trebuie sa alegem din funcțiile de agregare opțiunea Where.
Observație: Ca regula generala, terbuie sa avem o coloana cu funcția Group By, o alta coloana cu un rezultat agregat (Sum, Count, Average) și eventual mai multe coloane cu condiții.
Acum rezolvați urmatoarele interogari. (Observație: deoarece tabelele conțin—in mod intenționat—puține date, puteți vereifica manual rezultatele interogarilor).
Numarul total de bonuri emis dupa 1 noiembrie 2013. Observație: nu avem grupare! In zona de rezultat vom avea Count pe CodBon, și condiția aferenta datei calendaristice, cu Where.
Afișați prețul celui mai scump produs.
Afișați prețul mediu al produselor care conțin textul „Paine”.
Afișați numarul total de produse vandut in fiecare oraș, pentru produsele cu prețul cuprins intre 5 și 10 lei.
Afișați magazinele cu cele mai mare numar de produse vandut in luna crenta.
Odata salvate, interogarile trebuie editate pentru a schimba valorile crtieriilor. Un mod mult mai efeicient de a face interogarile mai flexibile este sa solicitam utilizatorului valoarea unui parametru la momentul cand se ruleaza interogarea. Pentru aceasta, introducem intre paranteze patrate textul care va fi afișat la momentul rularii. De exemplu, vrem sa aratam numele magazinului in funcție de un CodMagazin specificat de utilizator:
La Criteria in dreptul coloanei CodMagazin introduceți expresia:
[Introduceti codul magazinului]
Aceast text va aparea pe ecran la memntul rularii, iar valoarea introdusa de utilizator va fi folosita in cadrul interogarii. Realizați acum interogarea de mai sus, rulați-o și apoi salvați-o.
Parametrii pot fi folosiți și pentru calcule in cadrul coloanelor. Realizați o interogare conform imaginii de mai jos, pentru a afișa prețurile majorate cu un procent introdus de utilizator:
Salvați interogarea și inchideți-o.
Sa presupunem ca dorim sa gasim produsele al caror preț este mai mare decat prețul mediu al tuturor produselor. Este evident ca va trebui sa adaugam un criteriu pentru preț. Dar prețul mediu este obținut in urma agregarii mai multor coloane, iar noi trebuie sa comparam prețul fiecarui produs cu pretul mediu. Deci nu putem sa rezolvam cerința introducand criteriul > Avg(Pret), deoarece Avg este calculat la finalul interogarii, dupa ce rezultatele au fost extrase din baza de date.
Soluția este sa realizam mai intai o interogare care calculeaza prețul mediu al produselor, apoi interogarea finala, care compara prețul produselor cu prețul mediu obținut in pasul anterior.
Realizați o interogare pentru a afișa prețul mediu al produselor, ca in imaginea de mai jos.
Schimbați denumirea coloanei astfel incat sa fie PretMediu, și pe linia Total alegeți funcția Avg. Rulați interogarea și observați valoarea rezultatului, precum și numele coloanei:
Salvați interogarea, cu numele „Media Preturilor Produselor”.
Creați o noua interogare și alegeți ca surse de date tabela Produs și intergoarea Media Preturilor Produselor (salvata anterior), conform imaginii de mai jos:
Pentru coloana Pret am introdus la Criteria condiția > [PretMediu], care provine din interogarea anterioara. (Sa observam ca intre tabela și interogare nu exista nicio relație, deoarece nu au chei comune). Rulați interogarea, obsevați rezultatul și apoi salvați.
Observație: cand realizam interogari bazate pe alte interogari, este foarte important:
Sa comparam aceleași tipuri de date, adica prețuri cu prețuri, coduri cu coduri etc;
Sa fim atenți la numarul de rezultate (linii) pe care le returneaza sub-interogarea. In cazul de mai sus, comparam valoarea prețului cu media prețurilor—adica verificam daca o singura valoare este mai mare decat o alta valoare. Daca sub-interogarea ar fi returnat mai mult de o valoare ca rezultat, atunci comparația nu putea fi realizata (deoarece am fi comparat o valoare cu mai multe valori) și interogarea nu se executa. Pentru a va convinge, modificați interogarea cu prețul mediu și scoateți funcția Avg, apoi rulați din nou interogarea principala.
De acum devine evident ca interogarile pot fi deosebit de complexe, combinand criterii, grupari, sub-interogari etc. Vom reveni asupra sub-interogarilor in capitolul despre SQL.
In jargonul profesional, operațiile pe tabele se abreviaza RCUD – Read, Create, Update, Delete. Pana acum am discutat despre interogari de extragere a datelor (read); in continuare vom prezenta ștergerea și modificarea datelor.
In urmatorul exemplu vom mari cu 10% prețurile tuturor produselor care au TVA-ul mai mici de 20%. Inainte de a rula interogarea, sa observam produsele al caror preț va fi modificat:
Creați o noua interogare bazata pe tabela Produs.
Activați butonul Update, și completați zona de detalii conform cu imaginea de mai jos:
Atenție: numele coloanelor trebuie intotdeauna inclus intre paranteze patrate!
Rulați interogarea și confirmați modificarea datelor. Dupa execuție, interogarea ramane in același ecran. Pentru a observa datele modificate, trebuie sa deschideți tabela Produs:
Acum inchideți și salvați interogarea, apoi modificați prețurile la valoarea inițiala (pentru a menține consecvența rezultatelor pe parcursul exercițiului).
Deoarece urmatoarele cerințe modifica ireversibil datele, creați o copie a bazei de date, inchizand-o și apoi duplicand fișierul.
Realizați interogari pentru urmatoarele cerințe, pe fișierul de rezerva:
Sa se modifice prețurile produselor cu un procent introdus ca parametru de la tastatura.
Sa se reduca cu 15% prețul celor mai slab vandute produse in anul curent (ultimele doua produse). Aici avem nevoie de o sub-interogare care sa gaseasca CodProdus pentru cele mai slab vandute produse. Rezultatul trebuie limitat la 2 produse, modificand proprietațile interogarii (Top Values). Experimentați pana obțineți codurile de produs corecte. Apoi, interogarea principala (care modifica prețurile) trebuie sa puna condiția CodProdus IN (rezultatul sub-interogarii).
Atenție: nu exista Undo pentru ștergerea datelor din tabele! Citiți cu atenție alertele emise de Access la rularea interogarilor de ștergere! O interogare fara niciun fel de restricție poate șterge deoadata cateva tabele fara posibilitatea de a le recupera. De aceea e esențial sa testam interogarile de ștergere pe copii de siguranța (backup) ale bazei de date. Atenție cu interogarile de ștergere in special pe bazele de date din sistemele de producție!
In continuare vom adauga o inregistrare in tabela Produs, dupa care o vom șterge.
Deschideți tabela Produs și adaugați un nou produs. Notați-va valoarea lui CodProdus, deoarece il vom folosi pentru ștergere.
Inchideți tabela Produs și creați o noua interogare, bazata pe tabela Produs. Apasați din bara de instrumente butonul Delete și configurați interogarea ca in ecranul de mai jos, astfel incat sa ștergem produsul cu CodProdus = 7.
Rulați interogarea. Apare urmatorul mesaj. Citiți-l cu atenție!
Access ne atenționeaza ca vom șterge o inregistrare, și ca nu avem opțiunea Undo.
Dați clic pe Yes pentru a confirma ștergerea. Mesajul de atenționare dispare și revenim in ecranul Design View al interogarii. Deși nu este aparent, ștergerea s-a realizat. Pentru a va convinge, deschideți tabela Produs și observați ca produsul a fost șters.
Salvați și inchideți interogarea.
Acum vom aborda un caz mai complex. Sa presupunem ca (dupa ce am efectuat o copie de rezerva a bazei de date) vrem sa ștergem toate bonurile mai vechi de o luna, pentru a preveni umplerea discului cu date vechi.
Inchideți baza de date și creați o copie, apoi deschideți copia.
Creați o noua interogare bazata pe tabela Bon.
Nu apasam inca butonul Delete din bara de opțiuni. Pornim prin a crea mai intai o interogare de selecție, pentru a ne asigura ca obținem doar inregistrarile care trebuie șterse. Configurați interogarea ca in imaginea de mai jos:
Funcția DateAdd returneaza momentul in timp situat la o luna inaintea datei curente, deci comparația cu acest moment ne asigura ca vom obține bonurile mai vechi de o luna.
Rulați interogarea și observați ca datele calendaristice ale bonurilor rezultate sunt mai vechi de o luna. Notați-va și numarul de inregistrari returnate de aceasta interogare. Apoi intoarceți-va in Design View.
Acum activați butonul Delete din bara de opțiuni, și rulați interogarea. Apare fereastra de confirmare, care ne anunța numarul de inregistrari ce vor fi șterse. Acest numar trebuie sa fie același cu numarul de inregistrari returnat la pasul anterior (3).
Apasați Yes pentru a confirma ștergerea. Apare un noua fereastra:
Mesajul ne anunța ca interogarea nu poate fi executata din cauza de incalcare a regulilor referitoare la chei, și din zero motive legate de incalcarea regulilor de incuiere (blocare) a scrierii in tabele (vom explica mai tarziu despre regulile de blocare). Deci, problema este cauzata de incalcarea regulilor referitoare la chei. Dar care sunt aceste reguli? Daca apasați Help e posibil sa le gasiți. Mai simplu este sa cautați in Google textul „access delete key violations”. Pentru a economisi timp, insa, iata explicația:
Apasați No pentru a renunța la execuția interogarii.
Salvați interogarea și inchideți-o, pentru a putea reveni mai tarziu.
Din bara de opțiuni, accesați DATABASE TOOLS > Relationships. In imaginea de mai jos avem relațiile:
Sa ne aducem aminte ca fiecarei inregistrari din tabela Bon ii corespund mai multe inregistrari in tabela ContinutBon, pentru a marca produsele și cantitațile vandute pe bonul respectiv. Daca ștergem un bon, atunci in tabela ContinutBon bon vor ramane inregistrari orfane—adica fara corespondent in tabela Bon. In imaginea de mai jos am afișat in stanga tabela de bonuri. Cele taiate cu linie roșie vor fi șterse (CodBon 24 și 25). In dreapta avem tabela ConținutBon și observam produsele vandute pe bonurile 24 ș 25.
Daca s-ar efectua ștergerea, ar ramane in baza de date vanzari despre care pierdem informația despre data calendaristica a vanzarii (DataBon) și nu vom ști nici in ce magazin au fost vandute respectivele marfuri (daca ștergem bonul pierdem informația despre CodMagazin).
Access insa refuza sa ștearga bonurile din cauza modului in care am definit relația intre tabele:
Dați clic-dreapta pe relația dintre tabelele Bon și ConținutBon, apoi Edit Relationship Apare ecranul de editare al relației:
Cand am creat relația intre tabele, am bifat Enforce Referential Integrity. Aceasta opțiune asigura ca pentru o cheie externa, exista o valoare corespondenta a cheii primare. In alte cuvinte, pentru orice inregistrare din ConținutBon exista un Bon valid. Ori daca am fi șters bonuri, am fi incalcat aceasta regula. Din acest motiv, Access nu a permis ștergerea. Soluția este:
Bifați opțiunea Cascade Delete Related Records. Aceasta opțiune va șterge inregistrarile aflate in relație cu bonurile. Adica, la ștergerea unui bon se vor șterge automat și inregistrarile corespondente in tabela ConținutBon, astfel incat baza de date sa ramana intr-o stare consistenta, fara inregistrari orfane.
Apasați OK pentru a edita relația, apoi inchideți ecranul Relationships.
Rulați din nou interogarea de ștergere, apoi deschideți tabela Bon pentru a verifica daca s-au șters bonurile.
Acum este randul Dvs:
Ștergeți magazinele din București. Observație: trebuie modificata relația dintre Magazin și Bon.
Pentru a continua, inchideți baza de date pe care ați exersat ștergerile, și deschideți baza de date care conține toate inregistrarile.
Observație: intotdeauna cand ștergem inregistrari dintr-o baza de date aflata intr-un sistem de producție (live) exista consecințe! In general, datele din sistemele live se arhiveaza, fara a se șterge definitiv. In cazul nostru:
Daca ștergem un produs, ii pierdem prețul și nu vom mai putea calcula corect valoarea bonurilor
Daca ștergem un bon, raman inregistrari „orfane” in ContinutBon, etc.
De aceea, este necesar sa planificam impactul ștergerii inregistrarilor.
In continuare dorim sa aflam cantitatea totala de vanzari pentru fiecare produs, in funcție de zona geografica (oraș). Observați ca avem grupare in funcție de doua criterii (Produs, Oraș) și funcția de agregare Sum(Cantitate). Acest tip de cerința se realizeaza cu o interogare Crosstab.
Creați o noua interogare in care preluați toate tabelele.
Dați clic pe butonul Crosstab din bara de meniuri, și configurați interogarea ca in imaginea de mai jos:
Rulați interogarea. Rezultatul trebuie sa arate așa:
Rezultaul arata totalul cantitații vandute din fiecare produs, in fiecare oraș. Acum sa analizam modul in care am definit cele 3 elemente din interogare (antetul liniei, antetul coloanei și valorile din tabel):
Pentru Row Heading (antet linie) am ales Oras, folosind funcția de agregare Group By.
Pentru Column Heading (antet coloana) am ales Produs, folosind funcția de agregare Group By.
Pentru Value (valoarea efectiva prezentata in tabel) am ales Cantitate și funcția de agregare Sum.
Interogarile Crosstab sunt deosebit de importante pentru analiza datelor. De obicei, aceste interogari sunt incluse in rapoartele pentru management și pot folosi ca punct de plecare pentru fundamentarea deciziilor tactice și strategice, cum ar fi introducerea sau excluderea unei linii de produse, crearea unei politici de prețuri, extinderea geografica etc. De obicei, acest tip de interogari se realizeaza pe bazele de date agregate la nivelul unui teritoriu geografic extins și pe o perioada de timp semnificativa, astfel incat concluziile sa fie relevante.
Realizați interogari Crosstab pentru urmatoarele cerințe:
Cantitatea totala de produse vandute in fiecare luna (coloane), in funcție de Oraș (linii).
Prețul mediu al produselor vandute in fiecare luna (coloane), pe fiecare magazin (linii).
|
