BAZE DE DATE

1

BAZE DE DATE ACCESS (suport curs ID)

SGBD = Sistem de Gestiune a Bazelor de Date SGBD reprezinta o aplicatie complexa cu ajutorul careia se pot crea si gestiona baze de date. Ex. de SGBD-uri:

- de tip local/pentru retele mici: Microsoft Access, Microsoft Visual FoxPro - de tip client server: Microsoft SQL Server, SyBase SQL Server - de tip complex/pentru retele extinse: Oracle, Jawa

Baza de date reprezinta un ansamblu structurat de date legate functional intre ele. Datele sunt organizate in bazele de date utilizand urmatoarele modele:

- modelul relational; - modelul obiect;

SGBD Access permite crearea bazelor de date relationale.

Instrumentele (obiectele) de lucru Access 1. Tabela (Table/Tables) obiectul din Access in care se pastreaza datele; 2. Interogarea (Query/Queries) obiectul din Access care permite extragerea si afisarea

datelor din tabele; 3. Formularul (Form/Forms) obiectul din Access care functioneaza ca o interfata de lucru

intre utilizator si baza de date; prin intermediul formularului utilizatorul poate efectua operatii de introducere, modificare, actualizare sau stergere a datelor;

4. Raportul (Report/Reports) obiectul din Access care permite afisarea unor situatii centralizatoare (rapoarte);

5. Pagina de accesare web (Page/Pages) obiectul din Access care permite afisarea unor situatii centralizatoare (rapoarte), dar care pot fi accesate de la distanta, prin intermediul unui browser web (ex. Internet Explorer);

6. Macroul (Macro/Macros) obiectul din Access care permite automatizarea SGBD-ului; 7. Modulul (Module/Modules) obiectul din Access care permite obtinerea unor situatii

complexe, folosind comenzi ale limbajului VBA (Visual Basic for Application).

TABELA (TABLE/TABLES) Intr-o baza de date, datele sunt organizate in tabele. O tabela se refera la o entitate, un

document, o activitate economica etc. Ex. Clienti, Facturi, Materiale_Facturate

Intr-o baza de date organizata dupa modelul relational, in functie de specificul activitatii descrise, pot exista una sau mai multe tabele legate intre ele.

Obs. Intr-o baza de date nu pot exista mai multe tabele cu acelasi nume; fiecare tabela are un nume unic.

O tabela reprezinta o structura bidimensionala, organizata pe randuri (inregistrari, date) si coloane (campuri, coloane, atribute, caracteristici).

Elementele unei tabele Campul este numele unei coloane si care semnifica valorile (inregistrarile, datele) din acea coloana; Inregistrarea (tuplul) este un rand dintr-o tabela; trebuie sa fie identificata unic;

BAZE DE DATE

2

Domeniul de valori este dat de totalitatea valorilor dintr-un camp; Cardinalitatea reprezinta numarul de randuri dintr-un tabel; Gradul relatiei este dat de numarul de coloane dintr-un tabel; Cheia este formata dintr-un camp sau mai multe campuri si se utilizeaza pentru identificarea inregistrarilor; Asocierea reprezinta legaturile intre tabele.

La momentul definirii unui camp (Field) al tabelei trebuie sa se tina seama de urmatoarele: - stabilirea numelui campului, conform unor principii clar definite (numele unui camp nu

are voie sa contina cuvinte compuse, ci va fi scris legat sau cel mult despartit prin caracterul underline; numele unui camp trebuie sa fie scris cat mai mic dar reprezentativ, nu trebuie sa contina caracterele ., ! etc);

- cunoasterea tipului de date; - cunoasterea proprietatilor campurilor.

Crearea tabelelor Se face:

- in modul Design; - folosind sintaxe/comenzi ale limbajului SQL (prin intermediul unei interogari); - folosind sintaxe/comenzi ale limbajului VBA (prin intermediul unui modul).

Crearea tabelei in modul Design implica o structura de forma de mai jos: Ex. tabela Clienti

Field Name Data Type Description codclient Text Codul clientului denumire Text Denumirea clientului adresa Memo Adresa clientului telefon Text Telefonul clientului codfiscal Text Codul fiscal al clientului persoana juridical banca Text Banca contbanca Text Contul bancar

Obs!!! Modalitatile de creare a unei tabele in modul Design este exemplificata in manualul electronic, la capitolul special dedicat tabelelor.

Tipurile de date Access Text permite introducerea unui text (sir) de maximum 255 de caractere alfanumerice; Memo permite introducerea unui text (sir) mai mare de 255 caractere alfanumerice; Number permite introducerea valorilor numeric (intregi sau cu zecimale); Date/Time permite introducerea datelor calendaristice; Autonumber permite incrementarea automata a valorilor numerice; Currency permite introducerea valorilor numerice cu titulatura de unitati monetare; in dreptul valorii introduse va aparea simbolul unei monede; OLE Object permite introducerea unor obiecte de tip imagine (fisiere de tip imagine); Yes/No permite utilizarea valoriilor de tip bolean (Yes pentru o valoare acceptata, No pentru o valoare negate);

BAZE DE DATE

3

Hyperlink permite realizarea legaturilor cu obiecte (fisiere, baze de date, resurse URL) din afara bazei de date curenta; Lookup Wizard permite asocierea a doua campuri din tabele diferite (asocierea tabelelor).

Caracteristici ale tabelelor Camp cheie primara (principala) este campul dintr-o tabela care identifica in mod unic

o inregistrare. Ex. campul codclient din tabela Clienti Particularitati:

- nu permite inregistrari duplicate (fiecare inregistrare este unica); - nu permite inregistrari nule; - este indexat automat (permite unicitatea inregistrarilor); - este camp de referinta in asocierea a doua tabele; - o tabela poate avea cel mult un camp cheie primara.

Camp cheie secundara (externa, de legatura) este campul dintr-o tabela al carui singur scop este acela de a participa la asocierea tabelei care il contine cu o alta tabela, in care exista un camp identic, din toate punctele de vedere (nume, tip de data, proprietati, inregistrari). Ex. campul codclient din tabela Contracte CONTRACTE nrcontract datacontract valoare codclient 1028 12/10/2007 7500000 100 2075 27/11/2007 86350000 100 1388 01/02/2008 136900000 100 2011 15/03/2008 86500 102

Particularitati: - permite inregistrari duplicate; - nu permite inregistrari nule; - este un camp care participa la asocierea a doua tabele; - o tabela poate avea mai multe campuri cheie secundara.

Cheie concatenata (compusa) functioneaza ca o cheie primara pe mai multe campuri, in scopul identificarii unice a unui set de inregistrari.

CLIENTI CONTRACTE codclient denumire adresa telefon codfiscal banca contbanca

nrcontract datacontract valoare codclient

1

BAZE DE DATE

4

Ex. campurile nrfactura si codprodus din tabela Materiale_Facturate; se observa ca grupul de inregistrari 15801 1011 este unic si nu se repeta. MATERIALE FACTURATE nrfactura codprodus cantitate pret 75801 1011 750 15400 75900 1011 860 18400 75801 1012 1300 12500 75900 1012 860 21300

Asocierea tabelelor Se face in doua moduri:

1. In fereastra Relationships, din meniul Tools se realizeaza prin tehnica drag-n-drop (glisarea campurilor cheie dintr-o tabela in alta).

- se recomanda pentru o cantitate cosiderabila (mare) de date; 2. Folosind tipul de data Lookup Wizard - se recomanda pentru o cantitate neglijabila (mica) de date.

Obs!!! Modalitatile de asociere ale tabelelor sunt exemplificate in manualul electronic, la capitolul special dedicat tabelelor si relatiilor de asociere.

Tipuri de legaturi Legatura unu la unu 1 -1: unei inregistrari dintr-o tabela ii corespunde o singura

inregistrare din tabela asociata. Se realizeaza intre campuri (acelasi camp) cheie primara din tabele diferite. STUDENTI

nrleg nume initiala prenume cnp adresa localitate telefon 101 Vasile I Bogdan - - - - 102 Bradescu I Lavinia - - - -

TAXE nrleg rata1 rata2 101 200 250 102 200 250

Ex. Fiecare student (101 si 102) plateste taxa o singura data.

Legatura unu la mai multi 1 n (1 - ): unei inregistrari dintr-o tabela ii corespund mai multe inregistrari din tabela asociata. Se realizeaza intre un camp cheie primara dintr-o tabela si un camp cheie secundara dintr-o tabela asociata.

CLIENTI codclient denumire adresa telefon codfiscal banca contbanca

2113 Vasile I Bogdan - - - 2114 Bradescu I Lavinia - - -

CONTRACTE nrcontract datacontract valoare codclient 1028 12/10/2007 7500000 2113 2075 27/11/2007 86350000 2113

Ex. Clientul 2113 semneaza mai multe contracte (1028 si 2075).

1 - n

1 - 1

BAZE DE DATE

5

Legatura mai multi la mai multi m n ( - ): unei inregistrari dintr-o tabela ii corespunde 0, 1 sau mai multe inregistrari din cealalta tabela, si invers. Obs. SGBD Access permite realizare legaturilor 1-1 si 1 n.

Tabela principala este tabela care contine un camp cheie primara (ex. tabela Clienti). Tabela secundara este tabela care, pe langa un posibil camp cheie primara, are in structura si campuri cheie secundara; este tabela care contine o cheie compusa (ex. tabela Contracte, tabela Materiale_Facturate).

La crearea unei baze de date se au in vedere urmatoarele etape: 1. se realizeaza tabele principale; 2. se realizeaza tabelele secundare; 3. se realizeaza/verifica legaturile intre tabele; 4. se populeaza cu date tabelele principale; 5. se populeaza cu date tabelele secundare.

Introducerea datelor Datele se introduce in modul Datasheet; de asemenea, tabela astfel accesata permite

efectuarea unor alte operatii de manipularea a datelor existente (modificare, stergere). Datele se introduce rand cu rand, de la stanga la dreapta, cu completarea obligatorie a campurilor cheie.

Obs!!! Modalitatile de introducere a datelor in tabele sunt exemplificate in manualul electronic, la capitolul special dedicat tabelelor.

INTEROGAREA (QUERY/QUERIES) Este obiectul din Access care permite extragerea si afisarea unor situatii de iesire cu

datele din tabele. Particularitati:

- permite consultarea simultana a mai multor tabele (intre care exista o legatura); - permite stabilirea unor filter (conditii, criterii) la extragerea datelor din tabele sursa; - permite utilizarea campurilor calculate (ex. valoare: cantitate*pret) ; - permite parametrizarea interogarilor; - foloseste ca sursa de date pentru generarea formularelor sau pentru obtinerea de rapoarte.

Tipuri de interogari 1. Interogari de selectie (simple)

1a. cu campuri calculate 1b. parametrizate

2. Interogari de tip total (centralizatoare) 3. Interogari de tip table incrucisat (Crosstab) 4. Interogari de actiune

4a. care creeaza o tabela noua Query Make Table 4b. care adauga date intr-o tabela Query Append 4c. care sterge date intr-o tabela Query Delete 4d. care actualizeaza date intr-o tabela Query Update

5. Interogari de tip Union (Union Select) 6. Subinterogari

BAZE DE DATE

6

Obs. Sursa de date pentru o interogare poate fi una sau mai multe tabele, interogari sau combinatii ale acestora.

Crearea interogarilor Se face:

- in modul QBE (Query by example)/modul Design; - folosind sintaxe/comenzi ale limbajului SQL; - folosind sintaxe/comenzi ale limbajului VBA.

Interogari de selectie Crearea unei interogari de selectie in modul QBE implica completarea unei grile de

interogare, cu urmatoarea structura:

FIELD TABLE SORT SHOW CRITERIA OR

Pe randul FIELD se trec campurile care participa la interogare. Pe randul TABLE se trec tabelele sursa pentru campurile din randul Field. Pe randul SORT se specifica modalitati de sortare a datelor din campuri (sortare Ascendenta sau Descendenta). Pe randul SHOW se bifeaza o caseta de validare, ceea ce implica vizualizarea datelor din campul respectiv. Pe randul CRITERIA se stabilesc filtre (conditii) pentru extragerea datelor din tabele; daca se impune stabilirea mai multor filter acestea se vor respectate simultan. Pe randul OR se specifica filter suplimentare atunci cand o expresie conditionala foloseste operatorul Or.

Obs. Stabilirea unui filtru (conditie) implica utilizarea unei expresii care contine operatori Access, functii, constante, inregistrari, nume de campuri etc.

Crearea unei interogari de selectie in modul SQL implica parcurgerea urmatoarei sintaxe: SELECT lista_campuri

FROM lista_tabele_sursa WHERE conditii [ORDER BY] modalitati_sortare;

In clauza SELECT se trec, delimitate prin virgule, campurile care vor constitui capul de table al situatiei finale. In clauza FROM se trec tabelele sursa si modul in care acestea sunt relationate. In clauza WHERE se trec filtrele (conditiile) pentru extragerea datelor din campuri; conditiile vor fi respectate simultan, astfel ca in scrierea lor se foloseste operatorul And. In clauza optionala ORDER BY se specifica modalitati pentru sortarea datelor. Obs. O sintaxa SQL se incheie cu caracterul ;.

BAZE DE DATE

7

Elementele utilizate n definirea expresiilor folosite pentru stabilirea criteriilor i a cmpurilor calculate:

Operatori: - aritmetici: +, -, *, /, Mod (impartirea a doua numere cu returnarea restului impartirii), ^

(ridicarea la putere), \ (impartirea a doua numere cu obtinerea catului sub forma de intreg) - de comparare: =, , =, - ali operatori (asociati operatorilor de comparare): Like, In, Between, Is null, Is not null Obs. Operatorul LIKE se foloseste impreuna cu caracterele de inlocuire * si ?. - logici: And, Or, Not - de concatenare: +, & - de identificare: !, . (punct)

- identificarea obiectelor dintr-o colecie Form![Stocuri] - identificarea cmpurilor din tabele Retribuii![Salariu] - identificarea proprietilor din cadrul obiectelor Text1.FontSize= 12

Constante - de tip sir de caractere (ex. Stiinte economice) - o inregistrare de tip text, ca parte dintr-

o expresie conditionala, se evidentiaza intre caracterele ; - de tip valoare numerica (ex. 125) - o inregistrare de tip valoare numerica, ca parte dintr-o

expresie conditionala, se evidentiaza ca atare, fara a fi delimitate de vreun caracter; - de tip data calendaristica (ex. #31/12/2007#\) - o inregistrare de tip data calendaristica, ca

parte dintr-o expresie conditionala, se evidentiaza intre caracterele # #;

Identificatori: nume de obiecte (cmpuri, tabele, etc)

Funcii pentru iruri: - Funcia Left: returneaz primele n caractere ale unui ir

Left(expr_ir,n) - Funcia Right: returneaz ultimele n caractere ale unui ir

Right(expr_ir,n) - Funcia Mid: returneaz un subir dintr-un ir

Mid(expr_ir, poziie, lungime) - Funcia Ucase: transform literele mici n litere mari

Ucase(expr_ir) - Funcia Len: returneaza lungimea unui sir

Len(expr_ir) Funcii pentru data calendaristic:

- Funcia DatePart: returneaz o parte specificat a unei date DatePart(abreviere, dat)

- Funcia DateDiff: returneaz intervalul de timp ntre 2 date DateDiff(abreviere, data1, data2)

BAZE DE DATE

8

- Funcia DateAdd: returneaz rezultatul adugrii unei perioade specificate la o dat stabilit

DateAdd(abreviere, numr, data) Abrevieri: d zi

m luna yyyy anul q trimestrul

Funciile Day, Month, Year Date() : furnizeaz data curent

Interogari de tip total (centralizatoare) Sunt interogari care centralizeaza datele. Crearea unei interogari de tip total in modul QBE implica completarea unei grile de

interogare, cu urmatoarea structura:

FIELD TABLE TOTAL SORT SHOW CRITERIA OR

Pe randul FIELD se trec campurile care participa la interogare. Pe randul TABLE se trec tabelele sursa pentru campurile din randul Field. Pe randul TOTAL se trece una din optiunile disponibile (Group By, Sum, Avg etc). Pe randul SORT se specifica modalitati de sortare a datelor din campuri (sortare Ascendenta sau Descendenta). Pe randul SHOW se bifeaza o caseta de validare, ceea ce implica vizualizarea datelor din campul respectiv. Pe randul CRITERIA se stabilesc filtre (conditii) pentru extragerea datelor din tabele; daca se impune stabilirea mai multor filter acestea se vor respectate simultan. Pe randul OR se specifica filter suplimentare atunci cand o expresie conditionala foloseste operatorul Or.

Obs. Grila pentru o interogare de tip total are la baza o interogare de selectie. Randul TOTAL se insereaza in grila prin apasarea butonului cu semnul din bara de instrumente. Numarul minim de campuri care participa la o astfel de interogare este de doua, un camp pentru care se face gruparea datelor si un camp pentru care se aplica o functie agregat (Sum, Count, Min, Max, Avg).

Optiunile de pe randul Total: First() - afl valoarea unui cmp din prima nregistrare Last() - afl valoarea unui cmp din prima nregistrare Count() - numr valorile Not Null dintr-un cmp. Pentru toate valorile se folosete Count(*)

BAZE DE DATE

9

Avg() - calculeaz media valorilor implicate ntr-o grupare. Valorile Null sunt ignorate Sum() - calculeaz totalul valorile pentru un cmp implicat ntr-o grupare Group By - determin gruparea tuturor nregistrrilor pentru un cmp. Max() - determin valoarea maxim dintr-un cmp. Valorile Null sunt ignorate Min() - determin valoarea minim dintr-un cmp. Valorile Null sunt ignorate Expression - returneaz un singur rezultat pentru grupare Where - se utilizeaz la un cmp pentru selectarea nregistrrilor. Deselecteaz caseta Show Set Dev - calculeaz abaterea statistic standard a valorilor dintr-un cmp Var - determin variaia statistic a valorilor dintr-un cmp

Crearea unei interogari de selectie in modul SQL implica parcurgerea urmatoarei sintaxe: SELECT lista_campuri

FROM lista_tabele_sursa WHERE conditii (inainte de grupare) GROUP BY lista campuri HAVING conditii (dupa grupare) [ORDER BY] modalitati_sortare;

In clauza SELECT se trec, delimitate prin virgule, campurile care vor constitui capul de table al situatiei finale. In clauza FROM se trec tabelele sursa si modul in care acestea sunt relationate. In clauza WHERE se trec filtrele (conditiile) pentru extragerea datelor din campuri, inainte ca acestea sa fie grupate. In clauza GROUP BY se trec, delimitate prin virgule, campurile pentru care se face gruparea datelor; se trec acele campuri din clauza Select in a caror expresie nu apare nicio functie agregat. In clauza HAVING se trec filtrele (conditiile) pentru extragerea datelor din campuri, dupa ce acestea au fost grupate. In clauza optionala ORDER BY se specifica modalitati pentru sortarea datelor.

Interogri de tip tabel ncruciat (Crosstab) Sunt interogari de tip total care prezinta informatiile intr-un mod sintetic (tabel

incrucisat). Reprezentarea datelor este tridimensionala, pe randuri, coloane si la intersectia lor. Pentru o interogare Crosstab sunt necesare minimum trei campuri:

un cmp (sau mai multe) pentru furnizarea informaiilor necesare antetului de rnd (Row Heading);

un singur cmp pentru furnizarea informaiilor necesare antetului de coloan (Column Heading);

un cmp pentru calcularea valorilor care se plaseaz la intersecia rnd coloan (Value).

Obs. Primele doua categorii de campuri se refera la campuri grupate (Group By), iar campul valoric este un camp in a carei expresie apare o functie agregat.

Crearea unei interogari de tip total in modul QBE implica completarea unei grile de interogare, cu urmatoarea structura:

BAZE DE DATE

10

FIELD TABLE TOTAL CROSSTAB SORT CRITERIA OR

Pe randul FIELD se trec campurile care participa la interogare. Pe randul TABLE se trec tabelele sursa pentru campurile din randul Field. Pe randul TOTAL se trece una din optiunile disponibile (Group By, Sum, Avg etc). Pe randul CROSSTAB se trece una din optiunile disponibile (Row Heading, Column Heading, Value, Not Shown). Pe randul SORT se specifica modalitati de sortare a datelor din campuri (sortare Ascendenta sau Descendenta). Pe randul CRITERIA se stabilesc filtre (conditii) pentru extragerea datelor din tabele; daca se impune stabilirea mai multor filter acestea se vor respectate simultan. Pe randul OR se specifica filter suplimentare atunci cand o expresie conditionala foloseste operatorul Or.

Optiunile de pe randul Crosstab: Column Heading antet de coloana (camp grupat); Row Heading antet de rand (camp grupat); Value valoare numerica (camp cu functie agregat); (Not Shown) nu afiseaza nicio valoare.

Obs. Grila pentru o interogare de tip Crosstab are la baza o interogare de selectie. Prin alegerea optiunii Crosstab Query din meniul Query se insereaza in grila randurile TOTAL si CROSSTAB si dispare randul SHOW.

Crearea unei interogari de selectie in modul SQL implica parcurgerea urmatoarei sintaxe: TRANSFORM camp_agregat

SELECT lista_campuri_grupate FROM lista_tabele_sursa WHERE conditii (inainte de grupare) GROUP BY lista campuri HAVING conditii (dupa grupare) [ORDER BY] modalitati_sortare

PIVOT camp_grupat;

In clauza TRANSFORM se trece campul in a carui expresie apare o functie agregat (campul nmeric). In blocul de comenzi SELECT (Select, From, Where, Group By, Having, Order By) se trec acele campuri care vor returna datele grupate ca antet de rand. In clauza PIVOT se trece campul care va returna datele grupate ca antet de coloana.

BAZE DE DATE

11

Interogri de aciune

a. Make Table Query: creaz o tabel pe baza datelor preluate din una sau mai multe tabele. Scop: optimizarea crerii rapoartelor i formularelor (de regul pentru obinerea unui raport nu

sunt necesare toate cmpurile i toate nregistrrile; n acest caz se creaz un tabel din mai multe tabele numai cu datele necesare obinerii raportului);

optimizarea prelucrrilor (se recomand folosirea unui singur tabel pentru obinerea anumitor informaii evitndu-se n acest fel interogrile multitabel);

crearea copiilor de siguran.

Crearea interogrii QMT Se creaz o interogare de selecie, inclusiv criteriile de selecie a interogrilor. Se

transform interogarea de selecie ntr-o interogare Make Table Query (din meniul Query se alege opiunea Make Table Query). Se specifica numele tabelei care se creaz. n tabela care se creaz sunt permise cmpurile calculate.

Obs. n tabela care se creaz nu este motenit cheia primar, de aceea ea trebuie stabilit ulterior. De asemenea, trebuie revzute proprietile cmpurile tabelei.

b. Append Query: adaug nregistrri ntr-o tabel preluate din una sau mai multe tabele.

Crearea interogrii QA Se creaz o interogare de selecie, se aduc pe gril cmpurile care se adaug inclusiv

criteriile de selecie. Se transform interogarea de selecie ntr-o interogare Append Query (din meniul Query se alege opiunea Append Query). Se afieaz pe ecran o caset Append To n care trebuie specificat numele tabelei destinaie. Pe grila de interogare apare rndul Append To care cuprinde cmpurile tabelei destinaiei.

c. Delete Query: terge datele dintr-o tabel.

Obs. Dac se terg nregistrrile dintr-o tabel aflat n relaia 1- cu alte tabele, interogarea terge nregistarrile din parte 1 iar dac s-a optat pentru tergerea n cascad se terg nregistrrile i din tabela asociat.

Crearea interogrii QD Se creaz interogarea de selecie cu toate cmpurile tabelei i se precizeaz criteriile de

selecie (tergere). Se transform interogarea de selecie ntr-o interogare Delete Query (din meniul Query se alege opiunea Delete Query). Pe grila de interogare apare rndul Delete i dispar rndurile Sort i Show. Optiunile disponibile de pe randul Delete sunt Where si From.

d. Update Query: actualizeaz datele dintr-o tabel

Crearea interogrii QU Se creaz o interogare de selecie, se alege tabela n care se actualizeaz datele. Se aduc

pe gril doar cmpurile care se actualizeaz i cmpurile pentru care se stabilesc criterii de

BAZE DE DATE

12

selecie. Se transform interogarea de selecie ntr-o interogare Update Query (din meniul Query se alege opiunea Update Query). n grila de interogare apare rndul Update To i dispar rndurile Sort i Show. Pe randul Update To se trece formula pe baza careia datele vor fi actualizate.

BAZE DE DATE

13

STUDIU DE CAZ

Se va consulta baza de date Imobile cu urmatoarele tabele: Proprietari(nrpropr, nume, prenume, telefon, adresa) Imobile (nrimobil, tipimobil, camere, localitate, zona, sector, pretvz, pretc, datavz, datac, nrpropr, nragent) Agenti(nragent, nume, prenume, telefon, adresa)

Cerinta 1 Se doreste afisarea unei liste cu datele despre agentul care are in vanzare imobilul cu numarul 11.

QBE FIELD nragent nume prenume nrimobil TABLE Agenti Agenti Agenti Imobile SORT SHOW V

V

V

V

CRITERIA 11 OR

SQL SELECT Agenti.nragent, nume, prenume, nrimobil FROM Agenti INNER JOIN Imobile ON Agenti.nragent=Imobile.nragent WHERE nrimobil=11;

Observatie: In clauza SELECT scrierea Agenti.nragent se refera la utilizarea (afisarea) datelor din campul nragent, din tabela Agenti.

Datele rezultate sunt

Cerinta 2 Se doreste afisarea unei liste cu imobilele vandute in anul 2009.

QBE FIELD nrimobil tipimobil camere datavz TABLE Imobile Imobile Imobile Imobile SORT SHOW V

V

V

V

CRITERIA Between #01/01/2009# And #31/12/2009#

OR

BAZE DE DATE

14

SQL SELECT nrimobil, tipimobil, camere, datavz FROM Imobile WHERE datavz Between #01/01/2009# And #31/12/2009#;

Datele rezultate sunt

Cerinta 3 Se doreste afisarea unei liste cu apartamentele din Bucuresti, sector 1, 2 si 3, zona 1 si 2, vandute in anul 2009.

QBE FIELD nrimobil tipimobil localitate sector zona datavz FROM Imobile Imobile Imobile Imobile Imobile Imobile SORT SHOW V V V V V V CRITERIA apartament Bucuresti 1 OR 2

OR 3 1 OR 2 Between

#01/01/2009# And #31/12/2009#

OR

SQL SELECT nrimobil, tipimobil, datavz, localitate, sector, zona FROM Imobile WHERE (tipimobil=apartament) And (datavz Between #01/01/2009# And #31/12/2009#) And (localitate= Bucuresti) And (sector = 1 Or sector=2 Or sector=3) And (zona = 1 Or zona = 2);

Datele rezultate sunt

BAZE DE DATE

15

Cerinta 4 Se doreste afisarea unei liste care sa cuprinda numarul de imobil, tipul imobilului, localitatea, numele si prenume proprietarului, pentru imobilele situate in alte localitati decat Bucuresti si a caror proprietari nu au telefon.

QBE FIELD nrimobil tipimobil localitate nume prenume telefon TABLE Imobile Imobile Imobile Proprietari Proprietari Proprietari SORT SHOW V V V V V CRITERIA Bucuresti IS NULL OR

SQL SELECT nrimobil, tipimobil, localitate, nume, prenume FROM Imobile INNER JOIN Proprietari ON Imobile.nrpropr = Proprietari.nrpropr WHERE (localitate Bucuresti) And (telefon IS NULL);

Datele rezultate sunt

Cerinta 5 Se doreste afisarea unei liste care sa cuprinda numarul si tipul imobilului aflat in vanzarea unui agent oarecare (precizat la momentul executiei).

QBE FIELD nrimobil tipimobil nragent TABLE Imobile Imobile Imobile SORT SHOW V V V CRITERIA [precizati numarul agentului] OR

SQL SELECT nrimobil, tipimobil, nragent FROM Imobile WHERE nragent = [precizati numarul agentului];

BAZE DE DATE

16

Datele rezultate sunt

Obs. Pentru exemplificare, s-a avut in vedere agentul cu numarul 110.

Cerinta 6 Se doreste afisarea unei liste cu imobilele vandute intr-o perioada oarecare (precizata la momentul executiei).

QBE FIELD nrimobil tipimobil Datavz TABLE Imobile Imobile Imobile SORT SHOW V V V CRITERIA Between [data1] and [data 2] OR

SQL SELECT nrimobil, tipimobil, datavz FROM imobile WHERE datavz Between [data1] and [data2];

Datele rezultate sunt

Obs. Pentru exemplificare, s-a avut in vedere perioada 1 ianuarie 2009 31 martie 2009

Cerinta 7 Se doreste afisarea unei liste cu castigul obtinut de fiecare agent din fiecare tranzactie.

QBE FIELD nragent nume prenume nrimobil tipimobil castig:

[pretvz]-[pretc]

datavz

TABLE Agenti Agenti Agenti Imobile Imobile Imobile SORT SHOW V V V V V V CRITERIA IS NOT

NULL OR

BAZE DE DATE

17

SQL SELECT Agenti.nragent, nume, prenume, nrimobil, tipimobil, ([pretvz]-[pretc]) AS castig FROM Imobile INNER JOIN Agenti ON Imobile.nragent=Agenti.nragent WHERE datavz IS NOT NULL;

Datele rezultate sunt

Cerinta 8 Se doreste afisarea unei liste cu numarul si tipul imobilului, si anul in care acestea au fost cumparate.

QBE FIELD nrimobil tipimobil anul: Year([datac]) TABLE Imobile Imobile Imobile SORT SHOW V V V CRITERIA OR

SQL SELECT nrimobil, tipimobil, Year([datac]) AS anul FROM Imobile;

BAZE DE DATE

18

Datele rezultate sunt

Cerinta 9 Sa se afiseze o lista cu profitul obtinut de fiecare agent in anul 2009

QBE FIELD nragent profit:SUM([pretvz]-[pretc]) anul: Year([datavz]) TABLE TOTAL GROUP BY EXPRESSION GROUP BY SORT SHOW V V V CRITERIA 2009 OR

SQL SELECT nragent, SUM([pretvz]-[pretc]) AS profit, year([datavz]) AS anul FROM Imobile GROUP BY nragent, year([datavz]) HAVING Year([datavz]) = 2009;

Datele rezultate sunt

BAZE DE DATE

19

Cerinta 10 Se doreste obtinerea unei liste cu profitul pe fiecare agent si an calendaristic.

QBE FIELD nragent profit:SUM([pretvz]-[pretc]) anul:Year([datavz]) datavz TABLE Imobile Imobile Imobile TOTAL GROUP BY EXPRESSION GROUP BY WHERE SORT SHOW V V V CRITERIA Is Not

Null OR

SQL SELECT nragent, SUM([pretvz]-[pretc]) AS profit, Year([datavz]) AS anul FROM Imobile WHERE datavz IS NOT NULL GROUP BY nragent, Year([datavz]);

Datele rezultate sunt

Cerinta 11 Se doreste afisarea unei liste cu numarul de imobile vandute pe fiecare agent si an calendaristic.

QBE FIELD imobile_vandut: nrimobil nragent anul: Year([datavz]) datavz TABLE Imobile Imobile Imobile Imobile TOTAL COUNT GROUP BY GROUP BY WHERE SHOW V V V CRITERIA OR Is Not Null

SQL SELECT COUNT (nrimobil) AS immobile_vandute, nragent, Year([datavz]) AS anul FROM Imobile WHERE datavz IS NOT NULL GROUP BY nragent, Year([datavz]);

BAZE DE DATE

20

Datele rezultate sunt

Cerinta 12 Se doreste afisarea unei liste cu preturile minim, maxim si mediu pentru imobilele, pe fiecare agent. Se au in vedere preturile de cumparare si imobilele nevandute inca.

QBE FIELD nragent pret_min:

MIN(pretc) pret_max: MAX(pretc)

pret_mediu: AVG(pretc)

datavz

TABLE Imobile Imobile SORT TOTAL GROUP

BY EXPRESSION EXPRESSION EXPRESSION WHERE

SHOW V V V V

CRITERIA IS NULL OR

SQL SELECT nragent, MIN(pretc) AS pret_min, MAX(pretc) AS pret_max, AVG(pretc) AS pret_mediu FROM Imobile WHERE datavz IS NULL GROUP BY nragent;

Datele rezultate sunt

Cerinta 13 Se doreste afisarea unei liste cu numarul de imobile vandute pe fiecare agent si trimestru, din 2009. Datele vor fi reprezentate intr-o forma sintetica.(interogare CROSSTAB).

BAZE DE DATE

21

QBE FIELD Imobile:

COUNT([nrimobil]) nragent Trimestru:

DatePart(q, [datavz]) datavz

TABLE Imobile Imobile TOTAL EXPRESSION GROUP BY GROUP BY WHERE CROSSTAB VALUE ROW

HEADING COLUMN HEADING

(NOT SHOWN)

SORT CRITERIA Between

#01/01/2009# And #31/12/2009#

OR

SQL TRANSFORM COUNT([nrimobil]) As Imobile SELECT nragent FROM Imobile WHERE datavz Between #01/01/2009# And #31/12/2009# GROUP BY nragent PIVOT DatePart(q,[datavz]);

Datele rezultate sunt

Cerinta 14 Sa se actualizeze tabela Imobile in sensul reducerii preturilor de vanzare cu 5%, pentru imobilele nevandute.

QBE FIELD pretvz datavz TABLE Imobile Imobile UPDATE TO [pretvz]*0.95 CRITERIA IS NULL

SQL UPDATE Imobile SET pretvz = [pretvz]*0.95 WHERE datavz IS NULL;

Obs. Pentru vizualizarea datelor actualizate se va consulta tabela Imobile.