INTEROGĂRI

CAPITOLUL 9 INTEROGĂRI După studierea acestui capitol, veţi fi capabili să: • descrieţi tipurile de interogări; • creaţi interogări (prin proiectare independentă sau cu ajutorul sistemului de asistenţă) care vor selecta date din unul sau mai multe tabele; • executaţi interogări; • actualizaţi datele din tabele prin intermediul interogărilor; • obţineţi cîmpuri de date noi în baza informaţiilor existente; • creaţi totalizări ale datelor din unul sau mai multe tabele; • reprezentaţi compact subseturi de date din unul sau mai multe tabele. 9.1. Noţiuni generale despre interogări Sistemele de gestiune a bazelor de date au fost concepute pentru a păstra informaţii şi pentru a automatiza procesul de prelucrare a acestor informaţii. Chiar şi pentru o bază de date cu cîteva sute de înregistrări, căutarea manuală a datelor care satisfac anumite condiţii este anevoioasă. Menţionăm că există baze de date care conţin milioane de înregistrări. De exemplu, unii operatori de telefonie mobilă din Republica Moldova au peste 1 000 000 de abonaţi! La ultimele alegeri parlamentare din ţară au participat peste 1,5 milioane de alegători. Deci, în cazul votării electronice, se vor prelucra peste 1,5 milioane de înregistrări ale unei baze de date! Căutarea unor date poate implica consultarea cîtorva tabele. De exemplu, pentru a afla ce discipline studiază un elev anume din baza de date Liceu, trebuie să examinăm tabelele E levi, C lase, P rof_dis_clasa şi D iscipline. Să ne convingem! • Consultaţi baza de date Liceu şi determinaţi ce discipline studiază elevul D an M oraru. Exerciţiul propus este doar un mic argument menit să demonstreze necesitatea cercetării şi prelucrării automatizate a informaţiilor unei baze de date. Pentru selectarea rapidă din unul sau din mai multe tabele a seturilor de date care corespund unor condiţii, dar şi pentru actualizarea accelerată a înregistrărilor, sistemele de gestiune a bazelor de date utilizează interogări - cereri de căutare şi/sau de acţiune în conformitate cu aceste condiţii. Interogările sînt obiecte ale sistem elor de gestiune a bazelor de date care reprezintă adresări de căutare, analizare şi/sau de modificare a datelor bazei. Menţionăm că în calitate de surse de date, în afară de înregistrările tabelelor bazei, o interogare poate folosi rezultatele altei interogări, executate anterior. De exemplu, pentru a afişa lista profesorilor cu salariul maximal, se vor crea două interogări: prima interogare va găsi valoarea M a x a salariului maximal, iar a doua - va selecta din tabelul Profesori înregistrările cu valorile din cîmpul Salariu, egale cu M a x (adică egale cu rezultatul primei interogări). Interogările îndeosebi se utilizează pentru: - vizualizarea unui subset de înregistrări dintr-un tabel, fără a-l deschide; - afişarea într-un singur tabel a informaţiilor din cîteva tabele; - actualizarea datelor tabelelor (modificări, adăugări, excluderi de date); - efectuarea calculelor asupra valorilor cîmpurilor şi obţinerea informaţiilor noi; - crearea totalurilor, valorilor medii etc. În funcţie de tipul acţiunii şi de rezultate, interogările se clasifică în cele: a) de selecţie; b) de excludere a unor înregistrări; c) de m odificare a unor înregistrări; d) de creare a cîm pu rilor rezu lta n te ; e) de gru pare şi totalizare; f) încrucişate. I n te r o g ă rile d e se le c ţie sînt cereri formulate în baza unor condiţii logice. Ele selectează un subset de date din unul sau mai multe tabele legate între ele. De exemplu, căutarea elevilor născuţi pînă la 10 ianuarie 1992, afişarea elevilor clasei a 10-a B sînt interogări de selecţie. I n te r o g ă r ile d e e x c lu d e r e a u n o r în r e g is tr ă r i reprezintă cereri de eliminare dintr-un tabel a tuturor înregistrărilor care satisfac criteriile logice specificate. De exemplu, cererea de ştergere din tabelul E levi al bazei de date Liceu a informaţiilor despre elevii claselor a 12-a (în legătură cu absolvirea liceului) reprezintă o astfel de interogare. I n te r o g ă r ile d e m o d ific a r e a u n o r în r e g is tr ă r i schimbă valorile unui cîmp al tabelului după acelaşi algoritm. De exemplu, mărirea cu 50% a valorilor din cîmpul Salariu al tabelului P rofesori poate fi realizată cu ajutorul unei interogări de modificare. Uneori se pot solicita informaţii care trebuie afişate în cîmpuri noi. De exemplu, vîrsta elevilor se va prezenta într-un cîmp aparte. Cu acest scop se va utiliza o in te r o g a r e d e c r e a r e a c îm p u r ilo r r e z u lta n te . I n te r o g ă r ile d e g r u p a r e ş i to ta liz a r e se folosesc pentru sumarea datelor cîmpurilor, obţinerea valorilor medii, a celor minimale sau maximale etc. De exemplu, calcularea numărului total de ore pe săptămînă realizate în fiecare clasă a bazei de date Liceu se va face cu ajutorul unei interogări de grupare şi totalizare. I n te r o g ă r ile în c r u c iş a te sînt destinate prezentării compacte a informaţiilor sub formă de tabel. De exemplu, informaţia despre numărul săptămînal de ore rezervat fiecărei discipline în fiecare clasă poate fi afişată cu ajutorul unei interogări încrucişate sub forma următorului tabel: Anul_de_studii Nume_clasa Biologia Chimia ... 10 A 2 3 10 D 1 1 11 A 3 2 12 D 1 1 92 Aşa cum rezultatele interogărilor depind de informaţiile din tabele, modificările realizate asupra datelor din tabele vor atrage după sine modificarea rezultatelor interogărilor (evident după executarea lor repetată). La rîndul său, în cazul interogărilor a) - d), modificarea de către utilizator a rezultatelor interogării poate duce la schimbarea conţinutului tabelelor. Din aceste considerente rezultatul interogărilor a) - d) se numeşte set dinamic de date. Setul dinamic de date nu este constant, deci nu se memorează. El există doar pe parcursul execuţiei interogării. Pentru a crea o interogare nouă: 1. Selectăm clasa de obiecte Q u eries de pe bara de obiecte Objects. În zona de conţinut a ferestrei bazei de date apar opţiunile: - Create query in Design view (crearea interogării în regim de proiectare); - Create query by using wizard (crearea interogării cu ajutorul unui program de asistenţă). Executăm un dublu-clic pe prima opţiune. O b se rv a ţie : Pasul 1 este echivalent cu selectarea New -> Design View. 2. Apare fereastra interogării şi fereastra de dialog Show Table (fig. 9.1). Selectăm pe rînd tabelele necesare apăsînd de fiecare dată butonul Add. Tabelele alese (mai exact cîmpurile lor) apar în interiorul ferestrei interogării împreună cu reprezentarea grafică a relaţiilor dintre ele (fig. 9.2). Fig. 9.1 Fig. 9.2 3. Fereastra unei interogări este divizată în două zone. Zona superioară afişează denumirile cîmpurilor tabelelor. Zona inferioară afişează un formular QBE* în care programatorul poate folosi exemple de declaraţii parţiale sau expresii pentru a crea o interogare. Alegem cîmpurile care vor fi utilizate pentru scrierea criteriului de selecţie şi cîmpurile ale căror valori vor fi afişate. Adăugarea unui cîmp se face prin executarea unui dublu-clic pe denumirea lui sau prin selectarea şi „tragerea” acestui cîmp cu ajutorul mouse-ului. * Caracteristica QBE (Q uery by Exam ple) a fost creată iniţial pentru a permite utilizatorilor aplicaţiilor de bază de date să găsească şi să afişeze porţiuni de date fără a fi nevoiţi să ştie un limbaj de programare. pe parcursul timpului, aplicaţiile QBE au ajuns instrumente preferate pentru scrierea interogărilor. Majoritatea sistemelor de gestiune a bazelor de date şi-au dezvoltat propriile aplicaţii QBE, care permit formularea unui spectru larg de cereri de interogare. 93 Ordinea cîmpurilor afişate în formular poate fi schimbată în acelaşi mod ca şi ordinea coloanelor unui tabel afişate în regimul D a ta sh eet View. 4. Pentru fiecare cîmp adăugat în formularul QBE, în afară de cîmpul şi tabelul care conţine acest cîmp (afişate în rîndurile Field şi Table), se pot preciza: a) modul de sortare a înregistrărilor după acest cîmp (rîndul Sort) alegînd una din valorile A scen din g (crescător) sau D escen din g (descrescător); b) afişarea sau ascunderea valorilor cîmpului la executarea interogării (rîndul Show); c) o condiţie de selectare pe care o vor respecta valorile afişate (rîndurile C riteria şi or). 5. Putem afişa datele specificate de interogare pînă la salvarea ei prin selectarea comenzii D atasheet View din meniul View sau din lista derulantă -ţ de pe bara de instrumente Access. 6. Salvăm interogarea (instrumentul Save sau comanda cu acelaşi nume din meniul File). E x em p lu : În fig u ra 9.2 este prezentată în regim de proiectare o interogare care cere afişarea listei elevilor clasei a 10-a B, al căror prenume începe cu litera C. În figu ra 9.3 este arătată lista pînă la salvarea interogării. k LQ u e ry l : S e le ct Q u e ry [- □ X N um e e le v P re n e le v A n u l de studi Num e c la s a Bujor Călin 10 B Cozariuc Cătălina 10 B Mihalachi Cri st i na 10 B Record: M 1 1 ► 1 M +] of 3 Fig. 9.3 Întrebări si exerciţii » > O Cu ce scop se utilizează interogările? e Caracterizaţi principalele tipuri de interogări. e Descrieţi algoritmul de creare a unei interogări. O De ce rezultatul unor interogări se numeşte se t d in a m ic d e d a te? e Stabiliţi tipul următoarelor interogări: a) aflarea numărului de profesori de gen masculin şi a numărului celor de gen feminin; b) determinarea salariului mediu al profesorilor din baza de date Liceu; c) afişarea listei fetelor din clasele a 11-a; d) determinarea numărului de ore realizate săptămînal de fiecare profesor; e) căutarea profesorilor de gen feminin care predau în clasele a 10-a. © Examinaţi baza de date Liceu şi formulaţi două cereri de interogări: a) de selecţie; d) de creare a cîmpurilor rezultante; b) de excludere a unor înregistrări; e) de grupare şi totalizare; c) de modificare a unor înregistrări; f) încrucişate. 9.2. Interogări de selecţie Pornind de la faptul că interogările de selecţie sînt cele mai des utilizabile, Access stabileşte implicit acest tip pentru toate interogările nou-create. După cum vom vedea ulterior, utilizatorul trebuie să întreprindă acţiuni suplimentare pentru a schimba tipul interogării noi. Deci, pentru a crea o interogare de selecţie, vom alege tabelele şi cîmpurile necesare conform algoritmului descris în tema precedentă. 94 9.2.1. Criterii de selecţie j Un moment important în procesul de elaborare a unei astfel de interogări este scrierea criteriului de selecţie. > Dacă criteriul se formulează pentru un singur cîmp, atunci expresia logică, care va controla afişarea datelor, se scrie în celula C riteria a acestui cîmp. Menţionăm că operatorul Like este inserat automat de Access în cazul în care utilizatorul scrie un şablon de restricţionare a datelor. În particular, în celula C riteria se poate scrie o constantă de tip compatibil cu tipul valorilor cîmpului respectiv. E x em p lu : Pentru a afişa lista elevilor cu prenumele Ion, este suficient să se scrie în celula C riteria a cîmpului Pren_elev (din tabelul Elevi) şirul de caractere "Ion". Deoarece criteriile de selecţie sînt expresii logice Access, pentru scrierea lor pot fi utilizate funcţiile şi operatorii studiaţi în capitolul precedent, inclusiv cei logici. În acelaşi timp, formularul QBE oferă ajutor în crearea criteriilor compuse, formate din cîteva condiţii şi din operatorii AND şi/sau OR. Astfel: a) pentru un cîmp pot fi definite mai multe condiţii de selecţie legate între ele cu operatorul OR: prima se scrie în rîndul Criteria, iar celelalte - mai jos, cîte una în fiecare celulă; b) două sau mai multe condiţii din rîndul C riteria se consideră legate între ele cu operatorul AND. E xem ple: 1. Dacă în formularul interogării anterioare mai jos de valoarea "Ion" (în celula or) se va scrie "Vasile", atunci interogarea va afişa lista elevilor cu prenumele Ion sau Vasile. În cazul completării celulei C riteria din cîmpul N u m e_elev cu condiţia "B*", atunci interogarea va afişa lista elevilor cu prenumele Ion şi al căror nume începe cu litera "B". 2. Interogarea din figura 9.4 va afişa lista elevilor clasei a 11-a B născuţi în luna ianuarie. 3. Interogarea din fig u ra 9.5 va afişa lista elevilor din clasele 10-11 cu profil real. Evident, rezultatul va fi acelaşi dacă vom completa doar rîndul C riteria, substituind valoarea 10 cu expresia 10 OR 11. 4. Interogarea din fig u ra 9.6 va afişa lista profesorilor cu salariul lunar mai mare decît 2 500 lei şi mai mic sau egal cu 3 000 lei în ordinea crescătoare a acestor salarii. Fig. 9.4 Fig. 9.5 95 Evident, este imposibil de a prezice toate cererile de selecţie de care vor avea nevoie utilizatorii. Mai mult chiar, unele interogări se pot deosebi între ele doar prin unele valori din formularul QBE. De exemplu, interogările care vor afişa lista disciplinelor studiate în clasa a 10-a B, respectiv, în clasa a 11-a B, se vor deosebi doar prin valorile cîmpului A n u l_de_stu dii. În astfel de situaţii se poate crea o singură interogare, unde în loc de valoare, în cîmpul corespunzător, se va scrie un pa ra m etru . Un şir de caractere încadrat între simbolurile [ şi ], scris într-o celulă a rîndului C riteria este interpretat de Access drept parametru. Pentru fiecare parametru, la executarea interogării, mai întîi va apărea o fereastră de dialog în care utilizatorul va scrie valoarea parametrului (deci o valoare a cîmpului pentru care a fost creat parametrul), apoi se vor afişa înregistrările ale căror valori din cîmpul parametrului coincid cu valoarea scrisă de utilizator. De regulă, şirul de caractere ce defineşte parametrul este un text explicativ, care sugerează utilizatorului ce tip de valoare trebuie să scrie în fereastra de dialog. În mod implicit parametrul se consideră de tip Text. Pentru a modifica tipul parametrului, se va selecta Q u ery -> P aram eters. Va apărea fereastra Q u ery P aram eters, în care se va scrie fiecare parametru şi tipul lui. E x em p lu : În figu ra 9 .7 este reprezentată fereastra unei interogări de selecţie cu trei parametri: primul pentru cîmpul A nul_de_studii, al doilea - pentru cîmpul N um e_clasa, iar al treilea (de tip N u m ber) - pentru cîmpul N r_ore_saptam in a. Observăm că prin intermediul acestei interogări se poate afla lista disciplinelor studiate de elevii clasei indicate de utilizator, al căror număr de ore pe săptămînă este de asemenea specificat de utilizator. La executarea interogării din figu ra 9 .7 vor apărea pe rînd trei ferestre în care utilizatorul va preciza anul de studii, numele clasei (A, B, C sau D) şi, respectiv, numărul săptămînal de ore. 9.2.2. Interogări cu parametri Cod prut Nume_prof Pren_prof Data_prof Foto_prof 5a ariL Căsătorit Nuroe_prof Pren_prof Salariu Profesori Profesori Ascending Query! : Select Query Profesor O Ce constituie interogările de selecţie? e Care este rolul criteriului de selecţie într-o interogare? e Cu ce scop se folosesc parametrii într-o interogare? O Deschideţi baza de date Liceu. Creaţi o interogare de selecţie ce va afişa lista: a) fetelor bazei; b) diriginţilor; c) profesorilor născuţi pînă la 12 februarie 1970; d) profesoarelor născute în luna martie; e) băieţilor în ordine crescătoare a vîrstei lor; f) elevilor care nu au telefon; g) profesorilor de gen masculin care nu au îm plinit 50 de ani; h) elevilor născuţi vara; i) profesorilor al căror număr de telefon începe cu 022 48; j) elevilor care nu locuiesc în Chişinău; k) profesorilor al căror prenume începe cu litera a sau cu litera E; l) elevilor care nu învaţă Filosofia; m) elevilor care studiază Matematica 5 ore pe săptămînă. © Deschideţi baza de date Liceu. Creaţi o interogare de selecţie cu parametru ce va afişa lista: a) disciplinelor studiate de elevul specificat de utilizator; b) profesorilor care predau în clasa specificată de utilizator; c) profesorilor care locuiesc în localitatea specificată de utilizator; d) elevilor care studiază disciplina specificată de utilizator; e) profesorilor care predau disciplina specificată de utilizator. © Formulaţi şi creaţi 5 interogări de selecţie pentru baza de date Liceu. & pentru baza de date Liceu formulaţi şi creaţi 5 interogări de selecţie cu parametru. 9.3. Interogări de acţiune Interogările de acţiu n e se utilizează pentru a crea tabele noi în baza informaţiilor din tabelele existente şi/sau pentru a realiza modificări în aceste tabele. În fereastra bazei de date numele acestor interogări este precedat de semnul exclamării. Atenţie! Interogările de acţiune (cu excepţia celor care generează tabele) modifică conţinutul tabelelor. 9.3.1. interogări care generează tabele Interogările de selecţie extrag date din tabele şi le afişează doar la executarea interogării. Rezultatul unei astfel de interogări nu se păstrează (eventual într-un tabel). Cu acest scop, interogarea de selecţie poate fi transformată într-o interogare care va păstra rezultatul într-un tabel nou, deci va crea un tabel cu setul dinamic al interogării. Să alcătuim o interogare care va genera un tabel cu date despre elevii claselor a 10-a. 1. Elaborăm interogarea de selecţie corespunzătoare (fig. 9.8). 2. Din meniul Q u ery alegem M ake-T able Q uery... Apare fereastra M ake Table unde precizăm numele tabelului nou (de exemplu, C lasele_10) şi numele bazei de date în care va fi păstrat tabelul. Implicit, tabelul va fi creat în baza de date curentă. 3. Scriem numele tabelului, confirmăm prin apăsarea butonului OK, apoi salvăm interogarea. 97 Fig. 9.8 Fig. 9.9 4. Pentru a obţine tabelul C lasele_10, executăm interogarea recentă. Apare o fereastră cu mesaj de avertizare, în care confirmăm intenţia de creare a tabelului prin apăsarea butonului Yes. 9.3.2. Interogări de excludere a unor înregistrări Deseori apare necesitatea eliminării unor înregistrări din tabele. De exemplu, în cazul bazei de date Liceu să admitem că trebuie să excludem datele despre elevii claselor a 12-a (în legătură cu absolvirea liceului). 1. Aşa cum între tabelele C lase şi E levi există relaţia unu la mulţi, avînd caracteristica E xclu derea în cascadă a în reg istră rilo r (C a sca d e D elete R ela ted R ecords), este suficient să ştergem înregistrările din tabelul C lase care au valoarea cîmpului A n u l_ d e _ stu d ii egală cu 12. Invers nu este corect, deoarece tabelul E levi este subordonat tabelului C la se . 2. Elaborăm o interogare care afişează lista claselor. Din meniul Q u ery alegem D elete Q uery. În formularul QBE, în locul rîndurilor Show şi Sort, apare rîndul D elete. În celula C riteria a cîmpului A n u l_ d e_ stu d ii scriem 12 (fig. 9.9). 3. Salvăm interogarea. Ştergerea înregistrărilor se va realiza după executarea interogării. Atenţie! Aşa cum înregistrările şterse nu pot fi restabilite, se recomandă de afişat rezultatul interogării de selecţie înainte ca aceasta să fie transformată în una de excludere a înregistrărilor. Astfel, se va verifica corectitudinea acţiunii interogării. 9.3.3. Interogări de modificare a unor înregistrări Dacă valorile mai multor înregistrări ale unui cîmp pot fi transformate după acelaşi algoritm, atunci pentru automatizarea actualizărilor se va folosi o interogare de modificare. Să formulăm o interogare care va mări cu 50% valorile din cîmpul Salariu al tabelului P rofesori. 1. Creăm o interogare de selecţie care afişează valorile cîmpului Salariu. 2. Din meniul Q u e ry alegem U pdate Q u ery. În formularul QBE în locul rîndurilor S o rt şi S h ow apare rîndul U pdate To. Scriem expresia [Salariu]*1,5 în celula obţinută 98 la intersecţia rîndului U p d a te To şi a cîmpului S a la riu (fig. 9 .1 0 ). 3. Salvăm interogarea. Modificarea înregistrărilor se va realiza după executarea interogării. Atenţie! Interogările de modificare se execută o singură dată. La repetarea lor valorile se vor modifica din nou. Astfel, dacă vom executa de 2 ori ultima interogare, salariul profesorilor „se va mări” cu 125% în loc de 50%. 9.3.4. Interogări care adaugă înregistrări în tabele existente Fig. 9.10 Uneori trebuie să adăugăm într-un tabel înregistrări din alt tabel. De exemplu, fie tabelul E le v ii care conţine date despre elevii născuţi în luna mai. Dacă dorim să adăugăm în acest tabel înregistrările elevilor din tabelul E levi născuţi în luna iunie, vom proceda astfel: 1. Creăm o interogare de selecţie care va afişa lista elevilor născuţi în luna iunie. 2. Din meniul Q u ery alegem A p p e n d Q u ery. Apare fereastra M ake Table. Din lista derulantă Table N a m e selectăm E le v ii. 3. Salvăm, apoi executăm interogarea. Întrebări si exerciţii > > O Cu ce scop se utilizează interogările de acţiune? e Descrieţi algoritmul de creare a unei interogări: a) care generează tabele; c) de excludere a unor înregistrări; b) de modificare a unor înregistrări; d) care adaugă înregistrări în tabele existente. e Examinaţi baza de date Liceu. Creaţi o interogare ce va genera tabelul: a) T1 cu date despre profesorii de gen feminin; b) T2 cu date despre elevii născuţi în luna mai; c) T3 cu date despre diriginţii de clase; d) T4 cu date despre profesorii care predau matematica sau chimia; e) T5 cu date despre elevii care nu locuiesc în Chişinău. O Examinaţi baza de date Liceu. Elaboraţi o interogare ce şterge din tabelul: a) T1 datele despre profesorii născuţi vara; b) T2 datele despre elevii clasei a 11-a; c) T3 datele despre diriginţii claselor cu profil real; d) T4 datele despre profesorii care nu predau chimia; e) T5 datele despre elevii care locuiesc în Cricova. © Examinaţi baza de date Liceu. Alcătuiţi o interogare ce va: a) mări salariul profesorilor cu 500 de lei; b) micşora cu 300 de lei salariul profesorilor care predau doar o disciplină; c) mări cu 25% salariul profesorilor născuţi pînă la 01.01.1960. © Examinaţi baza de date Liceu. Creaţi o interogare ce va adăuga în tabelul: a) T1 date despre profesorii de gen masculin care predau matematica; b) T2 date despre elevii născuţi iarna; c) T3 date despre profesorii care nu sînt diriginţi şi locuiesc în Chişinău; d) T4 date despre profesorii care predau limba străină; e) T5 date despre elevii din Chişinău care învaţă în clasa a 10-a. 99 9.4.1. Interogări de creare a cîmpurilor rezultante (calculate) În capitolele precedente am menţionat că la proiectarea entităţilor unei baze de date relaţionale se vor exclude cîmpurile ale căror valori pot fi obţinute din cîmpurile rămase. Din aceste considerente în tabelul Elevi nu a fost inclus cîmpul Virsta. Valorile acestui cîmp depind de valorile cîmpului Data_elev. Să elaborăm o interogare care va afişa într-un cîmp nou vîrstele elevilor bazei de date Liceu. Interogarea nu va afecta structura şi datele tabelului Elevi. 1. Creăm o interogare de selecţie pe baza tabelului E levi. În primele două coloane ale formularului QBE includem cîmpurile N u m e_elev, Pren_elev, iar în locul denumirii coloanei a treia scriem expresia Virsta: DateDff(“yyyy”;[Data_elev];Date()). Menţionăm că V irsta este identificatorul cîmpului nou, iar funcţia D a te D iff(T ; D1; D2) returnează diferenţa exprimată în unităţi calendaristice T dintre datele D1 şi D2 (a se vedea tema F uncţii Access). Observăm că: - T este egal cu “yyyy”, deci exprimă ani; - D1 este [Data_elev], adică data naşterii elevului; - D2 este Date(), adică data curentă. 2. Salvăm interogarea. Cîmpul Virsta este dinamic. El există atît timp cît sînt afişate rezultatele interogării. 9.4.2. interogări de grupare şi totalizare Pentru a obţine rezultate bazate pe înregistrările unui sau ale mai multor tabele, se vor utiliza interogări de gru pare şi totalizare. Să definim o interogare care va afişa numărul total de elevi ai fiecărei clase din baza de date Liceu. 1. Creăm o interogare de selecţie pe baza tabelelor C lase şi E levi în care includem cîmpurile A n u l_ d e_ stu d ii, N u m e_clasa şi C od_elev. 2. Executăm un clic pe butonul Totals ([!]) de pe bara de instrumente. în formularul QBE apare rîndul Total (fig. 9.11). Completăm celulele acestui rînd: - în primele două coloane din listele derulante ale celulelor selectăm valoarea Group By (deoarece grupăm înregistrările după anul de studii şi numele clasei), iar 9.4. Interogări de totalizare i£ENi_elevi_cl(iS(i : Select Quen □ d ) ® A n u l _ d e _ s t u d i i N u m e c l a s a C o u n t O f C o d _ e l e v 1 0 4 2 9 1 0 B 1 8 1 0 C 3 7 1 0 D 3 1 1 1 A 3 3 1 1 B 2 6 1 1 C 2 2 1 1 D 2 3 1 2 A 2 2 1 2 B 2 4 1 2 C 2 0 | R eco rd : |TT| < l~ 1 of ll Fig. 9.11 Fig. 9.12 100 - în coloana a treia selectăm funcţia C ou n t (deoarece calculăm numărul de înregistrări ale cîmpului C od_elev). 3. Salvăm şi executăm interogarea. Rezultatul interogării este prezentat în figura 9.12. O b se rv a ţii: 1. Listele derulante ale celulelor rîndului Total oferă diferite fu n c ţii globale (funcţii aplicabile asupra grupurilor de celule de date) pentru obţinerea totalizărilor: Sum , M ax, M in , Avg, F irst etc. 2. În interogări de grupare şi totalizare, de asemenea, se pot formula criterii de selecţie. De exemplu, dacă pentru cîmpul C o d _ elev al interogării precedente se va scrie condiţia >25, atunci interogarea va afişa datele doar despre clasele al căror număr total de elevi este mai mare decît 25. 9.4.3. Interogări încrucişate Interogările încrucişate sînt interogări de totalizare care permit utilizatorului să stabilească exact modul în care vor fi afişate rezultatele sub formă tabelară. Astfel de interogări sînt recomandate în cazul unei cantităţi mari de date totalizatoare. La crearea unei interogări încrucişate se va ţine cont de următoarele restricţii: a) denumirile rîndurilor tabelului-rezultat pot fi valori din unul sau mai multe cîmpuri; b) denumirile coloanelor tabelului-rezultat pot fi valori doar ale unui singur cîmp; c) valorile celorlalte celule ale tabelului-rezultat sînt calculate cu ajutorul unei funcţii globale; d) înregistrările din rezultat nu pot fi ordonate după cîmpurile celulelor calculate. Să alcătuim o interogare care va afişa numărul total de ore rezervat fiecărei discipline în fiecare clasă a bazei de date L iceu. 1. Creăm o interogare de selecţie pe baza tabelelor Clase, D iscipline şi Prof_dis_clasa în care includem cîmpurile Anul_de_studii, N um e_clasa, N um e_disciplina şi N r_ore_saptam ina. 2. Din meniul Q u ery alegem C rosstab Q uery. În formularul QBE apar rîndurile Total şi C rosstab (fig. 9.13). 3. Completăm celulele rîndurilor Total şi C rosstab ca în fig u ra 9.13. Astfel, valorile cîmpurilor A nul_de_stu dii şi N u m e_clasa vor fi denumiri ale rîndurilor tabelului-rezultat, valorile cîmpului N u m e_d iscip lin a - denumiri ale coloanelor tabelului-rezultat, iar valorile cîmpului N r_o re_ sa p ta m in a vor fi sumate pentru a completa celelalte celule ale tabelului-rezultat (fig. 9.14). Fig. 9.13 Queryl : Crosstab Query □dl® Anul de. studii Nume_clasa Ei il olmgi a Chimia Educaţia civică ► mA 2 3 1 10 B 2 3 1 10 C 1 1 1 10 D 1 1 1 11 A 3 2 1 11 B 3 2 1 11 C 1 1 1 11 D 1 1 1 12 A 3 3 1 12 B 3 3 1 12 C 1 1 1 - 12 D 1 1 1 Fig. 9.14 101 Întrebări si exerciţii > 3 O Ce reprezintă interogările: a) de creare a cîmpurilor rezultante; b) de grupare si totalizare; c) încrucişate? e Descrieţi algoritmul de elaborare a unei interogări: a) de creare a cîmpurilor rezultante; b) de grupare şi totalizare; c) încrucişate. 0 Examinaţi baza de date Liceu. Aflaţi cu ajutorul unei interogări: a) vîrsta elevilor exprimată în zile; b) numărul de profesori de gen masculin şi numărul celor de gen feminin; c) numărul de clase la fiecare din profilurile real şi umanist; d) salariul mediu lunar al profesorilor; e) numărul de elevi din fiecare localitate; f) numărul de ore realizate săptămînal de fiecare profesor; g) numărul de ore realizate săptămînal de fiecare clasă; h) valoarea salariului minimal; i) profesorii care au salariul maximal; j) numărul de profesori care au salariul mai mic decît cel mediu. O Examinaţi baza de date Liceu. Formulaţi şi realizaţi două cereri de interogări: a) de creare a cîmpurilor rezultante; b) de grupare şi totalizare; c) încrucişate. Test de evaluare 1. Examinaţi baza de date Liceu. Creaţi o interogare ce va afişa lista: a) elevilor al căror prenume începe cu litera a ; b) elevilor născuţi joi; c) profesorilor care predau disciplina specificată de utilizator. 2. Examinaţi baza de date Liceu. Alcătuiţi o interogare ce va: a) adăuga în tabelul Elevicîmpul Adresa_Web, ale cărui valori vor fi formate în această ordine din numele, prenumele elevului şi secvenţa„@liceu.md". De exemplu, adresa Web a elevului Ion Popa va fi Popalon@liceu.md: b) genera tabelul Test cu date despre elevii care nu învaţă în clasele a 10-a A, a 11-a B, a 12-a C (inclusiv adresa Web descrisă în a)); c) elimina din tabelul Test datele despre elevii care învaţă în clasele a 10-a B, a 11-a C; d) modifica adresa Web a fiecărui elev din tabelul Test, substituind subşirul „liceu.md" cu secvenţa „elev.md"; e) adăuga în tabelul Test date despre elevii de gen feminin care învaţă în clasa a 10-a A; f) mări cu 40% salariul profesorilor cu vîrsta mai mare decît 40 de ani. 3. Examinaţi baza de date Liceu. Elaboraţi o interogare ce va afişa numărul: a) de elevi la fiecare din profilurile real şi umanist; b) elevilor de gen masculin şi numărul celor de gen feminin. 4. Creaţi o interogare încrucişată ce va afişa numărul elevilor în fiecare clasă a bazei de date Liceu (ca în figura 9.15). ' 4) Queryl: Crosstab Query | a -|| 0 |p£3^| Anul_de_studii Nume_clasa Real Umanist 10 A 29 10 B 18 1