Universitatea de Medicin i Farmacie Gr.T.Popa - Iai Facultatea de Medicin Informatic Medical i Biostatistic Conf. dr. Lucian Boiculese

1

Interogri asupra bazei de date Interogarea (query) este o metod de extragere de informaii din baza de date prin care

se pot executa diferite tipuri de operaii. Tipuri de interogri:

Pentru creare de tabele (create), Pentru actualizarea tabelelor (update), Pentru adugare de nregistrri, copiere, mutare ntre tabele (append), Pentru tergerea nregistrrilor (delete), Pentru realizarea interogrilor n cruce (crosstab), Pentru filtrarea tabelelor dup criterii specifice (practic reprezint rspunsuri la ntrebri) i salvarea rspunsului ntr-un nou tabel (select queries).

Limbajul de interogare de tip SQL - Structured Query Language Microsoft Access prezint o form elegant de lucru interactiv pentru crearea

interogrilor QBE (Query By Example). Pentru lansare n execuie (Access 2007i 2010): CREATE + QUERY DESIGN

2

Se va selecta n prima etap tabelul sau se vor selecta tabelele de lucru apoi se aleg cmpuile necesare pentru crearea interogrii. Meniul de proiectare/dezvoltare

a interogrii

Tabelul din cadrul bazei de date utilizate pentru crearea

interogrii

Cmpurile selectate pentru afiarea rezultatului interogrii

Criteriu de selecie doar cei din mediul urban (codificat u- nu

conteaz dac este scris cu litere mari sau mici)

3

Metod de lucru. Aciuni

Cmpurile selectate pot fi sortate cresctor-descresctor dup dorin, Coloanele de lucru pot fi afiate sau ascunse n interogarea creat (se bifeaz show), Se aplic criterii de selecie pentru crearea filtrelor asupra tabelelor, Se pot crea coloane noi cu valori calculate exist la dispoziie o bogat bibliotec de funcii, Se pot realiza totalizri asupra tabelelor n cauz (calcul indicatori stat. pt. grupuri definite dup anumite criterii), Tabelul nou creat poate fi exportat n diferite formate (evident i Excel) pentru prelucrri ulterioare.

Crearea criteriilor de selecie Condiia de selecie trebuie s in cont de tipul cmpului. Pentru cmp de tip text

irul cutat trebuie ncadrat ntre ghilimele. n aceast csu de selecie rezultatul funciei trebuie s fie de tip logic.

Ex: 1. dorim s selectm persoanele din mediul urban. n cadul criteriului se va introduce u. Nu ine cont de tipul literei mari sau mici (case sensitive ). 2. dorim s vizualizm toate persoanele a cror nume ncepe cu P. Vom scrie la criteriu: Like "p*. Semnul special * indic ncrcarea cu oricare alte caractere (wildcards - metacaractere). 3. dorim s afim persoanele care au fost tratate cu nizoral: InStr([Medicatie],nizoral")>"0 (funcie special). 4

Exist o bibliotec bogat de funcii pentru lucru cu tipul de dat text. Informaii referitoare la aceste funcii se gsesc i n documentaia electronic ce se instaleaz implicit odat cu programul Access. nelegerea nu prezint probleme iar utilizarea este facilitat de exemplele existente n Help.

Iat o parte din acest set de funcii utile pentru coloane tip text: Format InStr InStrRev LCase Left Len LTrim Mid Replace Right RTrim Space StrComp StrConv String StrReverse Trim UCase

Observaii n crearea unei expresii numele cmpului este ncadrat ntre paranteze

ptrate. Ex.: [IMC]=[Geutatea]/[Inaltimea]^2 Data calendaristic introdus explicit se ncadreaz folosind caracterul

diez #. Ex.: D1=#07/21/2001# Textul este ncadrat ntre ghilimele.

5

Operatori aritmetici

Operatori utilizai n comparaii: =, >, >=, = Value2

= Returns True if the first value is equal to the second value. Value1 = Value2

Returns True if the first value is not equal to the second value. Value1 Value2

Operator Purpose Example

+ Sum two numbers. [Subtotal]+[SalesTax]

- Find the difference between two numbers or indicate the negative value of a number.

[Price]-[Discount]

* Multiply two numbers. [Quantity]*[Price]

/ Divide the first number by the second number. [Total]/[ItemCount]

\ Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer.

[Registered]\[Rooms]

Mod Divide the first number by the second number, and then return only the remainder.

[Registered] Mod [Rooms]

^ Raise a number to the power of an exponent. Number ^ Exponent

6

Operator Purpose Example

And Returns True when Expr1 and Expr2 are true. Expr1 And Expr2

Or Returns True when either Expr1 or Expr2 is true. Expr1 Or Expr2

Eqv Returns True when both Expr1 and Expr2 are true, or when both Expr1 and Expr2 are false.

Expr1 Eqv Expr2

Not Returns True when Expr is not true. Not Expr

Xor Returns True when either Expr1 is true or Expr2 is true, but not both.

Expr1 Xor Expr2

Operatori logici

Operator Purpose Example

& Combines two strings to form one string. string1 & string2

+ Combines two strings to form one string and propagates null values (if one value is Null, the entire expression evaluates to Null).

string1 + string2

Operatori pentru concatenare

7

Operator Purpose Example

Is Null or Is Not Null Determines whether a value is Null or Not Null.

Field1 Is Not Null

Like "pattern" Matches string values by using the wildcard operators ? and *.

Field1 Like "instruct*"

Between val1 And val2 Determines whether a numeric or date value is found within a range.

Field1 Between 1 And 10 - OR - Field1 Between #7/17/2009# And #12/31/2009#

In(val1,val2...) Determines whether a value is found within a set of values.

Field1 In("red","green","blue") - OR - Field1 In(1,5,7,9)

Operatori speciali

8

9

STRING FUNCTIONS InStr([start, ]string1, string2[, compare]) Returns the position of the first occurrence of one string within another. Start - Optional. Numeric expression that sets the starting position for each

search. The first character position is position 1. If this argument is omitted, search starts at the first character position.

If the compare argument is specified, a start argument is required. String1 - Required. String expression being searched. String2 - Required. String expression searched for. Compare - Optional. Numeric value indicating the kind of comparison to use

when evaluating substrings. See Settings section for values. If this argument is omitted, a binary comparison is performed.

If this argument is specified, a start argument is required. Examples: MyPos = InStr (Ana Maria", Ma") Returns 5. MyPos = InStr (Ana Maria", Mi") Returns 0.

10 10

LEFT ( character_expression , integer_expression ) Returns the left part of a character string with the specified number of characters.

RIGHT( character_expression , integer_expression ) Returns the right part of a character string with the specified number of characters.

Mid(string, start [, length ] ) Returns a String containing a specified number of characters from a string.

Len(string) Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Extragerea de informaii dintr-un tabel

Avem n cadrul bazei de date (numit PROIECT1.accdb) un tabelul MG ce reprezint datele de la sesiunea de admitere. Ne intereseaz s gasim toate persoanele (numele acestora sau identificarea cod numeric, cod caracter eventual CNP) ce au luat nota 10 la testul gril de la examenul de admitere (cmp numit NOTA TEZA).

Exist mai multe metode de a rspunde la aceast ntrebare. Metoda 1 - filtre Deschidem baza de date, apoi tabelul MG , alegem tipul de prezentare (vizualizare)

Datasheet view. Aceast form este asemntoare prezentrii Excel. n capul de tabel avem posibilitatea de definire a filtrelor. Vom bifa pentru coloana NOTA TEZA doar valoarea 10.

11

Exemplul 1

Metoda 2 interogri SQL Deschidem baza de date, apoi tabelul MG. Putem crea o interogare (query) prin care s

afim numele i eventual notele (Bac., Lic.) persoanelor ce au obinut 10 la test. Vom alege din meniu Create apoi Query Design. Vom aduga tabelul MG (add MG) ,

vom selecta apoi cmpurile: COD_NR, COD_CAR, CNP (necesare crerii interogrii). Sub NOTA TEZA n rndul criteria introducem 10. Practic am selectat pe cei cu nota 10.

Pentru crearea tabelului interogrii lansm n execuie deci RUN. Aplicm Run pentru

afiarea rspunsului

Tabelul final obinut ca urmare a interogrii. Acesta poate fi exportat

n format Excel de exemplu

12

13

Sintaxa SQL SELECT < FILDS NAME> FROM < TABLE> WHERE ORDER BY [ASC/DESC]; Exemple: SELECT MG.COD_NR, MG.COD_CAR, MG.CNP, MG.[NOTA TEZA] FROM MG WHERE MG.[NOTA TEZA]=10; To select all fields: SELECT * FROM MG WHERE MG.[NOTA TEZA]=10 ORDER BY MG.[COD_CAR] DESC;

Exemplul 2 criterii de selecie pentru interogri

Din acelai tabel ne propunem s gsim toi candidaii care au ales drept disciplin de examen Chimia Organic i au luat note la tez mai mari ca 9.75.

Vom crea un nou filtru aa cum este prezentat mai jos.

Condiiile impuse prin interogare

Tabelul final obinut dup aplicarea RUN pentru afiarea

rspunsului

Este bifat afiarea cmpului n interogarea creat

14

Comanda SQL: SELECT MG.COD_NR, MG.COD_CAR, MG.CNP, MG.DISCIPLINA_EXAMEN, MG.[NOTA TEZA] FROM MG WHERE (((MG.DISCIPLINA_EXAMEN)="CHIMIE-ORGANICA") AND ((MG.[NOTA TEZA])>9.75));

Ne intereseaz candidaii care au media final ntre 9 i 9.50 i a cror cod caracter este A, C sau F.

Am folosit operatorul special IN() . Nu conteaz tipul de scriere, cu

litere mici sau mari.

Operator special Between (n domeniu inclusiv extremele )

Putem astfel crea diferite filtre asupra tabelului ce au ca efect selecia nregistrrileor dorite.

Totui exist ntrebri ce necesit numrare de cazuri. Nu ne intereseaz efectiv persoanele care ndeplinesc anumite condiii ci doar numrul de persoane ce au obinut nota 10 la examen sau au IMC-ul mai mare ca 30 sau au numrul de credite mai mic cu 20 fa de maxim etc. Ar putea s ne intereseze i indicatorii statistici de localizare sau mprtiere pentru diferite selecii din baza de date, de exemplu media seriei A de studeni la un anumit obiect comparativ cu media i de ce nu i dispersia studenilor din seria C .

Exemplul 3 criterii de selecie cu funcii /operatori speciali

15

COMANDA SQL: SELECT MG.COD_NR, MG.COD_CAR, MG.CNP, MG.DISCIPLINA_EXAMEN, MG.[NOTA TEZA] FROM MG WHERE (((MG.COD_CAR) In ("A","c","f")) AND ((MG.[NOTA TEZA]) Between 9 And 9.5));

Exemplul 4 calculul indicatorilor statistici cu ajutorul interogrilor

Ne intereseaz numrul de candidaii, media, minimul, maximul la MED_BAC pentru gruparea candidailor dup etnie.

Este necesar s avem aceast condiie de grupare, n cazul nostru etnia deja existent n tabel, cmp numit ETNIE. Metoda propus nu funcioneaz dect dac condiia de grupare este cuprins ntr-un cmp din tabel acest cmp poate fi i calculat.

Putem complica ntrebarea. Iat exemplul urmtor: Ci candidai au obinut note peste 9 , ci au note ntre 8 i 9 ... etc.? Trebuie mai nti s crem o nou coloan n tabel ce s conin aceast informaie deci sa presupunem c vom codifica cu 1 valorile peste 9 .....etc. celelalte valori.

Revenim i rspundem la ntrebarea cu indicatorii grupai dup etnie. Metoda de creare a interogrii: 1 Se lanseaz Create + Query Design 2 Se selecteaz prima coloan de interes ce cuprinde criteriul de grupare a datelor,

pentru cazul analizat este cmpul ETNIE. 3 Se selecteaz (practic se adaug) n definirea interogrii cmpul MED_BAC de 4 ori

deoarece dorim s calculm 4 indicatori: medie, min, max i numr de cazuri. 4 Se activeaz butonul TOTALS din meniul Query Tools + Design , astfel va apare un

rnd nou n definirea interogrii, numit evident Total (se poate i din meniul contextual). 16

5 Se definesc opiunile pentru fiecare cmp n parte.

Definirea opiunilor. Primul cmp ETNIE va trebui s reprezinte gruparea datelor deci vom alege din lista

derulant din rndul Total , Group by. Pentru celelalte cmpuri vom alege funcia dorit deci: Avg, Min, Max, Count. De

asemenea pentru a defini capul de tabel vom nsera numele funciei nainte de numele cmpului n coloana Field. Acestea se vor despri folosind caracterul special :.

Ex.: pentru calculul mediei vom introduce ca etichet a coloanei media: MED_BAC. media va fi eticheta ce va apare n capul de coloan iar MED_BAC este cmpul de

calcul al mediei deci al funciai Avg 17

COMANDA SQL: SELECT MG.ETNIE, Avg(MG.MED_BAC) AS Media, Min(MG.MED_BAC) AS [Min], Max(MG.MED_BAC) AS [Max], Count(MG.MED_BAC) AS [Count] FROM MG GROUP BY MG.ETNIE; Iat rezultatul obinut.

Capul de coloan definit media: MED_BAC

Cmpul criteriu de grupare

Tabel cu valorile calculate conform funciilor activate.

18

Revenim la ntrebarea: Cte persoane au nota peste 9.5 ? Vom modifica interogarea precedent adugnd cmpul MED_BAC pentru a impune

condiia >9.5 dar vom dezactiva gruparea pentru a obine valoarea dorit.

Exemplul 5 contorizare simpl cu o singur condiie

Am dezactivat gruparea i afiarea

Am introdus un cmp nou:MED_BAC , pentru criteriu >9.5, dar gruparea i

afiarea au fost dezactivate

Rspunsul cutat este 167 persoane au media peste 9.5.

Putem activa pentru grupare ETNIE dac dorim.

19

SELECT Avg(MG.MED_BAC) AS media, Min(MG.MED_BAC) AS [min], Max(MG.MED_BAC) AS [max], Count(MG.med_bac) AS [count] FROM MG HAVING (((MG.MED_BAC)>9.5)); Having este similar cu where. Rezultat final obinut din

interogare

Exemplul 6 interogare folosit pentru calculul unei noi coloane

Dorim s calculm o anumit valoare pentru fiecare nregistrare n parte. De exemplu dorim s calculm media final pentru fiecare candidat n parte, sau dorim pentru fiecare pacient s calculm IMC-ul (Indicele de Mas Corporal) .

Calculul mediei exemplu calculul mediei notelor MED_BAC, MED_LIC, NOTA TEZA Vom lansa crearea unei noi interogri folosim butonul Query Design Alegem cmpurile de interes evident cel puin cele 3 cmpuri ce particip la calculul

mediei. Vom crea o coloan nou n cadrul interogrii numit Media2. n rndul Field a

acestei coloane se introduce numele de afiare ct i formula de calcul astfel: Media2: ([med_bac]+[nota teza]+[med_lic])/3

Cmpul nou introdus: numele i formula de calcul

20

21

SELECT MG.MED_BAC, MG.MED_LIC, MG.[NOTA TEZA], ([med_bac]+[nota teza] + [med_lic]) /3 AS Media2 FROM MG;

Rezultatul obinut prin lansarea interogrii

Exemplul 7 interogare folosit n transformarea unei valori numerice n valori categoriale

Vom crea o nou coloan printr-o interogare. Noua coloan va fi practic transformarea n calificative (de tip A, B, C) a notei finale obinute.

Vom folosi funcia IIF : IIf(expr, truepart, falsepart) Avem posibilitatea s crem o expresie folosind generatorul Expression Builder.

Se activeaz prin click dreapta pe Field i alegerea opiunii Build... (n forma de proiectare (Design) a interogrii).

Se va introduce :

Calificativ: IIf([media finala]>=8, "A", IIf([media finala]>= 6, "B, "C"))

22

23

Cmpul final creat, cmp de tip text ce reprezint calificativul n sistemul cu doar 3 categorii: A,B,C .

Pentru a fi riguroi se poate prefixa numele coloanei cu numele tabelului de interes: [MG]![media finala] sau n forma nou [MG].[media finala] n loc de doar [media finala]

SELECT MG.NR, MG.COD_NR, MG.COD_CAR, MG.[MEDIA FINALA], IIf([media finala]>=8,"A",IIf([media finala]>=6,"B","C")) AS Calificativ FROM MG; Asemntor putem folosi n definirea bazei de date un cmp calculat.

Actualizara valorilor dintr-un tabel

n situaia n care am calculat valorile unui anumit cmp prin interogare, aceste valori nu au fost salvate n tabelul iniial. Acestea exist temporar n memorie ca urmare a lansrii n execuie a interogrii. Dac dorim s salvm valorile n tabelul iniial (tabel surs) va trebui s folosim o interogare de actualizare (update query).

Pentru aceasta se va crea mai nti o interogare de selecie prin care se vor defini cmpurile necesare de filtrare a datelor dar i cmpul sau cmpurile de actualizare. Se va lansa interogarea (Run) i se va verifica corectitudinea seleciei.

n etapa urmtoare se va transforma interogarea n tipul actualizare prin modul de vizualizare Design i alegerea Query Update. Va apare un nou rnd numit Actualizare cu (Update to) n care vom introduce formula de calcul sau n general expresia dorit.

Interogare de tip Select pentru filtrarea datelor

Cmp folosit pentru actualizarea datelor

24

Interogare de tip Update pentru actualizarea datelor

Formula de calcul de actualizare

Modul de lucru Design ce permite definirea/modificarea tipului de interogare

Pictograma indic schimbarea n interogare de tip aciune (Action Query)

25

SQL: UPDATE MG SET MG.Media2 = ([med_bac]+[nota teza]+[med_lic])/3 WHERE (((MG.DISCIPLINA_EXAMEN)="Chimie-Organica"));

Mesaj de confirmare a modificrilor ce urmeaz a fi facute. Atenie dac setrile de securitate nu permit actualizarea tabelei vei primi n bara de stare un mesaj de avertizare.

Actualizarea datelor prin lansare. S-a folosit pentru exemplu tabelul MG

Au fost actualizate doar nregistrrile ce au ales Chimie-Organic disciplina de examinare

MESAJ DE AVERTIZARE: This action or event has been blocked by Disabled Mode.

26

Stop Disabled Mode from blocking a query By default, if you open a database that you have not chosen to trust or that does not

reside in a trusted location, Access blocks all action queries from running. If you try to run an action query and it seems like nothing happens, check the Access

status bar for the following message: This action or event has been blocked by Disabled Mode. When you see that message, take the following steps: Enable blocked content On the Message Bar, click Options. The Microsoft Office Security Options dialog box appears. Click Enable this content, and then click OK. Run your query again. If you don't see the Message Bar Click the Database Tools tab, and in the Show/Hide group, click Message Bar. For more information about Disabled mode and Access security, see the article Secure

an Access 2007 database.

27

tergerea datelor dintr-un tabel

Sunt situaii cnd dorim s renunm la nregistrri sau doar dorim s tergem informaia din anumite cmpuri pentru anumite nregistrri.

Dac este necesar o tergere n ntregime a unei/unor nregistrri atunci se va folosi

Interogarea de tergere. Dac se dorete doar tergerea unor informaii gen celule din tabel atunci se folosete

Interogarea prin actualizare tocmai prezentat anterior. n cmpurile destinaie pentru tergere se va introduce irul de lungime 0 pentru tipul text () respectiv valoarea NULL pentru cmpul numeric.

tergerea n ntregime a anumitor nregistrri. Se creeaz asemntor metodei de actualizare mai nti o interogare de selecie, apoi

se verific , n final se transform n interogare de tergere (Delete Query). Dup lansarea interogrii de tergere nu se mai pot recupera informaiile.

28

Comanda SQL: DELETE MG.ETNIE FROM MG WHERE (((MG.ETNIE)"ROMN"));

1-Crearea interogrii iniiale de tip Select

3-Transformarea interogrii n tipul Delete

2-Verificarea seleciei

4-Observarea existenei rndului Delete confirmare a aciunii interogrii

5-Lansarea n execuie - Run 29

Exemple de lucru pentru studeni

Se va lucra n Access importnd fiierele cu date medicale de tip Excel-2007.

1. Se va importa fiierul Ex1.xlsx n Access 2007, se va inspecta structura i se vor verifica tipurile de cmpuri rezultate de la import. 2. Se vor crea interogri pentru selecia:

Doar a persoanelor din Iai Doar a persoanelor de sex masculin din Bacu Persoanelor cu vrsta peste 50 ani dar mai mic dect 65 ani din mediul urban Persoanelor din Iasi si Galati ce se afl n stadiul operator II.

3. Se va repeta ntrebarea a 2-a la care se vor calcula numrul de persoane, media, dispersia, i maximul vrstei pentru respectivele selecii.

4. Se va repeta ntrebarea 3 pentru dimensiunea tumoral.

5. Se va importa n Access2007 fiierul Ex2_nutritie.xlsx 6. Cte persoane sunt de sex M respectiv de sex F ? 7. Se detecteaz coloanele talie (T) i greutate (G) i se calculeaz IMC-ul. 8. Se creeaz o nou coloan din IMC de forma: subponderali, normoponderali (19-24),

supraponderali. 9. Se afl numrul de cazuri pentru fiecare grup definit la ntrebarea 8. 10. Se calculeaz media, min, max, std pentru Glicemie pe fiecare grup definit la 8.

30

31