Proiectdd.docx

Electronica

Proiectarea Bazelor de Date

1

Cuprins 1. Enunt si cerinte .................................................................................... 3 2. Crearea bazei de date ……….................................................................. 4 3. Crearea bazei de date……………………………………………………………………….. 5 4. Calitatea proiectării bazei de date…………………………………………………….. 8 5. Indexarea unei tabele .......................................................................... 9 6. Inserarea datelor in tabele………………………………………………………………… 9 7. Interogari pe baza de date..................................................................... 24 8. Functii .................................................................................................... 33 9. Proceduri ............................................................................................... 35 10. Trigger ................................................................................................. 39 11. Bibliografie .......................................................................................... 40

2

1. Enunt Să se creeze o bază de date care să ţină evidenţa clienţilor unui magazin. Fiecare client are un nume, o dată de înregistrare, o adresă şi alte detalii. Fiecare adresă se va reprezenta prin oraş, stradă, număr, bloc, etaj, apartament, cod poştal, judeţ, ţară şi tip de client. Mai multi clienţi pot avea aceeaşi adresă şi un client poate să aibă mai multe adrese. Fiecare client are o listă de contacte (persoane din cadrul magazinului). Fiecare persoană de contact poate lucra cu unul sau mai mulţi clienţi, se identifică prin nume, statut în cadrul firmei, departament, telefon. Fiecare persoană de contact ţine o listă cu comenzile pe care le-a primit. Fiecare comandă are o dată, un număr de ordine şi o sumă totală.

Cerinte   

Să se realizeze modelul entitate-legătură. Să se facă trecerea la modelul relaţional (să se detalieze operaţiile efectuate pentru fiecare entitate în parte) .  Să se creeze o bază de date care să conţină: 1. Legături între tabele; 2. Integritatea referenţială pentru a efectua corect operaţiile de actualizare (adăugare, ştergere, modificare); 3. Chei primare şi chei externe; 4. Reguli de ştergere şi modificare în cascadă pentru entităţile cu chei externe. 1) Să se facă testarea bazei de date prin efectuarea de diferite operaţii asupra ei. Trebuie să existe cel puţin o interogare din fiecare tip (în total minim 15 interogări), 2 funcţii diferite (care să nu facă un simplu select), 2 proceduri stocate şi 1 trigger.

3

2. Modelul Entitate-Legatura

Detalierea operatiilor: Fiecare entitate este transformata in cate o tabela separata cu campurile echivalente atributelor. Fiecare atribut identificator devine cheia primara a acelei tabele. Pentru legaturile tip 1-M, se considera cheie externa in tabela din partea M, facand legatura cu cealalta tabela, iar pentru legaturile tip M-M se construieste cate o tabela separata care contine cheile externe catre cele doua tabele intre care se face legatura. In modelul entitate-legatura exista un supertip „Persoana” si doua subtipuri „Angajat”, care la randul lui contine un subtip „Manager Magazin”, si „Client”. O persoana poate fi atat Angajat, cat si Client. Valorile pentru pret din tabelele Produs si ProdCump sunt valori calculate. In tabela Produs, valoarea pretului

4

este valoarea minima dintre preturile de la furnizori, iar in tabela ProdCump se calculeaza ca fiind Cantitate*Produs.Pret.

3. Crearea bazei de date create table Magazin ( CodMagazin varchar(5) primary key not null, Denumire varchar(30) not null ); create table Persoana ( CNP varchar(13) primary key not null, Nume varchar(50) not null, Adresa varchar(30), DataNasterii date ); create table Angajat ( idAngajat int primary key not null, CNP varchar(13) not null, Salariu float not null, codMagazin varchar(5) not null );

alter table Angajat add ( CONSTRAINT angajatp_fk FOREIGN KEY (CNP) REFERENCES Persoana(CNP) on delete cascade, CONSTRAINT angajatr_fk FOREIGN KEY (codMagazin) REFERENCES Magazin(CodMagazin) on delete cascade );

5

create table Manager_Magazin ( idAngajat int not null, Bonus float not null ); alter table Manager_Magazin add ( constraint magazin_pk primary key (idAngajat,Bonus), constraint manager_fk foreign key (idAngajat) references Angajat(idAngajat) on delete cascade );

create table Client ( NrLeg varchar(5) primary key not null, CNP varchar(13) not null ); alter table Client add constraint clientp_fk foreign key (CNP) references Persoana(CNP) on delete cascade; create table Produs ( CodProdus int primary key not null, Denumire varchar(50) not null, Pret float, Cantitate int not null, codMagazin varchar(5) not null ); alter table Produs add constraint produsr_fk foreign key (codMagazin) references Magazin(CodMagazin) on delete cascade; create table Furnizor ( CodFurnizor int primary key not null, Nume varchar(50) not null, Adresa varchar(50) ); 6

create table ProdusFurnizor ( CodProdus int not null, CodFurnizor int not null, PretInt float not null ); alter table ProdusFurnizor add ( constraint pf_pk primary key (CodProdus,CodFurnizor), constraint pfp_fk foreign key (CodProdus) references Produs(CodProdus) on delete cascade, constraint pff_fk foreign key (CodFurnizor) references Furnizor(CodFurnizor) on delete cascade ); create table Factura ( Serie varchar(20) not null, NrLeg varchar(5) not null, DataFact date not null ); alter table Factura add (constraint fact_pk primary key (Serie), constraint factp_fk foreign key (NrLeg) references Client(NrLeg) on delete cascade ); create table ProdCump ( Serie varchar(20) not null, CodProdus int not null, Cantitate int not null, Angajat int not null, Valoare float );

7

alter table ProdCump add ( constraint prodc_pk primary key (Serie,CodProdus), constraint prodcp_fk foreign key (CodProdus) references Produs(CodProdus) on delete cascade, constraint prods_fk foreign key (Serie) references Factura(Serie) on delete cascade, constraint proda_fk foreign key (ANgajat) references Angajat(idAngajat) on delete cascade );

4. Calitatea proiectării bazei de date. FN1: Relatiile din baza de date se afla in FN1, deoarece nu au atribute multivaloare. FN2: Relatiile din baza de date se afla in FN2, deoarece in relatiile in care exista cheie primara multipla, celelalte atribute depind de intreaga cheie primara. FN3: Relatiile din baza de date se afla in FN3, deoarece nu exista relatii in care atributele sa depinde de altceva, in afara de cheia primara. FNBC: Relaţiile din baza de date este în FNBC, deoarece orice determinant al relatiilor este cheie candidat.

8

5. Indexarea unei tabele create index indexProdus on Produs(CodProdus,Denumire);

6. Inserarea datelor in tabele -- insert in Magazin insert into Magazin (CodMagazin,Denumire) values ('LACTA','Lactate'); insert into Magazin (CodMagazin,Denumire) values ('PATIS','Patiserie'); insert into Magazin (CodMagazin,Denumire) values ('PANIF','Panificatie'); insert into Magazin (CodMagazin,Denumire) values ('MEZEL','Mezeluri'); insert into Magazin (CodMagazin,Denumire) values ('CARNP','Carne proaspata'); insert into Magazin (CodMagazin,Denumire) values ('CARNC','Carne congelata'); insert into Magazin (CodMagazin,Denumire) values ('LEGFR','Legume-Fructe'); insert into Magazin (CodMagazin,Denumire) values ('BAUTR','Bauturi racoritoare'); insert into Magazin (CodMagazin,Denumire) values ('BAUTA','Bauturi alcoolice'); insert into Magazin (CodMagazin,Denumire) values ('DULCE','Dulciuri'); insert into Magazin (CodMagazin,Denumire) values ('VRAC','Alimente vrac'); insert into Magazin (CodMagazin,Denumire) values ('PESTE','Peste'); insert into Magazin (CodMagazin,Denumire) values ('CONS','Conserve si gemuri');

9

insert into Magazin (CodMagazin,Denumire) values ('AUTO','Piese auto'); insert into Magazin (CodMagazin,Denumire) values ('ANIM','Animale'); insert into Magazin (CodMagazin,Denumire) values ('SNACK','Snacks-uri si Chips-uri'); insert into Magazin (CodMagazin,Denumire) values ('COPII','Jucarii'); insert into Magazin (CodMagazin,Denumire) values ('HAINE','Imbracaminte si incaltaminte'); insert into Magazin (CodMagazin,Denumire) values ('DETER','Produse de curatenie'); insert into Magazin (CodMagazin,Denumire) values ('ELCTR','Electronice si electrocasnice'); --insert into Persoana insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900212160021','Popescu Ion','Craiova, str.Caracal', to_date('12021990','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1934435160021','Popa Cristina','Bucuresti, str.Liviu Rebreanu', to_date('17121944','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900213424021','Ionescu Adrian','Craiova, str.Amaradia', to_date('12031985','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2851112160021','Sandu Dan','Craiova, str.Brestei', to_date('17091977','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2991112160021','Barbu Alina','Caracal, str.Craiovei', to_date('15121969','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1876543230021','Albu Raluca','Craiova, str.Lamaitei', to_date('04121990','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2345662160021','Badea Andrei','Arad, str.Timisoarei', to_date('17031954','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900234560021','Andrei Sorin','Craiova, str.1 mai', to_date('21061959','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900963072321','Antonescu Roxana','Craiova, str.Oltenia', to_date('27111983','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2039731216021','Serban Rodica','Alexandria, str.Caracal', to_date('22011985','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900333160021','Ene Bogdan','Craiova, str.Paltinis', to_date('15031938','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2222212160931','Barbulescu Lavinia','Caracal, str.Alexandriei', to_date('12091983','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900212161234','Georgescu Ion','Craiova, str.Henry Ford', to_date('31051983','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900212175832','Badoi George','Craiova, str.George Enescu', to_date('23051984','DDMMYYYY'));

10

insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900212172311','Caldare Cristian','Craiova, str.Dacia', to_date('12061990','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900218372921','Gadoiu Andreea','Pitesti, str.Craiovei', to_date('15021973','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1933212162221','Popescu Alin','Craiova, str.Doljului', to_date('12091980','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900212111021','Davidescu Ion','Bucuresti, str.Luncii', to_date('17041960','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1901111111021','Iordache Mirela','Craiova, str.Opanez', to_date('19081959','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900212120021','Radu Cristiana','Craiova, str.Decebal', to_date('19021973','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1900212162222','Predescu Ionela','Craiova, str.Teilor', to_date('22051949','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2880616131564','Goanta Miruna','Iasi, str.Voievozilor', to_date('16061988','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1860601167951','Popovici Claudiu','Targoviste, str.Lalelelor', to_date('01061986','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1801212236578','Mihai Petre','Craiova, str.Dacia', to_date('12121980','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1910512168749','Buricea Ionel','Craiova, str.Decebal', to_date('12051991','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2880803123654','Ionescu Ionelia','Craiova, str.N.Titulescu', to_date('03081988','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2691110155479','Preda Ioana','Timisoara, str.Calea Bucuresti', to_date('10111969','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('2830504698742','Mardale Oana','Arad, str.Calea Timisoarei', to_date('04051983','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1961212264789','Mihai Mihaita','Craiova, str.Lamaitei', to_date('12121996','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1901221544895','Argus Lucian','Titu, str.Bancuta', to_date('22011990','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1870307336896','Mirescu Alexandru','Bucuresti, str.Bradului', to_date('07031987','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1891115656899','Popa Sorin','Craiova, str.Teilor', to_date('15111989','DDMMYYYY')); insert into Persoana (CNP,Nume,Adresa,DataNasterii) values ('1881211644899','Prescu Cristinel','Craiova, str.Brandusa', to_date('11121988','DDMMYYYY'));

11

--insert into Client insert into Client (CNP,NrLeg) values ('1900212160021','10001'); insert into Client (CNP,NrLeg) values ('1934435160021','10002'); insert into Client (CNP,NrLeg) values ('1900213424021','10003'); insert into Client (CNP,NrLeg) values ('2851112160021','10004'); 12

insert into Client (CNP,NrLeg) values ('2991112160021','10005'); insert into Client (CNP,NrLeg) values ('1876543230021','10006'); insert into Client (CNP,NrLeg) values ('2345662160021','10007'); insert into Client (CNP,NrLeg) values ('1900234560021','10008'); insert into Client (CNP,NrLeg) values ('1900963072321','10009'); insert into Client (CNP,NrLeg) values ('2039731216021','10010'); insert into Client (CNP,NrLeg) values ('1900333160021','10011'); insert into Client (CNP,NrLeg) values ('2222212160931','10012'); insert into Client (CNP,NrLeg) values ('1900212161234','10013'); insert into Client (CNP,NrLeg) values ('1900212175832','10014'); insert into Client (CNP,NrLeg) values ('1900212172311','10015'); insert into Client (CNP,NrLeg) values ('1900218372921','10016'); insert into Client (CNP,NrLeg) values ('1933212162221','10017'); insert into Client (CNP,NrLeg) values ('1900212111021','10018'); insert into Client (CNP,NrLeg) values ('1901111111021','10019'); insert into Client (CNP,NrLeg) values ('1900212120021','10020');

13

--insert into Furnizor insert into Furnizor (CodFurnizor,Nume,Adresa) values (100,'Danone','Bucuresti'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (101,'CocaCola','Bucuresti'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (102,'Poiana','Brasov'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (103,'Vinuri Vrancea','Focsani'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (104,'Abatorul Diana',NULL); insert into Furnizor (CodFurnizor,Nume,Adresa) values (105,'Castro','Bucuresti'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (106,'Floriol','Iasi'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (107,'Spar','Bucuresti'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (108,'Bergenbier',NULL); insert into Furnizor (CodFurnizor,Nume,Adresa) values (109,'Ulker','Bucuresti'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (110,'D-Toys','Bucuresti'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (111,'Laura','Cluj-Napoca'); 14

insert into Furnizor (CodFurnizor,Nume,Adresa) values (112,'PanGroup','Craiova'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (113,'VelPitar',NULL); insert into Furnizor (CodFurnizor,Nume,Adresa) values (114,'Albalact','Cluj-Napoca'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (115,'Unisol',NULL); insert into Furnizor (CodFurnizor,Nume,Adresa) values (116,'Danone','Bucuresti'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (117,'Murfatlar','Constanta'); insert into Furnizor (CodFurnizor,Nume,Adresa) values (118,'Jolidon',NULL); insert into Furnizor (CodFurnizor,Nume,Adresa) values (119,'Pepsi','Bucuresti');

--insert into Produs insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (200,'Ciocolata Poiana',NULL,50,'DULCE'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (201,'Ciocolata Milka',NULL,50,'DULCE'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (202,'Fanta',NULL,60,'BAUTR'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (203,'Mirinda',NULL,50,'BAUTR'); 15

insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (204,'Biscuiti cu ciocolata',NULL,50,'DULCE'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (205,'Iaurt Danone cu fructe',NULL,50,'LACTA'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (206,'Lichid de parbriz',NULL,20,'AUTO'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (207,'Pepene verde',NULL,20,'LEGFR'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (208,'Morcovi',NULL,10,'LEGFR'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (209,'Bluza cu maneca scurta',NULL,10,'HAINE'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (210,'Masina cu telecomanda',NULL,5,'COPII'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (211,'Chio chips',NULL,20,'SNACK'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (212,'Paine de secara VelPitar',NULL,10,'PANIF'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (213,'Paine de secara PanGroup',NULL,10,'PANIF'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (214,'Whiskas junior',NULL,20,'ANIM'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (215,'Cuptor cu microunde',NULL,10,'ELCTR'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (216,'Frigider',NULL,6,'ELCTR'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (217,'Vin de masa dulce Murfatlar',NULL,10,'BAUTA'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (218,'Parizer de pui',NULL,20,'MEZEL'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (219,'Muschi de vita',NULL,10,'CARNC'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (220,'Ciocolata Rom',NULL,100,'DULCE'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (221,'Laptop HP',NULL,15,'ELCTR'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (222,'Pufuleti Duck',NULL,350,'SNACK'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (223,'Pantaloni 3/4',NULL,17,'HAINE'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (224,'Cauciuc iarna',NULL,124,'AUTO'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (225,'Coca Cola',NULL,1001,'BAUTR'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (226,'Picioare de porc',NULL,23,'MEZEL'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (227,'Parizer taranesc',NULL,64,'MEZEL'); 16

insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (228,'Bere Timisoreana',NULL,10,'BAUTA'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (229,'Vin de masa',NULL,10,'BAUTA'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (230,'Tacamuri de pui',NULL,100,'CARNC'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (231,'Lapte batut LaDorna',NULL,10,'LACTA'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (232,'Cascaval Rustic',NULL,14,'LACTA'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (233,'Iaurt Danone',NULL,113,'LACTA'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (234,'Ariel',NULL,90,'DETER'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (235,'Bonux',NULL,44,'DETER'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (236,'Figurina de actiune',NULL,61,'COPII'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (237,'Masinuta teleghidata',NULL,17,'COPII'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (238,'Macrou',NULL,101,'PESTE'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (239,'Hering',NULL,10,'PESTE'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (240,'Gem de piersici',NULL,10,'CONS'); insert into Produs (CodProdus,Denumire,Pret,Cantitate,CodMagazin) values (241,'Gem de gutui',NULL,10,'CONS');

17

--insert into ProdusFurnizor insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (209,118,25.4); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (212,112,2.8); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (212,113,2.2); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (213,112,2); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (213,113,2.4); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (202,119,6); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) 18

values (202,101,5.4); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (218,104,15); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (219,104,28); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (206,105,25); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (208,106,2); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (203,101,6); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (203,119,5); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (217,103,20); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (217,117,14.4); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (207,107,7); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (210,110,34); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (205,100,1.5); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (205,114,2); insert into ProdusFurnizor (CodProdus,CodFurnizor,PretInt) values (204,109,3);

19

--insert into Factura insert into Factura (Serie,NrLeg,DataFact) values ('FACT1000','10001',to_date('16052012','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1008','10001',to_date('18052012','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1119','10001',to_date('22052011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1001','10002',to_date('16052011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1002','10005',to_date('16052011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1055','10018',to_date('29052011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1111','10014',to_date('16062011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT0054','10019',to_date('14082011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1044','10015',to_date('13072011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1032','10006',to_date('21082011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1132','10009',to_date('12072011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1094','10015',to_date('21092011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1083','10011',to_date('26022011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1356','10017',to_date('14122011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1567','10017',to_date('22112011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT2000','10013',to_date('12012012','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT2011','10010',to_date('27052011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1863','10011',to_date('26072011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1006','10017',to_date('29082011','DDMMYYYY')); insert into Factura (Serie,NrLeg,DataFact) values ('FACT1014','10018',to_date('22012012','DDMMYYYY'));

20

------insert into Angajat insert into Angajat values ('900','1900212160021','700','LACTA'); insert into Angajat values ('901','1934435160021','700','PATIS'); insert into Angajat values ('902','1900213424021','700','PATIS'); insert into Angajat values ('903','1900212162222','850','PANIF'); insert into Angajat values ('904','2880616131564','900','PANIF'); insert into Angajat values ('905','1860601167951','650','MEZEL'); insert into Angajat values ('906','1801212236578','900','MEZEL'); insert into Angajat values ('907','1910512168749','1100','BAUTR'); insert into Angajat values ('908','2880803123654','1144','BAUTA'); insert into Angajat values ('909','2691110155479','658','DULCE'); insert into Angajat values ('910','2830504698742','963','LEGFR'); insert into Angajat values ('911','1961212264789','800','VRAC'); insert into Angajat values ('912','1901221544895','800','PESTE'); insert into Angajat values ('913','1870307336896','700','PESTE'); insert into Angajat values ('914','1891115656899','700','CONS'); insert into Angajat values ('915','1881211644899','700','AUTO'); insert into Angajat values ('916','2851112160021','1500','ANIM'); insert into Angajat values ('917','2991112160021','1501','COPII'); insert into Angajat values ('918','1876543230021','800','HAINE'); insert into Angajat values ('919','2345662160021','700','ELCTR'); insert into Angajat values ('920','1900234560021','700','ELCTR'); insert into Angajat values ('921','2222212160931','850','SNACK'); insert into Angajat values ('922','1900212161234','1000','DETER'); insert into Angajat values ('923','1933212162221','800','CARNC'); insert into Angajat values ('924','1901111111021','1000','CARNP'); 21

----Manager_Magazin insert into Manager_Magazin values ('900','700'); insert into Manager_Magazin values ('901','600'); insert into Manager_Magazin values ('903','600'); insert into Manager_Magazin values ('906','650'); insert into Manager_Magazin values ('912','700'); insert into Manager_Magazin values ('920','700'); insert into Manager_Magazin values ('916','600'); insert into Manager_Magazin values ('908','755'); insert into Manager_Magazin values ('915','800'); insert into Manager_Magazin values ('923','650'); insert into Manager_Magazin values ('907','700'); insert into Manager_Magazin values ('923','600'); insert into Manager_Magazin values ('924','750');

----ProdCump insert into ProdCump values ('FACT1000','200',1,'900',null); insert into ProdCump values ('FACT1000','211',3,'902',null); insert into ProdCump values ('FACT1001','240',1,'901',null); insert into ProdCump values ('FACT1001','241',4,'903',null); insert into ProdCump values ('FACT1008','200',2,'906',null); insert into ProdCump values ('FACT1119','222',4,'907',null); insert into ProdCump values ('FACT1055','215',1,'907',null); insert into ProdCump values ('FACT1111','210',3,'908',null); insert into ProdCump values ('FACT0054','211',1,'909',null); insert into ProdCump values ('FACT0054','200',3,'909',null); insert into ProdCump values ('FACT1044','230',2,'910',null); insert into ProdCump values ('FACT1032','229',1,'902',null); insert into ProdCump values ('FACT1132','222',1,'921',null); insert into ProdCump values ('FACT1094','211',1,'922',null); insert into ProdCump values ('FACT1083','213',2,'920',null); insert into ProdCump values ('FACT1083','219',5,'917',null); insert into ProdCump values ('FACT1083','240',3,'923',null); 22

insert into ProdCump values ('FACT1356','205',29,'919',null); insert into ProdCump values ('FACT1567','205',6,'920',null); insert into ProdCump values ('FACT2000','216',1,'918',null); insert into ProdCump values ('FACT2000','233',3,'921',null); insert into ProdCump values ('FACT2011','218',1,'920',null); insert into ProdCump values ('FACT1863','212',2,'917',null); insert into ProdCump values ('FACT1006','220',1,'920',null); insert into ProdCump values ('FACT1006','221',1,'917',null); insert into ProdCump values ('FACT1006','200',5,'919',null); insert into ProdCump values ('FACT1006','211',7,'920',null); insert into ProdCump values ('FACT1014','239',4,'917',null);

23

7. Interogari pe baza de date 

Sa se afiseze alfabetic toate produsele care au cantitatea mai mare de 25. select Denumire, Cantitate from Produs where Cantitate>25 order by Denumire;



Sa se afiseze pentru fiecare magazin numarul de produse existente (daca sunt mai multe sau egale cu 2) si cantitatea acestora. select Magazin.Denumire, count(CodProdus) as "NUMAR PRODUSE", sum(Cantitate) as CANTITATE from Magazin inner join Produs on Magazin.CodMagazin=Produs.CodMagazin group by Magazin.Denumire having count(CodProdus)>=2;

24



Sa se afiseze persoanele care sunt angajati,dar nu sunt clienti si care sunt clienti si nu sunt angajati. select CNP, Nume from Persoana where CNP in ((select CNP from Angajat where CNP not in (select CNP from Client)) union all (select CNP from Client where CNP not in (select CNP from Angajat)));

25



Sa se afiseze persoanele care sunt angajati, dar nu sunt manageri de magazin. select CNP, Nume from Persoana where CNP in (select CNP from Angajat where IdAngajat not in (select IdAngajat from Manager_Magazin));



Sa se afiseze toate magazinele si toate produsele existente. select Magazin.Denumire, Magazin.Denumire from Magazin left outer join Produs on Magazin.CodMagazin=Produs.CodMagazin

26



Sa se afiseze pentru fiecare produs, furnizorii lui si pretul de intrare. select Denumire, Nume, PretInt from (Produs inner join ProdusFurnizor on Produs.CodProdus=ProdusFurnizor.CodProdus) inner join Furnizor on ProdusFurnizor.CodFurnizor=Furnizor.CodFurnizor;



Sa se afiseze toate datele despre clientii care locuiesc in Craiova. select * from Persoana where Adresa like '%Craiova%' and CNP in (select CNP from Client);

27



Sa se afiseze angajatii care nu sunt manageri de magazin si au salariul intre 700 si 1000. select Nume from Persoana where CNP in (select CNP from Angajat where Salariu between 700 and 1000 and IdAngajat not in (select IdAngajat from Manager_Magazin));

28



Sa se afiseze salariile angajatilor crescute cu 25% daca sunt mai mici de 700 si scazute cu 15% daca sunt mai mari de 3000. select Nume, case when Salariu<700 then Salariu*1.25 when Salariu>3000 then Salariu*0.85 else Salariu end as "SALARIU RECALCULAT" from Persoana inner join Angajat on Angajat.CNP=Persoana.CNP order by Nume;

29



Sa se afiseze salariul total al Managerilor de Magazin select Nume, CodMagazin, Salariu+Bonus as "SALARIU MANAGER MAGAZIN" from ((Persoana inner join Angajat on Angajat.CNP=Persoana.CNP) inner join Magazin on Angajat. CodMagazin=Magazin.CodMagazin) inner join Manager_Magazin on Angajat.IdAngajat=ManagerMagazin.IdAngajat;



Sa se afiseze valoarea totala a fiecarei facturi select Factura.Serie, DataFact, sum(Valoare) as "VALOARE FACTURA" from Factura, ProdCump where Factura.Serie=ProdCump.Serie group by Factura.Serie, DataFact;

30



Sa se afiseze angajatii care sunt manageri de magazin si au salariul mai mic de 1000. select Nume from Persoana where CNP in (select CNP from Angajat where Salariu < 1000 and IdAngajat in (select IdAngajat from Manager_Magazin));



Sa se afiseze pentru clientii care nu sunt din Craiova, numarul facturilor lor select Nume, count(Serie) as "NUMAR FACTURI" from (Persoana inner join Client on Persoana.CNP=Client.CNP) inner join Factura on Client.NrLeg=Factura.NrLeg where Adresa not like '%Craiova%' group by Nume;

31



Sa se afiseze pretul cel mai mic al unui produs la care se gaseste acesta indiferent de furnizor. SELECT Produs.denumire, subquery1.pretMin FROM Produs, (SELECT CodProdus, MIN(ProdusFurnizor.PRETINT) AS pretMin FROM ProdusFurnizor GROUP BY CodProdus) subquery1 WHERE subquery1.CodProdus = Produs.CodProdus;

32

8. Functii 1) Sa se verifice daca o persoana este angajat si daca da, sa se verifice daca are salariul mai mare decat o valoare data; functia returneaza un mesaj corespunzator. create or replace function ang_sal (f_Cnp in varchar, f_Salariu in float) return varchar is ok varchar(50); pers Angajat%rowtype; begin select * into pers from Angajat where CNP=f_Cnp; if pers.cnp is null then ok := 'Persoana nu este angajata'; else if pers.Salariu <= f_Salariu then ok := 'Persoana este angajata si are salariul mai mic'; else ok := 'Persoana este angajata si are salariul mai mare'; end if; end if; return ok; end;

Apelul functiei: begin dbms_output.put_line(ang_sal('2880616131564',700)); end;

Rezultatul obtinut:

33

2) Sa se calculeze cate produse cu denumirea f_denumire a cumparat clientul cu numele f_nume. create or replace function prd_cump (f_nume in varchar,f_denumire in varchar) return number is val number :=0; cant number; pers Client%rowtype; prod number; fact Factura%rowtype; begin select * into pers from Client where CNP in (select Persoana.CNP from Persoana where Nume=f_nume); select CodProdus into prod from Produs where Denumire=f_Denumire; for fact in (select * from Factura where NrLeg=pers.NrLeg) loop select sum(Cantitate) into cant from ProdCump where Serie=fact.Serie and CodProdus=prod; val := val + cant; end loop; return val; end; Apelul functiei: begin dbms_output.put_line('Cantitatea cumparata de Ciocolata Poiana, de catre Popescu Ion este: ' || prd_cump('Popescu Ion','Ciocolata Poiana')); end; Rezultatul obtinut:

34

9. Proceduri 1) Sa se creeze o procedura care actualizeaza preturile produselor, ca fiind minimul dintre preturile de la furnizori. Daca acest pret nu exista, noul pret este 10 create or replace procedure UpdatePret as pret float; one Produs%rowtype; begin for one in (select * from Produs) loop select min(PretInt) into pret from ProdusFurnizor where CodProdus=one.CodProdus; if pret is null then pret:=10; end if; update Produs set Produs.Pret=pret where Produs.CodProdus=one.CodProdus; end loop; end; Apelul procedurii: begin UpdatePret; end;

35

Rezultatul obtinut:

36

2) Sa se creeze o procedura care actualizeaza valoarea produselor din fiecare factura ca fiind pretul din tabela Produs * cantitatea. create or replace procedure ValoareProd as one ProdCump%rowtype; val float; pretp float; begin for one in (select * from ProdCump) loop val:=0; select Pret into pretp from Produs where CodProdus=one.CodProdus; val:=pretp * one.Cantitate; update ProdCump set Valoare=val where Serie=one.Serie and CodProdus=one.CodProdus; end loop; end;

Apelul procedurii: begin ValoareProd; end;

Rezultatul obtinut:

37

38

10. Trigger Sa se creeze un trigger care sa se declanseze in momentul in care salariul scade cu mai mult de 75%. create or replace trigger Salariu_update before update of Salariu on Angajat for each row when (new.Salariu
Rezultatul obtinut:

39

11. Bibliografie   

40

Suport de curs – Liana Stanescu Platforme de laborator – Cosmin Stoica Tutoriale SQL: http://www.tutorialspoint.com/sql/sql-using-joins.htm http://www.w3schools.com/sql/ http://www.sqlcourse.com/