DyViwVmS

1if exists(select * from sys.databases where name = 'cabinet_medical')
2	print 'Database already exists'
3
4create database cabinet_medical
5use cabinet_medical
6
7if object_id(N'dbo.pacient', N'U') IS NOT NULL
8begin
9    print 'Table Exists'
10end
11
12create table pacient (
13	cnp VARCHAR(20) NOT NULL PRIMARY KEY ,
14	nume NVARCHAR(255) NOT NULL,
15	adresa NVARCHAR(255) NOT NULL,
16	
17);
18
19if object_id(N'dbo.medicament', N'U') IS NOT NULL
20begin
21    print 'Table Exists'
22end
23
24create table medicament (
25	cod_m INT NOT NULL PRIMARY KEY,
26	denumire_m NVARCHAR(255) NOT NULL,
27	cantitate_m NVARCHAR(255) NOT NULL,
28	
29);
30
31
32if object_id(N'dbo.reteta', N'U') IS NOT NULL
33begin
34    print 'Table Exists'
35end
36
37create table reteta (
38	cod_m INT NOT NULL FOREIGN KEY (cod_m) REFERENCES medicament(cod_m),
39	cnp VARCHAR(20) NOT NULL FOREIGN KEY (cnp) REFERENCES pacient(cnp),
40	cant INT NOT NULL,
41	pret_unitar FLOAT NOT NULL,
42
43);
44
45
46insert into medicament (cod_m, denumire_m, cantitate_m) values (112233, 'Spirulina', 3);
47insert into medicament (cod_m, denumire_m, cantitate_m) values (233112, 'Penicilina', 5);
48insert into medicament (cod_m, denumire_m, cantitate_m) values (100332, 'Paracetamol', 13);
49insert into medicament (cod_m, denumire_m, cantitate_m) values (333221, 'Eurespal', 23);
50insert into medicament (cod_m, denumire_m, cantitate_m) values (662123, 'Zinat', 55);
51
52insert into pacient (cnp, nume, adresa) values ('1970127284574', 'Flavian Gyenge', 'Str. Florilor, ap. 13');
53insert into pacient (cnp, nume, adresa) values ('1970127283477', 'Miroslav Klose', 'Str. Victoriei, ap. 15');
54insert into pacient (cnp, nume, adresa) values ('1970127284555', 'Stefania Malina', 'Str. Victoriei, ap. 15');
55insert into pacient (cnp, nume, adresa) values ('2750806457891', 'Gutui Florica','125664,Alexandria');
56insert into pacient (cnp, nume, adresa) values ('2750806457892', 'Gutui Florica','125664,Alexandria');
57insert into pacient (cnp, nume, adresa) values ('2750806457893', 'Gutui Florica','125664,Alexandria');
58
59insert into reteta (cod_m, cnp, cant, pret_unitar) values (112233, '1970127284574', 4, 5.6);
60insert into reteta (cod_m, cnp, cant, pret_unitar) values (233112, '1970127283477', 5, 5.6);
61insert into reteta (cod_m, cnp, cant, pret_unitar) values (100332, '2750806457891', 7, 4.9);
62insert into reteta (cod_m, cnp, cant, pret_unitar) values (112233, '2750806457893', 3, 4.5);
63insert into reteta (cod_m, cnp, cant, pret_unitar) values (112233, '1970127284555', 5, 4.1);
64
65select * from medicament
66select * from pacient
67select * from reteta
68
69
70/* Determinati cat se consuma din fiecare medicament */
71select medicament.denumire_m, SUM(reteta.cant) as 'Cantitate Medicament' from medicament
72JOIN reteta on medicament.cod_m = reteta.cod_m
73group by medicament.denumire_m
74order by medicament.denumire_m
75
76/* Procedura care determina consumul pentru medicamentul 112233 */
77go
78create procedure ConsumTotalMedicament
79as
80	select medicament.denumire_m, SUM(reteta.cant) as 'Cantitate Medicament' from medicament
81	JOIN reteta on medicament.cod_m = reteta.cod_m
82	where medicament.cod_m = 112233
83	group by denumire_m
84go
85
86exec ConsumTotalMedicament
87
88-- 4. Medicamentul cu pretul maxim
89select MAX(reteta.pret_unitar) as Pret from reteta
90 JOIN medicament on reteta.cod_m = medicament.cod_m
91
92-- 5. Determinati numarul de retete pe fiecare medicament
93select medicament.denumire_m, COUNT(reteta.cod_m) as 'Numar retete' from medicament
94JOIN reteta on medicament.cod_m = reteta.cod_m
95group by medicament.denumire_m