Exercitii Access Rezolvate

1

2

GESTIUNE PRODUSE FINITE .............................. .................................................... ............................................. ............................................. ................................ .......... 2 1.1 STRUCTURA BAZEI DE DATE .......................................... ................................................................. ............................................. ........................................... .....................2 1.2 SCHEMA BAZEI DE DATE ........................................... ................................................................. ............................................ ............................................. .......................... ...2 ESTRICŢII .......................................... 1.3 R ESTRICŢII ................................................................ ............................................ ............................................. ............................................. ......................... ...3 1.3.1 Restrictii la nivel de atribut.......................................... ................................................................. ............................................. ....................................... .................3 1.3.2 Restrictii la nivel de inregistrare ............................................. .................................................................... ............................................. ............................ ......4 1.4 POPULAREA CU DATE ............................................ ................................................................... ............................................. ............................................. ............................. ......4 1.5 I NTEROGĂRI ............................................ .................................................................. ............................................ ............................................. ............................................ .....................5 1.5.1 Filtrarea şi ordonarea directă a datelor dintr -o tabelă ........................................... ................................................................ .....................5 1.5.2 Actualizarea directă a datelor ................... datelor ......................................... ............................................ ............................................. ..................................... ..............5 1.5.3 Crearea de interogări cu instrumente de tip Wizard ............................................. .................................................................. .....................5 1.5.3.1 Crearea unei interogări cu Simple Qu ery Wizard ............................................ ............................................................. .................5 1.5.3.2 Crearea unei interogări cu Crosstab Query Wizard ........................................... ......................................................... ..............6 1.5.4 Definirea interogărilor prin Query Design ........................................... .................................................................. ..................................... ..............7 1.5.4.1 Interogări de selecţie ........................................... .................................................................. ............................................. ....................................... .................7 1.5.4.2 Interogări de adăugare ........................................... .................................................................. ............................................. .................................... ..............8 1.5.4.3 Interogări de modificare ......................................... ............................................................... ............................................. ..................................... ..............9 1.5.4.4 Interogări de ştergere .......................................... ................................................................. ............................................. ....................................... .................9 1.5.4.5 Interogare folosind folosind două tabele ......................................... ............................................................... ............................................. .......................... ...9 1.5.5 Interogări SQL .......................................... ................................................................ ............................................ ............................................. ................................... ............10 1.5.5.1 Primele argumente argumente ale interogărilor interogărilor SQL ............................................. ................................................................... .......................... ....10 1.5.5.2 Utilizarea principalilor operatori.......................................... ................................................................. .......................................... ...................11 1.5.5.3 Funcţii de agregare agregare ............................................. .................................................................... ............................................. ..................................... ...............12 1.5.5.4 Gruparea înregistrărilor ............................................ ................................................................... ............................................. .............................. ........15 APOARTE .......................................... 1.6 R APOARTE ................................................................ ............................................ ............................................. ............................................. ........................15 1.6.1 Asistentul de rapoarte .......................................... ................................................................ ............................................ ............................................. ......................... 15 1.6.2 Raport simplu din două tabele ............................................ ................................................................... ............................................. .............................. ........17 1.6.3 Raport cu gruparea înregistrărilor şi subtotaluri ............................................ .................................................................. .......................... ....18 1.7 FORMULARE ........................................... ................................................................. ............................................ ............................................. .......................................... ...................19

EVIDENŢĂ STUDENŢI .......................................... ................................................................ ............................................ ............................................. ............................... ........23 2.1 Crearea tabelelor şi definirea restricţiilor .................... restricţiilor .......................................... ............................................. .......................................... ...................23 2.1.1 Crearea tabelelor şi declararea valorilor implicite ............................................ ................................................................... .......................23 2.1.2 Reguli de validare ........................................................... ................................................................................. ............................................. ................................... ............23 2.1.2.1 La nivel de atribut ........................................ .............................................................. ............................................ ............................................. .........................23 2.1.2.2 La nivel de înregistrare ................................................... .......................................................................... ............................................. .......................... ....23 2.1.3 Declararea restricţiilor referenţiale ............................................ ................................................................... ............................................. ........................24 2.2 Editarea tabelelor şi navigarea prin înregistrări ............................................. ................................................................... .............................. ........24 2.3 Obţinerea de informaţii din baza de date ........................................... .................................................................. .......................................... ...................25 2.3.1 Setul nr. 1 de întrebări ......................................... ............................................................... ............................................ ............................................. .........................25 2.3.2 Setul nr. 2 de întrebări ......................................... ............................................................... ............................................ ............................................. .........................27 2.3.3 Setul nr. 3 de întrebări ......................................... ............................................................... ............................................ ............................................. .........................28 2.3.4 Setul nr. 4 de întrebări ......................................... ............................................................... ............................................ ............................................. .........................28 2.3.5 Setul nr. 5 de întrebări ......................................... ............................................................... ............................................ ............................................. .........................30 2.3.6 Setul nr. 6 de întrebări ......................................... ............................................................... ............................................ ............................................. .........................31 2.3.7 Setul nr. 7 de întrebări ......................................... ............................................................... ............................................ ............................................. .........................32 3 MODELE LUCRĂRI PRACTICE PRACTICE .......................................... ................................................................. ............................................. ..................................... ...............33 3.1 Lucrarea practică nr. 1 .......................................... ................................................................ ............................................ ............................................. ........................... ....33 3.2 Lucrarea practică nr. 2 .......................................... ................................................................ ............................................ ............................................. ........................... ....34 3.3 Lucrarea practică nr. 3 .......................................... ................................................................ ............................................ ............................................. ........................... ....36 3.4 Lucrarea practică nr. 4 .......................................... ................................................................ ............................................ ............................................. ........................... ....38

Gestiune produse finite

1 GESTIUNE PRODUSE FINITE TRUCTURA T RUCTURA BAZEI 1.1 S

DE DATE Denumire atribut

Tabela

Documente de intrare Linii in documente Avize de expeditie

Linii in avize

Gestiune Incasari

Produse

Clienti

Localitati

numar nota intrare data document  cod gestiune  numar nota intrare  cod produs  cantitate  numar aviz  data aviz  cod gestiune  cod client  numar aviz  cod produs  cantitate  procent tva  pret  cod gestiune  denumire gestiune  nume gestionar  numar document  data document  cod client  suma  tip de document  cod produs  denumire produs  unitate de măsură  pret  stoc  cod client  denumire client  cod localitate  sold  cod localitate  denumire localitate  denumire judet  

Tipul

Text Date/Ti me Text Text Text Number Text Date Time Text Text Text Text Number Number Number Text Text Text Text Date/Time Text Number Text Text Text Text Number Number Text Text Text Number Text Text Text

Lungime

8 Short Date 8 8 8 Single 8 Short Date 8 8 8 8 Single Byte Single 8 20 20 S Short Date 8 Single 20 8 20 3 Single Single 8 20 4 Single 8 20 20

1

Observaţii

pk

parte din pk (fk 2 - din Documente de intrare) parte din pk (fk - din Produse) pk fk - din gestiune fk - din clienţi parte din pk (fk - din Avize de expeditie) parte din pk (fk - din Produse)

pk parte din pk parte din pk (fk - din C lienţi lienţi) parte din pk pk

pk fk - din Localitati pk

Câmpurile care au conţinut numeric, dar nu sunt folosite în calcule pot fi configurate ca de tip Text, fiind mai uşor de gest ionat. Cheia primară se setează dând clic dreapta pe câmpul stabilit ca având acest rol şi alegând din meniul contextual opţiunea Primary Key. Aceeaşi opţiune poate fi aleasă din meniul Design. În cazul în care este nevoie de cheie primară compusă, se selectează primul câmp, apoi se „alunecă” spre celălalt, dacă sunt alăturate sau se ţine apăsată tasta Ctrl şi se apasă şi pe celălalt câmp. Baza de date în forma în care se pot stabili cheile primare, introdus valori implicite, reguli de validare etc.

gestiune produse produse finite - simpla.accdb

1.2 S CHEM A BAZEI DE DATE 1. Pentru a crea legăturile între tabele, se alege, din meniul Database Tools, opţiunea Relationships. Dacă este aleasă prima dată pentru respectiva bază de date, va apărea un formular de unde se pot introduce tabelele. Dacă a mai fost apelată această opţiune pentru acea bază de date, pentru a mai adăuga tabele: fie se alege din meniul Design, butonul Show Table; fie se dă clic dreapta dr eapta pe zona gri unde sunt tabelele şi se alege, din meniul contextual, aceeaşi opţiune Show Table; 1 2

pk - primary primary key – key – cheie cheie primară fk - foreign key key - cheie străină


1 GESTIUNE PRODUSE FINITE TRUCTURA T RUCTURA BAZEI 1.1 S

DE DATE Denumire atribut

Tabela

Documente de intrare Linii in documente Avize de expeditie

Linii in avize

Gestiune Incasari

Produse

Clienti

Localitati

numar nota intrare data document  cod gestiune  numar nota intrare  cod produs  cantitate  numar aviz  data aviz  cod gestiune  cod client  numar aviz  cod produs  cantitate  procent tva  pret  cod gestiune  denumire gestiune  nume gestionar  numar document  data document  cod client  suma  tip de document  cod produs  denumire produs  unitate de măsură  pret  stoc  cod client  denumire client  cod localitate  sold  cod localitate  denumire localitate  denumire judet  

Tipul

Text Date/Ti me Text Text Text Number Text Date Time Text Text Text Text Number Number Number Text Text Text Text Date/Time Text Number Text Text Text Text Number Number Text Text Text Number Text Text Text

Lungime

8 Short Date 8 8 8 Single 8 Short Date 8 8 8 8 Single Byte Single 8 20 20 S Short Date 8 Single 20 8 20 3 Single Single 8 20 4 Single 8 20 20

1

Observaţii

pk

parte din pk (fk 2 - din Documente de intrare) parte din pk (fk - din Produse) pk fk - din gestiune fk - din clienţi parte din pk (fk - din Avize de expeditie) parte din pk (fk - din Produse)

pk parte din pk parte din pk (fk - din C lienţi lienţi) parte din pk pk

pk fk - din Localitati pk

Câmpurile care au conţinut numeric, dar nu sunt folosite în calcule pot fi configurate ca de tip Text, fiind mai uşor de gest ionat. Cheia primară se setează dând clic dreapta pe câmpul stabilit ca având acest rol şi alegând din meniul contextual opţiunea Primary Key. Aceeaşi opţiune poate fi aleasă din meniul Design. În cazul în care este nevoie de cheie primară compusă, se selectează primul câmp, apoi se „alunecă” spre celălalt, dacă sunt alăturate sau se ţine apăsată tasta Ctrl şi se apasă şi pe celălalt câmp. Baza de date în forma în care se pot stabili cheile primare, introdus valori implicite, reguli de validare etc.

gestiune produse produse finite - simpla.accdb

1.2 S CHEM A BAZEI DE DATE 1. Pentru a crea legăturile între tabele, se alege, din meniul Database Tools, opţiunea Relationships. Dacă este aleasă prima dată pentru respectiva bază de date, va apărea un formular de unde se pot introduce tabelele. Dacă a mai fost apelată această opţiune pentru acea bază de date, pentru a mai adăuga tabele: fie se alege din meniul Design, butonul Show Table; fie se dă clic dreapta dr eapta pe zona gri unde sunt tabelele şi se alege, din meniul contextual, aceeaşi opţiune Show Table; 1 2

pk - primary primary key – key – cheie cheie primară fk - foreign key key - cheie străină

Gestiune produse finite fie se dă clic în panoul din stânga unde sunt tabelele, pe tabela dorită şi, ţinând apăsat butonul mouse -ului, se trage peste fundalul gri, unde sunt tabelele. 2. Se dă clic pe cheia primară din tabelatabela - părinte, părinte, ţinânduţinându-se apăsat butonul stânga al mousemouse -ului, şi se trage mouse-ul mouse -ul peste tabela-copil, eliberând butonul mouse-ului mouse- ului peste cheia străină (corespondentul cheii primare din tabela părinte). Va apărea un formular precum cel de jos. În cazul în care nu apare corespondenţa cheie primară – cheie străină, se poate schimba dând clic pe câmpul eronat şi alegerea, din combobox.

De regulă, se bifează primele două casete ( Enforce Referential Integrity şi Cascade Update Related Fields )

1.3 R ESTRICŢII 1.3.1 Restrictii la nivel de atribut 3 1. În tabela Clienti, denumirea clientului să fie obligatoriu cu majuscule. Validation Rule: StrComp(UCase([denumire StrComp(UCase([denumire client]);[denumire client];0)=0 2. Validation Text: "DENUMIREA CLIENTULUI - CU MAJUSCULE !!!" 3. În tabela Linii in documente , cantitatea trebuie să fie pozitivă.  Validation Rule: >0  Validation Text: „CANTITATEA TREBUIE SA FIE POZITIVA!!!” 4. Tabela Produse, unitate de masura să aibă prima literă scrisă cu majuscule. 4  Validation Rule : StrComp(Left(UCase([unitate StrComp(Left(UCase([unitate de masura]);1);Left([unitate masura]);1);Left([unitate de masura];1);0)=0  Validation Text: "INTRODUCETI PRIMA LITERA MAJUSCULA !!!" 3

Atenţie ca, regula de validare să fie aplicată câmpului solicitat! Ceea ce înseamnă că, întâi vom da clic pe câmpul respectiv respectiv apoi se completează Validation Rule şi Validation Text. 4 După cum se observă delimitatorul argumentelor funcţiilor este punctul şi virgula (;), dar poate fi şi virgula (,), funcţie de setările regionale ale sistemului de operare.

Gestiune produse finite 1.3.2 Restrictii la nivel de inregistrare5 În tabela Incasari, pentru chitante suma trebuie să fie mai mică de 500 de lei:  

Validation Rule: IIf(UCase([tip de document])="CHITANŢĂ";IIf([suma]<=500;True;False);True) Validation Text: "CU CHITANŢĂ, NU MAI MULT DE 500 RON !!!"

1.4 P O PULA REA CU DATE Baza de date utilizabilă pentru antrenament la popularea cu date. Conţine deja chei primare, legăturile între tabele, restricţii

gestiune produse finite - cu restrictii.accdb

Ordinea de populare (introducere de date) se face începând cu tabelele părinte, apoi cu tabelele copil .

Baza de date pentru antrenarea interogărilor, formularelor, rapoartelor etc. gestiune produse finite - populata.accdb

5

Atunci când regula de validare solicită cel puţin două atribute, din meniul Design se alege opţiunea Property Sheet, afişând un panou în partea dreaptă, unde se introduce regula de validare în fereastra Validation Rule. Dacă se doreşte să se lucreze mai uşor, se poate apăsa pe butonul cu trei puncte ( ), care apare când se selectează opţiunea Validation Rule. Astfel va fi afişat formularul Expression Builder.


1.5 I NTEROGĂRI

1.5.1 Filtrarea şi ordonarea directă a datelor dintr -o tabelă6 Ex. 1

Să se identifice clienţii din localitatea cu codul 1002, folosindu - se filtrarea directă a datelor.

1.5.2 Actualizarea directă a datelor

Adăugare

Ştergere

1.5.3 Crearea de interogări cu instrumente de tip Wizard 1.5.3.1

Ex. 2

6

Crearea unei interogări cu Simple Query Wizard

Să se obţină o listă cu toţi clienţii şi toate localităţile din care aceştia fac parte. Lista trebuie să conţină numele clienţilor şi numele localităţilor .

Se foloseşte doar pentru a obţine rezultate rapide, dar nu se prea practică într -o manieră profesionistă.


1.5.3.2

Ex. 3

Crearea unei interogări cu Crosstab Query Wizard

Să se creeze o listă cu încasările de la fiecare client pe tipuri de documente. Lista trebuie să conţină pe prima coloană clienţii şi câte o coloană pentru fiecare tip de document . La intersecţia fiecărui cl ient cu fiecare tip de document trebuie să apară suma încasărilor.


sau

sau TRANSFORM Sum([suma]) AS SumOfsuma SELECT [cod client], Sum([suma]) AS [Total suma] FROM incasari GROUP BY [cod client] PIVOT [tip de document]

1.5.4 Definirea interogărilor prin Query Design7 1.5.4.1 Interogări de selecţie

Ex. 4

Să se întocmească lista clienţilor din

localitatea cu

codul 1002. lista va cuprinde numai denumirea clienţilor. SELECT [denumire client] FROM clienti WHERE [cod localitate]="1002"

Dacă, în loc de select, din meniul QUERY se alege opţiunea Make-Table Query , se poate crea o nouă tabelă cu rezultatul respectivei interogări. Mult mai simplu este, introducerea la fraza SQL, expresia INTO si numele noii tabele: SELECT [denumire client] INTO [Tabela Noua] FROM clienti WHERE [cod localitate]="1002" Parametru

Pentru realizarea unui parametru cu specificaţie clară a tipului de dată solicitat, se alege din meniul Design, opţiunea Parameters, unde, în formularul ce apare se introduce expresia ce solicită inserarea unui element.

Aceeaşi expresie se introduce ca şi criteriu. 7

Ca şi în exemplificările de mai sus, vom prezenta la fiecare interogare utilizând Query Design şi alternativa SQL.

Gestiune produse finite PARAMETERS [Introdu codul localitatii] Text ( 255 ); SELECT [denumire client], [cod localitate] FROM clienti WHERE [cod localitate]=[Introdu codul localitatii]

Ex. 5

Care este conţinutul avizului de expediţie cu numărul 1002 . lista va conţine câmpurile: data aviz, denumire gestiune, denumire client, denumire localitate (localitatea clientului), denumire produs, cantitate, procent TVA, preţ.

1.5.4.2 Interogări de adăugare

Ex. 6

Introduceţi în tabela Clienţi clientul SC BENNY HILL S.R.L. cu codul 1008, din localitatea cu codul 1001, ce are soldul 777.


1.5.4.3 Interogări de modificare

Ex. 7

Să se modifice pentru clientul cu codul 1008 soldul în 888

1.5.4.4 Interogări de ştergere

Ex. 8

Ştergeţi clientul cu codul 1008

1.5.4.5 Interogare folosind două tabele

SELECT [denumire client], [denumire localitate] FROM localitati INNER JOIN clienti ON localitati.[cod localitate] = clienti.[cod localitate]

Gestiune produse finite 1.5.5 Interogări SQL 1.5.5.1 Primele argumente ale interogărilor SQL

Formatul specific unei interogări: SELECT câmp_1, câmp_2, ……, câmp_n FROM tabel_1, tabel_2, ………., tabel_n WHERE condiţie ORDER BY câmp_i, câmp_j Ex. 9

Lista clienţilor din localitatea cu codul 1002. Lista va cuprinde numai denumirea clienţilor. SELECT [denumire client] FROM clienti WHERE [cod localitate]= "1002"

Obs: Ex. 10

Constantele de tip text pot fi scrise între ghilimele (“) sau apostrof (’)

Care sunt clienţii din localitatea cu codul 1006 care au soldul >1000. Lista va cuprinde: denumire client, cod client. SELECT [denumire client], [cod client] FROM clienti WHERE [cod localitate]="1006" AND sold>1000

Obs: Pot fi folosiţi şi operatorii logici (AND/OR/NOT) Ex. 11

Care sunt documentele de intrare emise între 15 februarie şi 15 martie 2006. Lista va cuprinde numărul ş i

data

documentului.

SELECT [numar nota intrare], [data document] FROM [documente de intrare] WHERE [data document]>=#2006/02/15# AND [data document]<=#2006/03/15#

Data calendaristică s-a scris în format ISO: YYYY/MM/DD. Se pot folosi şi: DD/MM/YYYY sau MM/DD/YYYY . În loc de slash (/) se poate folosi ca delimitator şi cratimă ( -) Obs:

Ex. 12

Care sunt clienţii din judeţul cu

codul IS.

Lista va cuprinde doar denumirea clienţilor.

Varianta 18) SELECT [denumire client] FROM clienti, localitati WHERE clienti.[cod localitate]=localitati.[cod localitate] AND [denumire judet]= "IS"

Varianta 2) SELECT [denumire client] FROM localitati INNER JOIN clienti ON localitati.[cod localitate] = clienti.[cod localitate] WHERE [denumire judet]="IS" Ex. 13

8

Întocmiţi lista clienţilor din judeţul Iaşi în ordinea alfabetică a denumirii acestora. Lista va cuprinde: denumirea clientului şi soldul acestuia. SELECT [denumire client], [sold] FROM clienti c, localitati l WHERE c.[cod localitate]=l.[cod localitate] AND [denumire judet]= "IS" ORDER BY [denumire client]

Atunci când se lucrează cu interogări SQL, un avantaj al scrierii eficiente este redată de alias-uri: în loc de numele unei tabele, se declară o literă sau două pentru a înlocui numele tabelei cu acel alias. SELECT [denumire client] FROM clienti c, localitati l WHERE c.[cod localitate]=l.[cod localitate] AND [denumire judet]= "IS"

Ex. 14

Gestiune produse finite Întocmiţi lista clienţilor din judeţul Iaşi , ordonată după soldul descrescător. Lista va cuprinde denumire client şi sold SELECT [denumire client], sold FROM clienti AS c, localitati AS l WHERE c.[cod localitate]=l.[cod localitate] AND [denumire judet]="IS" ORDER BY sold DESC 1.5.5.2 Util izarea pri ncipalilor operatori

Operatori: =,<,>,<=,>=,AND, OR, BETWEEN, UNION, LIKE, IN Reluarea Ex. 11

SELECT [numar nota intrare], [data document] FROM [documente de intrare] WHERE [data document] BETWEEN #2006/02/15# AND #2006/03/15# Ex. 15

Ce încasări s -au făcut în perioada 1 ianuarie 2006 – 31 martie 2006, cu valori între 1000 şi 10000 de la clienţii a căror denumire începe cu una din literele de la A la S.

Varianta 1) SELECT i.* FROM incasari i, clienti c WHERE i.[cod client]=c.[cod client] AND [data document] BETWEEN #2006/01/01# AND #2006/03/31# AND suma BETWEEN 1000 AND 10000 AND MID([denumire client],1,1) BETWEEN "A" AND "S"

Varianta 2) SELECT i.* FROM incasari i, clienti c WHERE i.[cod client]=c.[cod client] AND MONTH([data document]) BETWEEN 1 AND 3 AND YEAR([data document]) AND suma BETWEEN 1000 AND 10000 AND LEFT([denumire client],1) BETWEEN "A" AND "S" Ex. 16

Care sunt codurile produselor ce apar în documentele de intrare sau în avizele de expediţie?

SELECT [cod produs] FROM [linii in documente] UNION SELECT [cod produs] FROM [linii in avize] Obs:

Cu ajutorul operatorului UNION se realizează reuniunea a două sau mai multe interogări. Se elimină automat liniile identice. Dacă se doreşte obţinerea tuturor liniilor din respectivele mulţimi, se foloseşte clauza ALL Ex. 17

Întocmiţi fişa produsului având

codul 1001

(fişa produsului conţine stocul iniţial, intrările şi ieşirile pentru produsul

respectiv)

SELECT 'Stoc initial' AS TipDoc, SPACE(8) AS NrDoc, stoc AS Cantitate FROM produse WHERE [cod produs] = '1001' UNION SELECT 'Nota intrare', [numar nota intrare], cantitate FROM [linii in documente] WHERE [cod produs]='1001' UNION SELECT 'Aviz de expeditie', [numar aviz], cantitate FROM [linii in avize] WHERE [cod produs]='1001' Obs: Funcţia SPACE returnează un şir de caractere Ex. 18

spaţiu de o anumită lungime.

Întocmiţi fişa produsului având codul 1001, în care ordinea liniilor din rezultat să fie ordinea cronologică de apariţie a documentelor (prima linie va fi ocupată de soldul iniţial) SELECT #2006-01-01# AS Data, 1 AS Ord, 'Stoc initial' AS TipDoc, SPACE(8) AS NrDoc, Stoc AS Cantitate

Gestiune produse finite FROM produse WHERE [cod produs]='1001' UNION SELECT [data document], 2, 'Nota intrare', i.[numar nota intrare], cantitate FROM [linii in documente] L, [documente de intrare] i UNION SELECT [data aviz], 3, 'Aviz de expeditie', a.[numar aviz], cantitate FROM [linii in avize] L, [avize de expeditie] a WHERE L.[numar aviz] = a.[numar aviz] AND [cod produs]='1001' Ex. 19

Care sunt clienţii de tip SRL 9? SELECT * FROM clienti WHERE [denumire client] LIKE '*SRL*'

Ex. 20

Care sunt gestionarii care au numele de familie format din 7 caractere şi se termină în ‘escu’ 10? SELECT * FROM gestiune WHERE UCASE([nume gestionar]) LIKE '???ESCU*'

Ex. 21

Întocmiţi lista clienţilor din judeţul Iaşi, Vaslui, Suceava, Botoşani.

Varianta 1) SELECT c.* FROM clienti c, localitati l WHERE c.[cod localitate]=l.[cod localitate] AND l.[denumire judet] IN ('IS','VS', 'SV', 'BT') Obs:

Operatorul IN este folosit pentru a testa dacă o expresie se regăseşte într -o listă de valori.

Varianta 2) Se poate şi prin utilizarea operatorului logic OR SELECT c.* FROM clienti c, localitati l WHERE c.[cod localitate]=l.[cod localitate] AND (l.[denumire judet] = 'IS' OR l.[denumire judet] = 'VS' OR l.[denumire judet] = 'SV' OR l.[denumire judet] = 'BT')

Varianta 3) Mulţimea în care caută IN nu e neapărat o listă de valori; poate fi mulţimea obţinută dintr -o subinterogare SELECT * FROM clienti WHERE [cod localitate] IN (SELECT[cod localitate] FROM localitati WHERE [denumire judet] IN ('IS', 'VS', 'SV', 'BT')) 1.5.5.3

Funcţii de agregare

COUNT, SUM, AVG, MIN, MAX Ex. 22

Câţi clienţi aveau soldul mai mare ca zero? SELECT COUNT (*) FROM clienti WHERE sold>0

Ex. 23

Câţi clienţi sunt din judeţul Iaşi? SELECT COUNT (*) FROM clienti

9

Caracterul asterix (*) înlocuieşte un şir de caractere Semnul întrebării (?) înlocuieşte un singur caracter.

10

Gestiune produse finite WHERE [cod localitate] IN ( SELECT [cod localitate] FROM localitati WHERE [denumire judet]='IS') Ex. 24

Câte documente de intrare s-au întocmit în gestiunea lui Popescu în anul 2006

SELECT COUNT (*) FROM gestiune g, [documente de intrare] d WHERE g.[cod gestiune]=d.[cod gestiune] AND UCASE([nume gestionar]) LIKE '*POPESCU*' AND YEAR([data document])=2006 Ex. 25

Care este valoarea încasărilor de la clientul cu codul 1001? SELECT SUM(suma) AS Total FROM incasari WHERE [cod client]='1001'

Ex. 26

Care a fost suma totală a soldurilor clienţilor? SELECT SUM(sold) AS Total FROM clienti

Ex. 27

Care este suma încasată de la clienţii din municipiul Iaşi?

Varianta 1) SELECT SUM(i.suma) AS Total FROM incasari i, clienti c, localitati l WHERE i.[cod client]=c.[cod client] AND c.[cod localitate]=l.[cod localitate] AND UCASE([denumire localitate]) LIKE 'IA?I'

Varianta 1’) Folosind o subinterogare şi operatorul IN SELECT SUM(suma) AS Total FROM incasari WHERE [cod client] IN ( SELECT [cod client] FROM clienti c, localitati l WHERE c.[cod localitate]=l.[cod localitate] AND UCASE([denumire localitate]) LIKE 'IA?I')

Varianta 2) Folosind o subinterogare şi operatorul IN, dar o subinterogare mai mică SELECT SUM(i.suma) AS Total FROM incasari i, clienti c WHERE i.[cod client]=c.[cod client] AND c.[cod localitate] IN ( SELECT [cod localitate] FROM localitati WHERE UCASE([denumire localitate]) LIKE 'IA?I')

Varianta 3) Folosind o subinterogare şi operatorul NOT IN SELECT SUM(suma) AS Total FROM incasari WHERE [cod client] NOT IN ( SELECT [cod client] FROM clienti c, localitati l WHERE c.[cod localitate]=l.[cod localitate] AND UCASE([denumire localitate]) NOT LIKE 'IA?I')

Varianta 4) Folosind două subinterogări imbricate SELECT SUM(suma) AS Total FROM incasari WHERE [cod client] IN ( SELECT [cod client] FROM clienti c

Gestiune produse finite WHERE [cod localitate] IN ( SELECT [cod localitate] FROM localitati WHERE UCASE([denumire localitate]) LIKE 'IA?I'))

Varianta 5) Folosind funcţia IIF SELECT SUM(IIF([denumire localitate] LIKE 'Ia?i', i.suma,0)) AS Total FROM incasari i, clienti c, localitati l WHERE i.[cod client]=c.[cod client] AND c.[cod localitate]=l.[cod localitate] Ex. 28

Care este volumul încasărilor din luna februarie 2006? SELECT SUM(i.suma) AS Total FROM incasari i WHERE MONTH([data document])=2 AND YEAR([data document])=2006

Ex. 29

Care este totalul vânzărilor din luna februarie 2006? SELECT SUM(cantitate*pret*(1+[procent TVA]/100)) FROM [avize de expeditie] a, [linii in avize] l WHERE a.[numar aviz]=l.[numar aviz] AND MONTH([data aviz])=2 AND YEAR([data aviz])=2006

Ex. 30

Care este totalul valorii producţiei din luna februarie 20 06? SELECT SUM(cantitate*pret) AS Productie FROM [documente de intrare] d, [linii in documente] l, produse p WHERE d.[numar nota intrare]=l.[numar nota intrare] AND l.[cod produs]=p.[cod produs] AND MONTH([data document])=2 AND YEAR([data document])=2006

Ex. 31

Care este valoarea totală a avizului de expediţie cu numărul 1001? SELECT SUM(cantitate*pret*(1+[procent TVA]/100)) AS [Valoare Totala] FROM [linii in avize] WHERE [numar aviz]='1001'

Ex. 32

Ce cantitate de lapte s- a vândut, pe piaţa municipiului Iaşi, în anul 2006?

SELECT SUM(la.cantitate) AS Cantitate_Totala FROM localitati l, clienti c, [avize de expeditie] a, [linii in avize] la, produse p WHERE l.[cod localitate]=c.[cod localitate] AND c.[cod client]=a.[cod client] AND a.[numar aviz]=la.[numar aviz] AND la.[cod produs]=p.[cod produs] AND UCASE(l.[denumire localitate]) LIKE 'IAS?I' AND UCASE(p.[denumire produs]) LIKE '*LAPTE*' Ex. 33

Ex. 34

Care este valoarea medie a încasărilor? SELECT AVG(suma) AS [Media incasarilor] FROM incasari Care este produsul care avea la începutul anului cel mai mare stoc?

SELECT [denumire produs] FROM produse WHERE stoc IN ( SELECT MAX(stoc) FROM produse) Ex. 35

Care este cea mai mare încasare din anul 2006?

SELECT * FROM incasari WHERE suma IN( SELECT MAX(suma)

Gestiune produse finite FROM incasari WHERE YEAR([data document])=2006) 1.5.5.4

Ex. 36

Gruparea înregistrărilor

Care sunt documentele de intrare cu cele mai multe linii (pot exista mai multe documente cu acelaşi număr maxim de linii)? Lista va cuprinde doar numerele documentelor de intrare.

SELECT [numar nota intrare] FROM [linii in documente] GROUP BY [numar nota intrare] HAVING COUNT(*)=( SELECT MAX(cate) FROM ( SELECT COUNT(*) AS cate FROM [linii in documente] GROUP BY [numar nota intrare])) Obs:

Clauza GROUP BY determină grupuri pe baza valorilor luate de unul sau mai multe câmpuri. Această clauză are sens doar dacă se foloseşte în interogare cel puţin o funcţie de agregare.

Clauza HAVING este asemănătoare cu clauza WHERE, numai că operează cu funcţii de agregare asupra grupului. Baza de date completată pentru verificare gestiune produse finite - finala.accdb

A POARTE 1.6 R

1.6.1 Asistentul de rapoarte Ex. 1

Să se realizeze un raport simplu care să conţină toate informaţiile despre gestiunile existente şi persoanele responsabile cu administrarea lor.

Alegerea tabelei gestiune, fără a se deschide, dând clic panoul obiecte, din stânga. Apoi, click pe opţiunea Report, din meniul Create, grupul de butoane Report 11.

Rezultatul este afişat în modul de vizualizare Layout View, de unde se mai pot ajusta dimensiunile obiectelor. Dacă se doreşte vizualizarea produsului finit, se alege Print Preview sau Report View. Ex. 2

11

Periodic serviciul vânzări solicită o listă actualizată a clienţilor pentru a analiza sumele încasate şi debitele pe care le au produsele vândute fiecăruia dintre ei.

În aceeaşi manieră rapidă se poate realiza şi un formular, în cazul de faţă pentru actualizarea gestiunii.



1.6.2 Raport simplu din două tabele Ex. 3

Periodic, serviciul de vânzări solicită lista actualizată a clienţilor în vederea elaborării politicilor de marketing pe localităţi.

1. Proiectarea raportului

Macheta raportului Lista clienţilor la data de 3/21/2007 Codul localităţii 1005 1002 1002 1006 1005 1006 1007

Denumirea localităţii Bârlad Iaşi Iaşi Galaţi Bârlad Galaţi Tecuci

Denumirea judeţului VS IS IS GL VS GL GL Pag 1

Codul clientului 1001 1002 1003 1004 1005 1006 1007

Denumirea clientului SC KOMBASAN SA SC UNIREA SA SC HOFFER SA SC CORAL SRL SC AMBRAS SA SC VADUL SA SC HORBAD SRL

2. Pregătirea datelor sursă

Interogarea Rap_3_clienti_pe_localitati 12 SELECT l.[cod localitate], [denumire localitate], [denumire judet], [cod client], [denumire client] FROM localitati l, clienti c WHERE l.[cod localitate]=c.[cod localitate] 3. Construirea raportului

12

Readucem aminte că, pentru a economisi timp, în loc de a scrie numele unei tabele, într -o interogare se poate folosi „alias-ul” care ajută înlocuind un cuvânt sau mai multe cu o literă sau două... (astfel, în loc de Localităţi ajunge doar un L, iar în loc de Clienţi se poate folosi un sugestiv C)

Gestiune produse finite Click dreapta pe raport şi selectarea opţiunii Properties sau din meniul Design, alegerea opţiunii Property Sheet .

Inserarea câmpurilor prin tragere, din acest tabel, în zona Detail a raportului. Controlul de tip Label, care conţine denumirea câmpului respectiv va fi dusă în zona Page Header. Afişarea/ascunderea zonelor Page Header/Footer sau Report Header/Footer se poate realiza fie dând clic pe butoanele din meniul Arrange sau din meniul contextual, dând clic dreapta pe una din „benzile” pe care scrie Detail, Page Header sau Report Header ori Report Footer sau Page Footer . Atribuirea unui titlu raportului şi inserarea datei curente se face alegând opţiunea Title, respectiv Date &Time din meniul Design. Va apărea un obiect de tip Label, în care se introduce textul: „ Lista clienţilor pe localităţi la data de ” şi formatarea textului de tip Garamond, 16 pt.

1.6.3 Raport cu gruparea înregistrărilor şi subtotaluri Ex. 4

Periodic, serviciul marketing solicită o situaţie a produselor vândute, grupate pe clienţi, care să conţină denumirea clienţilor, denumirea produselor, cantităţile livrate, unităţile de măsură, procentul TVA, preţurile, numărul şi data avizelor de expediţie. Valoarea totală a vânzărilor se va determina atât pentru fiecare client, cât şi pentru toţi clienţii la sfârşitul raportu lui.

1. Proiectarea raportulu i

Lista produselor vândute clienţilor până la data 3/21/2006 Denumirea clientului

Numărul avizului

Procentul TVA

Preţul

02.06.2006 Cornuri 30 Buc 19 02.06.2006 Lapte 12 L 19 Valoarea produselor vândute clientului SC HOFFER SA Kg 02.05.2006 Biscuiţi 25 Buc 19 02.05.2006 Covrigi 10 Kg 19 02.07.2006 Colaci 4 Buc 19 Valoarea produselor vândute clientului SC KOMBASAN SA Valoarea totală a produselor vândute

25 12,5 17,5

Data avizului

Denumirea produsului

Cantitatea

Unitatea de măsură

SC HOFFER SA 1005 1006 SC KOMBASAN SA 1002 1002 1003

12 15 20 47 64,5

2. Pregătirea datelor 13 SELECT [denumire client], AE.[numar aviz], [data aviz], [denumire produs], cantitate, [unitate de masura], [procent TVA], LA.pret, LA.[cod produs]

13

După cum se remarcă, reamintim că după SELECT, câmpurile care sunt unice (nu se regăsescîn mai multe tabele, cum este deseori cazul cheilor sau, ca în exemplul nostru, PRET, nu necesită referinţa la tabelă. Obligatoriu, însă ea se va trece la realizarea „punţii”, prin intermediul cheilor. Aliasul poate fi precedat sau nu de particula AS: clienti C este identic cu clienti AS C

Gestiune produse finite FROM clienti C, [avize de expeditie] AE, [linii in avize] LA, produse P WHERE C.[cod client]=AE.[cod client] AND AE.[numar aviz]=LA.[numar aviz] AND LA.[cod produs]=P.[cod produs] 3. Constru ir ea raportul ui

Aidoma exemplului anterior

4. Gruparea înregistrărilor . Meniul Design  Group & Sort . La denumire client , în partea inferioară a ferestrei se alege pentru Group Header , Group Footer se alege opţiunea Yes.

Pentru a calcula suma, se introduce un obiect de tip TextBox în zona denumire client Footer şi se dă apoi clic dreapta pe acest obiect, alegând din meniul contextual opţiunea Properties.

1.7 F ORMULARE Ex. 5

Să se realizeze un formular care să permită actualizarea ambelor tabele: localitati şi clienti.

Gestiune produse finite 4. Proiectarea formul arului

Macheta formularului Actualizare clienţi

Cod localitate Denumire localitate Judeţ Clienţi

Cod client

5. Defi nir ea formu larul ui cu asistentul de formul are.

wizard.

Denumire client

Sold

Din meniul Create se alege dând clic pe More Forms , opţiunea Form


Ex. 6

Să se realizeze un formular, denumit Avize de expediţie , pentru actualizarea informaţiilor despre produsele livrate clienţilor după macheta următoare Data curentă

Actualizare avize de expediţie

Data

Numărul avizului

Denumire produs

Cantitate

Denumirea clientului

Unitate de măsură

Procent TVA

Preţ

Aviz nou

Căutare

Ştergere

Valoarea totală a avizului de expediţie numărul

|<

<

Salvare

>

>|

Închidere formular


Evidenţă studenţi

EVIDENŢĂ STUDENŢI

2

Crearea tabelelor şi definirea restricţiilor

2.1

Baza de date necesară pentru antrenarea cheilor, restricţiilor, regulilor de validare EvStud - simpla.accdb

2.1.1

Crearea tabelelor şi declararea valorilor implicite Primary Key

Atribut (Field Name) STUDENTI Matricol

NumePren CNP Specializare AnStudii Fstudii Grupa

Tip (Data Type)

Lungime (Field Size)

Valori implicite (Default Value)

Text Text Text Text Number Text Number

10 40 15 50 Long Integer 2 Long Integer

"Trunchi comun" 1 "ID" 1

Text Text Number Number Number

6 40 Long Integer Long Integer Long Integer

6 28 28

Text Text Date/Time Number

10 6

DISCIPLINE

CodDisc DenumireDisc NrCredite NrOreCurs NrOreSeminar EXAMENE

Matricol CodDisc DataEx NotaEx

2.1.2

Reguli de validare

2.1.2.1

La nivel de atribut

Atribut (Field Name)

Matricol

Long Integer

"AE1101" IIf(Date()<#20/01/2006#;Date();#20/01/2006#) 7

Restrictii Validation Rule STUDENTI

Validation Text

StrComp(UCase([Matricol]);[Matricol];0)=0

Specializare AnStudii Fstudii Grupa

Literele din matricol, obligatoriu majuscule! StrComp(Left(UCase([Specializare]);1);Left([Specializare];1);0)=0 Prima litera din Specializare e majuscula! [AnStudii] Between 1 And 5 An studii intre 1 si 5! [Fstudii] In ('ZI';'ID') Forma studii doar ZI sau ID! [Grupa]>0 Nr grupei pozitiv!

CodDisc NrCredite NrOreCurs NrOreSeminar

StrComp(UCase([CodDisc]);[CodDisc];0)=0 <=8 <=42 <=42

DataEx

Month([DataEx]) In (1;2;5;6;7) And Year([DataEx])=2006

NotaEx

Between 1 And 10

cu

DISCIPLINE

Literele din CodDisc cu majuscule! Nr credite nu mai mult de 8! Nr orelor de curs nu mai mult de 42! Nr orelor de seminar nu mai mult de 42!

EXAMENE

2.1.2.2

Numai lunile IAN, FEB, MAI, IUN sau IUL din 2006! Nota este cuprinsa intre 1 - 10!

La nivel de înregistrare

Se deschide tabela în modul Design View, apoi se alege, din meniul View, opţiunea Properties Textul de afişat

Restrictii (Validation Rule)

(Validation Text) Tabela (Table) STUDENTI

[AnStudii]<>4 Or [AnStudii]=4 And [Grupa]<=10

La anul 4 de studiu pot fi maximum 10 grupe! Tabela (Table) DISCIPLINE

[NrOreCurs]+[NrOreSeminar]<=70

Nr ore curs + ore seminar cel mult 70! Tabela (Table) EXAMENE

[CodDisc]<>'AE1101' Or [CodDisc]='AE1101' And [DataEx] In

Examenele la Macroeconomie au fost programate pe 28 ian,

Evidenţă studenţi (#28.01.2006#;#02.11.2006#;#07.04.2006#)

2.1.3

11 feb si 4 iul!

Declararea restricţiilor referenţiale Legătura se realizează apelând în meniul DATABASE TOOLS, opţiunea RELATIONSHIPS.

În tabela părinte, se dă clic pe cheia primară, se ţine apăsat butonul stânga şi se deplasează peste câmpul cu acelaşi nume din tabela părinte şi se eliberează apoi mouse-ul.

2.2

Editarea tabelelor şi navigarea prin înregistrări

În gestionarul de obiecte, se apasă dublu clic pe tabela ce se doreşte a fi editată.

Evidenţă studenţi 2.3

Obţinerea de informaţii din baza de date

Baza de date cu restricţii şi populată, pentru antrenamentul cu interogări. EvStud - populata.accdb

Setul nr. 1 de întrebări a) Care sunt studenţii din anul I I I , specializarea Informatică economică, ZI ? Vari anta 1 – uti l izarea Query Desi gn 2.3.1

Din meniul Create se alege Query Design, apoi tabela cerută

Varianta 2 – uti li zarea SQL View

Din meniul Create se alege Query Design, dar în formularul ce apare se apasă butonul Close şi nu Add; se alege, din meniul Design, butonul View (SQL View)14.

Notă: Modalitatea de scriere în modul SQL View nu este dependentă de capitalizarea sau nu a literelor dar este mai elegant. De asemenea, tot scriptul ar putea fi scris pe un singur rând dar se citeşte mai uşor.

După ce a fost finalizată operaţiunea prin una din cele două variante, se alege opţiunea Run din meniul Query sau butonul de forma semnului exclamării de pe bara de butoane ( !). (1 ianuarie)? Ce studenţi trebuie felicitaţi de Sf. Vasil e Se utilizează, în cadrul criteriului, simbolul % înainte şi după cuvântul/cuvintele folosite ca şi condiţie, între primul simbol % şi cuvânt introducându-se şi spaţiu pentru a sugera identificarea cuvântului al doilea. b)

14

O altă modalitate este: în zona alocată tabelei (gri) sau pe banda de titlu (Query1: Select Query ) se apasă mouse-ul cu clic

dreapta şi se alege opţiunea SQL View .

Evidenţă studenţi Vari anta 1 – uti l izarea Query Desi gn

Notă: În cazul în care nu funcţionează condiţionarea utilizând simbolul %, atunci, sigur va merge cu simbolul *, nemailăsând spaţiu între simbolul * şi cuvânt, acest simbol având rolul de a înlocui un şir de caractere.

Vari anta 2 – uti li zarea SQL Vie

c) Ce studenţi trebuie felicitaţi de Sf. I on (7 ianuarie)?

d)

Componenţa grupei 4 de la specializarea Contabilitate şi Informatică de Gestiune , ZI , anul I I I ;

Notă: Pentru a face proba, în tabela Studenţi am mai introdus un nou student. a) Care sunt disciplinele cu peste 6 credite?

b) Care sunt disciplinele cu peste 28 de

ore de curs şi sub 42 de ore de seminar?

Evidenţă studenţi c)

În ce zile a susţinut examene studentul cu matricolul

d)

La ce discipline a susţinut examene studentul cu matricolul

? ELZ02001

? ELZ02001

e) Care este codul disciplinelor la care s-au susţinut examene în luna februarie 2006 ?

Setul nr. 2 de întrebări a) zilele în care s-a susţinut examen la Microeconomie ; 2.3.2

b) numele studenţilor examinaţi pe 28 ianu ari e 2006 ;

Note: 1. Clauza DISTINCT elimină apariţia repetată a unui examen, în cazul în care ar da mai multe examene în aceeaşi zi.

2. Se va avea în vedere formatul datei. Este posibil ca cel românesc să nu fie acceptat, de aceea este bine a se încerca, spre exemplu şi formatul: #1/28/2006#. A nu se uita de simbolul #. c) Care sunt ? 2006

studenţii de la Tr unchi

, Zi , Anul I care au picat comun

măcar un examen în

februarie

Evidenţă studenţi

Setul nr. 3 de întrebări a) denumirea disciplinelor la care a susţinut examen studentul obţinute; 2.3.3

L ama W. Dalai , datele

şi notele

b) numele studenţilor care au picat examenul Baze de date I măcar o dată;

c) rezultatele din 14 februarie 2006 ale grupei 1 din anul I V , specializarea Marketing , zi , la disciplina Cercetări de marketing (situaţia va cuprinde matricolul, numele şi nota);

Notă: Pentru a face proba, a fost introdus un student la specializarea cerută şi specializarea Marketing (CodDisc: AE4002)

2.3.4

Setul nr. 4 de întrebări

a) Câte discipline au 7 credite?

b)

Câţi studenţi sunt în anul I I I , specializarea Informatică Economică, cursuri de zi ?

Evidenţă studenţi c)

Câte credite a obţinut studentul ”picate”!)?

Barbu I. Vasile (atenţie,

nu se iau în calcul examenele

d) Câte credite s-au obţinut la nivelul anului I I I al specializării Informatică economică, Zi ?

e)

Care este cea mai mare notă la Microeconomie ?

Notă: Chiar dacă nu este necesar, este mai prudent a plasa un câmp între paranteze pătrate, atunci când el reprezintă argumentul unei funcţii, adică este plasat şi între paranteze rotunde. Parantezele pătrate se folosesc atunci când denumirea unui câmp, tabelă etc. au mai mult de un cuvânt. f)

Care este cea mai mică notă obţinută la nivelul anului

I I I , specializarea Informatică Economică,

Zi ?

g) Care este media grupei 1 din anul I I I , specializarea Informatică Economică, Zi , la disciplina Baze ? de date I pentru examenul susţinut pe 24 ianu arie 2006

Evidenţă studenţi h)

Câte puncte (nr. de credite înmulţit cu nota de la examen) a obţinut se iau în calcul examenele ”picate”!)?

Setul nr. 5 de întrebări a) La ce disciplină a susţinut primul examen studentul

L ama W. Dalai (atenţie,

nu

2.3.5

b) Care a fost disciplina/disciplinele la care s-au ? febru ari e 2006

? Barbu I . Vasil e

susţinut examenele în ultima zi de sesiune din

c) La ce discipline studentul Barbu I . Vasil e a obţinut note peste media sa?

Evidenţă studenţi d) Care a fost ultimul examen promovat de studentul Barbu I . Vasil e ?

e) Care este media notelor obţinute la

primul examen de

M icr oeconomie de la anul I ?

Setul nr. 6 de întrebări a) Câţi studenţi sunt în fiecare an de studii? 2.3.6

b)

Câte note sub 5 au fost, în total, la fiecare disciplină?

c)

Câte examene a susţinut (la câte s -a prezentat) fiecare student din anul I?

Evidenţă studenţi d)

Câţi studenţi au susţinut examene în fiecare zi de sesiune?

e)

Câţi studenţi au fost examinaţi în cele patru luni de sesiuni, pentru fiecare disciplină?

f)

Numărul de examene picate, pentru fiecare an de studii.

Setul nr. 7 de întrebări a) Care dintre anii de studii numără mai mult de 2.3.7

1000 de studenţi?

Notă: Pentru a putea verifica interogarea, am ales ca şi condiţie cifra 2 în locul lui 1000, neavând o bază de date cu peste 1000 de înregistrări.

b) Care sunt studenţii care au susţinut

c)

în sesiunile ianuarie -februarie 2006 mai mult de 3 examene?

Ştiind că facultatea are doar patru săli pentru examinare, să se extragă zilele în care numărul examenelor este mai mare decât cel al sălilor.

Baza de date rezolvată, pentru verificare EvStud - rezolvata.mdb

Modele lucrări practice

3 3.1

MODELE LUCRĂRI PRACT ICE

Lucrarea practică nr. 1 Transport-rez.mdb

Dispuneti de baza de date TRANSPORT cu tabelele AUTOBUZE(Numar, Tip, Locuri), PLECARI(Ora, Destinatie, Marca, Numar) şi SOFERI(Marca, Numepren, CNP, Strada, Numar, Telefon). Se cere: Să se analizeze structurile celor trei tabele şi să se stabilească cheile primare şi, unde este posibil,  cheile alternative.  Să se stabilească legăturile permanente dintre tabele.  Să se definească următoarele restricţii: o În tabela Autobuze literele din câmpul Numar să fie majuscule. o În tabela Plecari, valorile câmpului Ora să fie cuprinse între 6 şi 22. o În tabela Plecari, şoferului Zaharia Cezar (marca 1010 ) îi sunt interzise cursele spre Bacau. Să se scrie o frază SELECT SQL pentru obţinerea listei cu plecările spre Bacău, realizate cu autobuze  care au mai mult de 40 de locuri. Lista va oferi următoarele informaţii: ora plecării, numărul de înmatriculare, tipul autobuzului, numărul de locuri. Propunere de rezolvare 

cheile primare / cheile alternative: a se vedea în figura de mai jos 15. legăturile permanente dintre tabele 16.



restricţii:



o

În tabela Autobuze literele din câmpul Numar să fie majuscule. StrComp(UCase([numar]);[numar];0)=0

o

În tabela Plecari, valorile câmpului Ora să fie cuprinse între 6 şi 22.

o

În tabela Plecari, şoferului Zaharia Cezar (marca 1010) îi sunt interzise cursele spre Bacau 17.

Between 6 And 22

IIf([marca]=1010;IIf([destinatie]="Bacau";False;True);True) 

frază SELECT SQL pentru obţinerea listei cu plecările spre Bacău, realizate cu autobuze care au mai mult de 40 de locuri. SELECT ora, Autobuze.numar, tip, locuri, destinatie FROM Autobuze INNER JOIN Plecari ON Autobuze.numar = Plecari.numar WHERE locuri>=40 AND destinatie="Bacau"

15

16

Cheia alternativă ar fi CNP-ul, pe care-l regăsiţi în enunţ, la tabela SOFERI, dar nemaifigurat în tabela propriu-zisă. Pe lângă atributul Indexed, setat Yes (No Duplicates), ar mai putea fi ales şi Required cu opţiunea Yes, pentru a evita valori nule.

A nu se face confuzia între câmpul NUMAR, existent în tabelele AUTOBUZE şi PLECĂRI, cu câmpul NUMAR din tabela SOFERI, care se referă la numărul străzii şi nu al autobuzului ca în primele tabele. 17 Validare la nivel de înregistrare (Property Sheet) pentru că avem nevoie de două câmpuri.

Modele lucrări practice 3.2

Lucrarea practică nr. 2 Aprov-rez.mdb

Dispuneţi de baza de date APROV care conţine tabelele: FURNIZOR(Codfurn, Numefurn, Localitate, Banca), APROVIZIONARE(Nrfactura, Data, Categorie, Codfurn, Valoarefact) şi PLATI(Nrfactura, Dataplata, Valoare). Se cere:  Să se analizeze structura tabelei Plati şi să se stabilească cheia primară. Să se stabilească legăturile permanente dintre tabele şi să se aplice restricţiile referenţiale.  Să se înregistreze în baza de date o nouă aprovizionare: de la un furnizor nou de birotică (datele  despre furnizor: 1010, FLAMINGO SRL, Iasi, BRD), factura nr. 12445, din 10 mai 2011, în valoare de 215 lei.  Să se afişeze o listă a furnizorilor din Iasi sau Vaslui. Să se scrie o frază Select SQL pentru a obţine o listă a facturilor din categoria “utilitati”, cu valoarea  de peste 200 lei (Nrfactura, Numefurn, Data, Valoarefact).  Să se întocmească un raport al aprovizionărilor (Număr factură, Data, Nume furnizor, Valoare), cu gruparea înregistrărilor după categoria de furnizor. Pentru fiecare categorie de furnizor se va cal cula totalul valoric al facturilor. Propunere de rezolvare  



cheia primară pentru tabela Plăţi – conform figurii de mai jos. legăturile permanente dintre tabele şi restricţiile referenţiale.

aprovizionare nouă: de la un furnizor nou de birotică (datele despre furnizor: 1010, FLAMINGO SRL, Iasi, BRD), factura nr. 12445, din 10 mai 2011, în valoare de 215 lei. INSERT INTO Aprovizionare (nrFactura, data, valoareFac, codFurn, categorie ) SELECT 12445, #5/10/2010#, 215, 1010, "birotica"

sau18

şi INSERT INTO Furnizori ( codFurn, numeFurn, localitate, banca ) SELECT 1010, "FLAMINGO SRL", "Iasi", "BRD"

sau

În oricare din variantele alese, acţiunea se finalizează apăsând butonul Run (!) din meniul Design. Verificarea operaţiunii se 18

Varianta cu Append Query presupune următorii paşi:  Create  Query Design.  Se apasă butonul Close (nu se adaugă nicio tabelă).  Se alege din meniul Design opţiunea Append Query. În acest formular se va adăuga tabela dorită (de ex. APROVIZIONARE).  În formularul structurii interogării se scrie la Field noua înregistrare (de ex. 12445) şi la Append To câmpul unde se adaugă înregistrarea (de ex. NrFactura). La fel se face şi cu restul înregistrărilor. A se avea în vedere următoarele aspecte: la valori tip dată se vor scrie între simbolul #, la text, între ghilimele. De asemenea, dacă trebuie să facem adăugări în mai multe tabele trebuie respectate ordinea populării: întâi părinţii.

Modele lucrări practice face deschizând tabelele şi urmărind dacă s-au operat adăugările. 

Lista a furnizorilor din Iasi sau Vaslui. sau SELECT codFurn, numeFurn, localitate, banca FROM Furnizori WHERE localitate="Iasi" OR localitate="Vaslui"



Lista facturilor din categoria “utilitati”, cu valoarea de peste 200 lei (Nrfactura, Numefurn, Data, Valoarefact). SELECT nrFactura, numeFurn, data, valoareFac FROM Furnizori INNER JOIN Aprovizionare ON Furnizori.codFurn = Aprovizionare.codFurn WHERE valoareFac>=200 AND categorie="utilitati"



R aport al aprovizionărilor (Număr factură, Data, Nume furnizor, Valoare), cu gruparea înregistrărilor după categoria de furnizor. Pentru fiecare categorie de furnizor se va calcula totalul valoric al facturilor 19.

La rulare va arăta ca mai jos:

19

Realizarea acestui raport se poate face din Report Wizard.


Lucrarea practică nr. 3 Dispuneţi de baza de date APROV care conţine tabelele FURNIZOR(Codfurn, Numefurn, Localitate, Banca), APROVIZIONARE(Nrfactura, Data, Categorie, Codfurn, Valoarefact) şi PLATI(Nrfactura, Dataplata, Valoare). Se cere: Să se analizeze structura tabelei Plati şi să se stabilească cheia primară.  Să se stabilească legăturile permanente dintre tabele.   Să se definească o regulă de validare care să impună ca valoarea maximă a unei facturi pentru un furnizor din categoria “birotica” să nu fie mai mare de 300 lei.  Folosind o interogare Select SQL, să se afişeze o listă cu facturile care nu au fost plătite integral, ordonate descrescător după valoarea facturii. Lista va conţine: Nrfactura, Categorie, Valoare.  Să se obţină un formular pentru vizualizarea /actualizarea nomenclatorului de furnizori.  Să se întocmească un raport al plăţilor (Număr factură, Nume furnizor, Data plată, Valoare), cu gruparea înregistrărilor pe zile. Se va calcula total valoare facturi pentru fiecare zi şi valoarea medie a plăţilor zilnice. 3.3

Propunere de rezolvare Dispuneţi de baza de date APROV care conţine tabelele FURNIZOR(Codfurn, Numefurn, Localitate, Banca), APROVIZIONARE(Nrfactura, Data, Categorie, Codfurn, Valoarefact) şi PLATI(Nrfactura, Dataplata, Valoare). Se cere: analiza tabelei Plati şi stabilirea cheii primară – conform figurii de mai jos.   legăturile permanente dintre tabele.

regulă de validare care să impună ca valoarea maximă a unei facturi pentru un furnizor din categoria “birotica” să nu fie mai mare de 300 lei20.



IIf([categorie]="birotica";IIf([valoareFac]<=300;True;False);True)

interogare Select SQL, să se afişeze o listă cu facturile care nu au fost plătite integral, ordonate descrescător după valoarea facturii.21.



SELECT Aprovizionare.nrFactura, categorie, valoarefac-SUM(NZ([valoare],0)) AS [valoare neachitata] FROM Aprovizionare LEFT JOIN Plati ON Aprovizionare.nrFactura = Plati.nrFactura GROUP BY Aprovizionare.nrFactura, categorie, data, valoarefac HAVING valoareFac >SUM(NZ([valoare],0)) ORDER BY data DESC

sau

20 21

Validare la nivel de înregistrare (Property Sheet) Dacă enunţul făcea referire doar la facturi neplătite, se putea realiza următoarea interogare: SELECT nrFactura, categorie, valoareFac FROM Aprovizionare WHERE nrFactura Not In (SELECT nrFactura FROM Plati) ORDER BY data DESC





Modele lucrări practice formular pentru vizualizarea /actualizarea nomenclatorului de furnizori.

raport al plăţilor (Număr factură, Nume furnizor, Data plată, Valoare), cu gruparea înregistrărilor pe zile. Se va calcula total valoare facturi pentru fiecare zi şi valoarea medie a plăţilor zilnice.




Următorii paşi ţin de gustul estetic, altfel puteţi da direct Finish.

Lucrarea practică nr. 4

3.4

Vanzare-rez.mdb

Dispuneţi de o bază de date VANZARE cu tabelele AGENŢI(Codag, Nume, Adresa, Telefon), VANZARI(Nrfactura, Data, Valfact, Codag, Codzona) şi ZONE(Codzona, Denumire, Procent). Se cere:  Să se analizeze atributele din fiecare tabel şi să se stabilească cheile primare corespunzătoare acestora.  Să se stabilească legăturile permanente dintre cele 3 tabele şi să se aplice restricţiile referenţiale.  Să se adauge factura cu numărul 2222, din data de 12 mai 2011, în valoare de 100 lei. Factura este întocmită pentru zona Tataraşi de către un agent nou, Marinescu Ioana, de pe strada Ion Creangă, nr. 13, care are codul A10.  Folosind o interogare SQL, să se afişeze o listă cu vânzările din zona 1, cu valori mai mici de 300 lei (Lista va conţine: Nume agent, Număr factură, Data facturii, Valoare factură).  Să se obţină într -un raport vânzările agentului A2, grupate pe zile. Raportul va conţine: Numele agentului, Numărul facturii, Data facturii şi Valoarea facturii. Propunere de rezolvare  



cheile primare sunt redate în figura de mai jos. legăturile permanente dintre cele 3 tabele şi restricţiile referenţiale.

factura cu numărul 2222, din data de 12 mai 2011, în valoare de 100 lei. Factura este întocmită pentru zona Tataraşi de către un agent nou, Marinescu Ioana, de pe strada Ion Creangă, nr. 13, care are codul A10 22. INSERT INTO Agenti ( codAg, nume, adresa )

şi

SELECT "A10", "Marinescu Ioana", "strada Ion Creangă, nr. 13" INSERT INTO Vanzari (nrFactura, data, valFact, codAg, codZona) SELECT 2222, #1/12/2010#, 100, "A10", 5



lista cu vânzările din zona 1, cu valori mai mici de 300 lei SELECT nume, nrFactura, data, valFact FROM Agenti INNER JOIN Vanzari ON Agenti.codAg = Vanzari.codAg WHERE valFact<=300 AND codZona=1

22

Pentru varianta Append Query a se vedea LP2

Exercitii Access Rezolvate

Recommend Documents