PROIECT BAZE DE DATE

PROIECT BAZE DE DATE

Aceasta aplicatie este creata pentru procesul de programari intr-o clinica privata.

Baza de date este alcatuita din 5 tabele: Solicitanti, Operatori, Programari, Servicii, Randprog.

Tabela SOLICITANTIi contine datele inregistrate despre persoanele care solicita o programare: codul solicitantului, numele, adresa, telefonul, varsta, zona.

Tabela OPERATORI contine informatiile despre angajatii care stabilesc programarile: codul operatorului, nume, vechime, salariu,sexul.

Tabela PROGRAMARI cuprinde datele necesare stabilirii programarilor: nr programarii, codul operatorului care a stabilit programarea si data la care s-a facut cererea de programare.

Tabela SERVICII     cuprinde detaliile despre investigatiile pe care clinica este imputernicita sa le ofere : codul serviciului, denumirea, sectia de care apartine si durata necesara acestei investigatii.

Tabela RANGPROG prezinta pacientii cu fiecare tip de investigatie pe care o solicita.Aceasta contine pretul pe fiecare serviciu oferit si data programarii.

SOLICITANTI

OPERATORI

PROGRAMARI

SERVICII

RANDPROG

A.

Create table SOLICITANTI

Cod-sol number(3) primary key,

Nume varchar2(35),

Adresa varchar2(50),

Telefon number(10),

Varsta number(2),

Zona varchar2(10)

Create table OPERATORI

Cod-op number(3) primary key,

Nume varchar2(35),

Vechime number(2),

Salariul number(5),

Sex varchar2(1)

Create table PROGRAMARI

Nr-prog number(5) primary key,

Cod-sol number(3) references SOLICITANTI (cod-sol),

Cod-op number(3) references OPERATORI (cod-op),

Data date

Create table SERVICII

Cod-serv number (3) primary key,

Den varchar2(30),

Sectie varchar2(30),

durata number(5)

Create table RANDPROG

Nr-prog number(5) references PROGRAMARI (nr-prog),

Cod-serv varchar2(3) references SERVICII (cod-serv),

Pret number(5,2),

Nrpers number(2),

Data-p date

Insert into SOLICITANTI values('100','ION ADRIANA' ,'str. Biruintei,nr.15, Timisoara',0744100000,'30','Banat');

Insert into SOLICITANTI values('101','DAN IOANA','str.Silva,nr 225,Bacau',0722000000,'50','Moldova');

Insert into SOLICITANTI values ('102','ZAMFIR ADRIAN','str.Alba,nr100,Vaslui', 0745111222,'50','Moldova');

Insert into SOLICITANTI values ('103','NITU CATALIN','str.Pacii, nr 45,Bucuresti', 0743555666,'55','Muntenia');

Insert into SOLICITANTI values ('104','DEDU NICOLETA','str.Teiului, nr 321,Botosani',0721333444,'37','Moldova');

Insert into OPERATORI values('200','Petrache Ion','1',900,'M');

Insert into OPERATORI values ('201','Manta Rodica','2',1300,'F');

Insert into OPERATORI values ('202','Drumea Daniel','2',1400,'M');

Insert into OPERATORI values ('203','Radoi Alina','3',1600,'F');

Insert into OPERATORI values ('204','Milea Gabriela','1',800,'F');

Insert into PROGRAMARI values('300',' 100', '201','14-apr-2007');

Insert into PROGRAMARI values ('301','100', '201','17-apr-2007');

Insert into PROGRAMARI values ('302','101','201','20-apr-2007');

Insert into PROGRAMARI values ('303','102','201','29-apr-2007');

Insert into PROGRAMARI values ('304','103','201','05-may-2007');

Insert into PROGRAMARI values ('305','104','201','10-may-2007');

Insert into SERVICII values('1','tomogarfie','radiologie','10');

Insert into SERVICII values('2','mamografie','radiologie','10');

Insert into SERVICII values('3','EKG','cardiologie','15');

Insert into SERVICII values('4','EEG','neurologie','25');

Insert into SERVICII values('5'','endoscopie','gastroenterologie','15');

Insert into SERVICII values('6','conoscopie','radiologie','75');

Insert into RANDPROG values('300', 1, 300, 1,'16-may-2007');

Insert into RANDPROG values('300', 3, 50, 2,'16-apr-2007');

Insert into RANDPROG values('301', 1, 300, 1,'20-may-2007');

Insert into RANDPROG values('302', 4, 70, 1,'25-may-2007');

Insert into RANDPROG values('303', 2, '130', '1','01-jun-2007');

Insert into RANDPROG values('304', 5, '200.99', 2,'10-jun-2007');

Insert into RANDPROG values('305', '6', '300.55', 1,'15-jun-2007');

B.

1. Sa se redenumeasca tabela SERVICII in ARTICOLE

Alter table SERVICII

rename to ARTICOLE;

2. Sa se adauge in tabela OPERATORI coloana Studii, avand tipul varchar2(30)

Alter table OPERATORI

add(studii varchar2 (30));

3. Sa se adauge in tabela RANDPROG o restrictie de integritate avand numele Verificapret aspura campului pret, care sa nu permita introducerea de valori negative

Alter table RANDPROG

add(constraint verificapret check(pret>0));

4. Sa se modifice proprietatile campului nrpers din tabela RANDPROG, altfel incat lungimea acestuia sa fie de 1.

Alter table RANDPROG

modify(volum number(1));

5. Sa se dezactiveze restrictia de integritate Verificapret din tabela RANDPROG.

Alter table RANDPROG

disable constraint verificapret;

6. Sa se stearga coloana studii din tabela OPERATORI

Alter table OPERATORI

drop column studii;

C.

1. Sa se stearga seviciile ale caror durate este mai mare de 10min si mai mici decat 20 min.

Delete from SERVICII

where durata>10 and durata<20;

Sau

Delete from SERVICII

where durata between 10 and 20;

2. Sa se stearga programarilele efectuate in luna aprilie

Delete from PROGRAMARI

where data<to_date('01042007','ddmmyyyy')

and data>=to_date('01052007','ddmmyyyy');

Sau

Delete from PROGRAMARI

where to_char(data,'mm')='04';

3. Sa se stearga programarile catre solicitantii cu codul 100-101

Delete from PROGRAMARI

where cod-sol=100

or cod-sol=101;

Sau

Delete from PROGRAMARI

where cod-sol in(100,101);

4. Sa se majoreze cu 15% salariile angajatilor care au un salariu mai mic decat 900.

Update OPERATORI

set salariu=salariu*1.15

where salariu<900;

5. In tabela OPERATORI sa se modifice vechimea operatorului cu cod-op egal cu 204 stabilind-o 2 ani.

Update OPERATORI

set vechime=2

where cod-op=204;

D.

1. Sa se rotunjeasca la o zecimala preturile produselor comandate

Select cod-serv, round(pret,1) from RANDPROG;

2. Sa se selecteze serviciile al caror pret este mai mare decat media preturilor

Select cod-serv from RANDPROG

where pret>(select avg(pret)from RANDPROG);

3. Sa se scrie decodat sexul operatorilor

Select decode(sex,'M','masculin','F','feminin','necunoscut')

from OPERATORI;

4. Sa se selecteze datele la care pretul a fost maxim.

Select data-p from RANDPROG

where pret=(select max(pret)from RANDPROG)

5.Sa se selecteze numai solicitantii din Bucuresti

Select * from SOLICITANTI

Where upper(adresa) like'%BUCURESTI%'

6.Sa se calculeze suma pe care trebuie sa o achite fiecare solicitant

Select servicii.den , randprog.nrpers , randprog.pret

Randprog.nrpers*randprog.pret as plata

From servicii, randprog

Where servicii.cod-serv=randprog.cod-serv

7. Sa se selecteze programarile din luna septembrie facute pentru 2 servicii:

Select c.nr-prog from programari c, RANDPROG r

where c.nr-prog=r.nr-prog

and to_char(data,'mm')='09'

group by c.nr-prog

having count(*)=2;

8. Sa se afiseze codul serviciului, denumirea si nr pers corespunzatoare:

Select p.cod-serv, p.den, r.nrpers

from SERVICII p, RANDPROG r

where p.cod-serv=r.cod-serv(+);

9. Sa se afiseze produsele care au durata intre 10 si 50 minute fara cele care au durata de 15 si 30 de unitati

Select * from SERVICII

where stoc between 10 and 50

minus

select * from SERVICII

where stoc in(15, 30);

10. Sa se afiseze valoarea totala a programarilor din fiecare luna

Select to_char(data,'mm'), sum(volum*pret)

from PROGRAMARI c, RANDPROG r

where c.nr-prog=r.nr-prog

group by to_char(data,'mm');

11.Sa se afiseze numarul total de programari

Select count(nr-prog) nr-programari from Programari

12.Sa se afiseze denumirea serviciului si durata lui:

Select ,SERVICIUL : ' || initcap(den) ||' dureaza' || durata

From Servicii

13.Afisati perioada de timp corespunzatoare in saptamani intre data in care s-a facut programarea si data curenta

Select nr-prog, round((sysdate-data)/7) saptamani from programari

14. Sa se afiseze serviciile programate cu pretul cel mai mic.

Select p.den, r.pret

From servicii p, randprog r

Where p.cod-prog=r.cod-prog

And r.pret=(select min(randprog.pret)from randprog);

15.Sa se selecteze denumirea serviciilor, valoarea totala de plata si nr de programari pentru serviciile solicitate de maxim 3 ori care au valoarea totala diferita de 200 si 700.

Select p.den,sum(r.nrpers*r.pret),count(r.nr-prog) nrprogramari

from SERVICII p, RANDPROG r

where p.cod-serv=r.cod-serv

group by p.den

having count(r.nr-prog)<=3

intersect

select p.den, sum(r.nrpers*r.pret),count(r.nr-prog) nrprogramari

from SERVICII p, RANDPROG r

where r.cod-serv=p.cod-serv

group by p.den

having sum(r.nrpers*r.pret) not in(200, 700);

E.

1. Sa se realizeze o tabela virtuala care sa contina numai serviciile din sectia de radiologie

Create view SERV_RAD_V

as select cod-serv COD-SERVICIU, den DENUMIRE, sec SECTIE

from SERVICII

where sectie='radiologie';

2.Sa se realizeze o tabela virtuala cu toti solicitantii din Moldova. Tabela virtuala nu va putea fi actualizata

Create view SOLICITANTI_ZONA_V

as select*from SOLICITANTI

where upper(zona)='MOLDOVA'

with read only;

3. Sa se creeze un index pe tabela SOLICITANTI pe coloana nume:

Create index SOLICITANTI_NUME_IDX on SOLICITANTI(nume);

4. Sa se creeze un sinonim pentru tabela RANDPROG

Create synonym detalii_comanda for RANDPROG

5. Sa se creeze o secventa pentru asigurarea unicitatii cheii primare din tabela PROGRAMARI. Aceasta va incepe cu valoarea 306 si se termina cu valoarea 400, avand pasul 1.

Create sequence seq_nrprog

start with 306

increment by 1

maxvalue 400 nocycle;