Bază de date - farmacie

CREARE TABELE

create table furnizori(codf number(3) constraint co_pk primary key,denumire varchar2(20),adresa varchar2(30),localitate varchar2(20),telefon varchar2(10))

create table categorie_prod(id_cat number(2) constraint iddcat_pk primary key,descriere varchar2(20))

create table produse1(cod number(3) constraint codi_pk primary key,denumire varchar2(30),cantitate number(4),id_cat number(2) references categorie_prod(id_cat),data_expirarii date,caracteristici varchar(60))

create table aprovizionari(nr_ap number(2) constraint nrap_pk primary key,data date,cod_prod number(3),cod_furniz number(3),cantitate number(4),constraint cod1_fk foreign key(cod_prod) references produse1(cod),constraint cod2_fk foreign key(cod_furniz) references furnizori(codf))

create table angajati(cod_ang number(2) constraint codd_pk primary key,cod_ang1 number(2),nume varchar2(20),prenume varchar2(20),functia varchar2(10),adresa varchar2(60),telefon varchar2(10),data_ang date,salariu number(5),constraint cc_fk foreign key(cod_ang1) references angajati(cod_ang))

create table istoric_pret(cod_prod number(3),data_i date,data_f date,pret number(4),tva number(1,2),constraint ccc_pk primary key(cod_prod,pret),constraint con_fk foreign key(cod_prod) references produse1(cod))

create table depozit(cod_dep number(1) constraint pp_pk primary key,adresa varchar2(40),tel varchar2(10),suprafata varchar2(10),capacitate varchar2(10),id_cat number(2),cod_ang number(2),constraint fj_fk foreign key(id_cat) references categorie_prod(id_cat),constraint dd_fk foreign key(cod_ang) references angajati(cod_ang))

create table client(cnp number(15) constraint gg_pk primary key,nume varchar2(20),prenume varchar2(20),adresa varchar2(60),tel varchar2(10),valoare_cump number(4),data_inregistrare date,discount number(2,2))

create table comanda(nr_c number(5) constraint yrg_pk primary key,cod_prod number(3),cnp number(15),cantitate number(4),val_comanda number(5),mod_plata varchar2(10),data_comanda date,constraint qw_fk foreign key(cod_prod) references produse1(cod),constraint tv_fk foreign key(cnp)references client(cnp))

INTRODUCEREA DATELOR IN TABELE

insert into furnizori values(23,'sc medica sa',' bd teilor 3','oradea','0743727992')

insert into furnizori values(719,'sc krank srl','blumen 1','berlin','030 20165740')

insert into furnizori values(12,'sc santax srl',' bd decebal 7','deva','0736258912')

insert into furnizori values(5,'sc kobal sa','grigstrasse 9','hamburg','031 70164710')

insert into furnizori values(211,'sc vie srl','bd soleil 11','paris','050 55165729')

insert into categorie_prod values(1,'medicamente')

insert into categorie_prod values(2,'cosmetice')

insert into categorie_prod values(3,'produse naturiste')

insert into categorie_prod values(4,'suplim alimentare')

insert into categorie_prod values(5,'produse bebe')

insert into produse1 values(113,'coldrex',2000,1,'09-jan-13','raceala si gripa')

insert into produse1 values(4,'augumentin',600,1,'20-mar-12','antibotic,pentru infectii')

insert into produse1 values(14,'supradyne',1600,4,'30-may-11','complex vitamine')

insert into produse1 values(53,'suzeta',300,5,'15-oct-15','diferite culori')

insert into produse1 values(31,'nivea repair',200,2,'05-dec-14','sampon anti-matreata,reparator')

insert into produse1 values(17,'ceai de tei',400,3,'19-sep-12','ceai calmant')

insert into produse1 values(42,'multifruty',1000,3,'22-jul-13','sirop de fructe de padure')

insert into produse1 values(9,'pampers',2700,5,'10-jun-15','scutec de unica folosinta')

insert into produse1 values(452,'dove',900,2,'27-feb-13','sapun hidratant')

insert into produse1 values(119,'spirulina',500,3,'30-aug-11','substante hranitoare')

insert into produse1 values(24,'milupa',400,5,'5-jan-11','lapte praf')

insert into aprovizionari values(21,'15-jan-11',17,211,500)

insert into aprovizionari values(31,'22-dec-10',53,5,300)

insert into aprovizionari values(10,'30-sep-10',113,719,2500)

insert into aprovizionari values(45,'4-nov-10',14,23,1800)

insert into aprovizionari values(2,'3-apr-10',31,211,12)

insert into angajati values(10,NULL,'coman','alexandru','farm-sef','arini 13','0741214679','12-apr-07',2200)

insert into angajati values(9,10,'marin','ana','asistent','dacia 18','0732494453','21-sep-10',900)

insert into angajati values(7,10,'toma','alina','asistent','macilor 4','0731494673','12-jul-10',1000)

insert into angajati values(5,10,'niculai','andrei','farmacist','paris 1','0734911088','31-jul-08',1600)

insert into angajati values(2,10,'marcu','marta','farmacist','traian 11','0766491159','10-oct-07',1800)

insert into angajati values(3,10,'lalescu','adina','farmacist','corvin 92','0761454370','04-mar-09',1400)

insert into istoric_pret values(4,'01-jan-11','15-may-11',5,0.19)

insert into istoric_pret values(14,'01-jan-11','31-may-11',10,0.19)

insert into istoric_pret values(53,'11-dec-10','31-mar-11',15,0.24)

insert into istoric_pret values(42,'04-nov-10','30-apr-11',12,0.19)

insert into istoric_pret values(9,'30-nov-10','16-jun-11',9,0.24)

insert into istoric_pret values(17,'10-jan-11','01-aug-11',6,0.19)

insert into istoric_pret values(452,'03-jan-11','15-mar-11',20,0.19)

insert into istoric_pret values(119,'20-dec-10','01-may-11',7,0.24)

insert into istoric_pret values(24,'15-oct-10','16-apr-11',11,0.19)

insert into istoric_pret values(113,'01-dec-11','07-jul-11',8,0.19)

insert into istoric_pret values(31,’ '04-dec-11','17-jul-11',14,0.19)