-- 1. Crearea si instantiarea bazei de date
drop table medicament;
create table medicament
(
COD_M int primary key,
DENUMIRE_M varchar(30) not null,
CANTITATE_M int not null
);

drop table reteta;
create table reteta
(
COD_M int not null,
CNP varchar(13) not null,
CANT int not null,
PRET_UNITAR int not null,
--cheie multipla pentru a nu putea introduce pentru acelas pacient, de doua ori acelas medicament
primary key(COD_M,CNP)
);

drop table pacient;
create table pacient
(
CNP varchar(13) primary key,
NUME varchar(30) not null,
ADRESA varchar(50) not null
);

--constrangere pentru a nu putea atribui o reteta unui pacient care nu exista
alter table reteta add constraint FK_reteta foreign key (CNP) references pacient(CNP);
--constrangere pentru a nu putea scrie intr-o reteta un medicament care nu exista
alter table reteta add constraint FKs_reteta foreign key (COD_M) references medicament(COD_M);

insert into medicament values (1,'Histamina',200);
insert into medicament values (2,'Aspirina',400);
insert into medicament values (3,'Paracetamol',300);
insert into medicament values (4,'Algocalmin',500);
insert into medicament values (5,'Abacavir',100);
insert into medicament values (6,'Antiseptice',250);
insert into medicament values (7,'Tertensif',150);
insert into medicament values (8,'Ibuprofen',50);
insert into medicament values (9,'Corlentor',600);
insert into medicament values (10,'Insulina', 170);

insert into pacient values ('2940321334521','Leyla Mills','Craiova');
insert into pacient values ('1930401049015','Bobby Huang','Bucuresti');
insert into pacient values ('1970726523846','Adalynn Francis','Craiova');
insert into pacient values ('1920929151023','Maxwell Morales','Craiova');
insert into pacient values ('2980123028666','Ally Osborn','Craiova');

insert into reteta values (1,'2940321334521',100,50);
insert into reteta values (5,'2940321334521',300,100);
insert into reteta values (7,'2940321334521',100,70);
insert into reteta values (10,'2940321334521',50,50);

insert into reteta values (1,'1930401049015',200,100);
insert into reteta values (2,'1930401049015',200,70);
insert into reteta values (7,'1930401049015',150,105);
insert into reteta values (8,'1930401049015',50,50);
insert into reteta values (9,'1930401049015',600,1200);
insert into reteta values (10,'1930401049015',170,340);

insert into reteta values (3,'1970726523846',300,150);
insert into reteta values (6,'1970726523846',250,500);

insert into reteta values (4,'1920929151023',500,2000);
insert into reteta values (7,'1920929151023',50,35);
insert into reteta values (8,'1920929151023',25,25);

insert into reteta values (1,'2980123028666',200,100);
insert into reteta values (3,'2980123028666',150,75);
insert into reteta values (5,'2980123028666',30,10);
insert into reteta values (6,'2980123028666',100,250);
insert into reteta values (8,'2980123028666',10,10);

select * from medicament;
select * from pacient;
select * from reteta;

-- 2. Determinati cat costa medicamentele pe fiecare pacient

select CNP,SUM(PRET_UNITAR) as Cost_Medicamente from reteta group by CNP;

-- 3. Procedura care determina costul medicamentelor pentru bolnavul X

select CNP,SUM(PRET_UNITAR) as Cost_Medicamente from reteta where CNP='1930401049015' group by CNP;

-- 4. Bolnavi cu cel putin 3 medicamente

select CNP from reteta group by CNP having COUNT(CNP)>=3;

-- 5. Determinati numarul de medicamente pe fiecare bolnav

select CNP,COUNT(CNP) as Numar_Medicamente from reteta group by CNP;