mHTMRUs7

1drop database if exists medicine;
2create database medicine;
3use medicine;
4
5create table drugs(
6id int primary key auto_increment,
7name varchar(100) not null,
8vid varchar(30) not null,
9doza decimal not null,
10price double(4,2) not null,
11description tinytext not null);
12
13create table sickness(
14drugs_id int,
15constraint foreign key(drugs_id) references drugs(id),
16sickness_name varchar(100));
17
18create table customer(
19id int primary key auto_increment,
20name varchar(100) not null);
21
22create table history(
23withRecipe boolean default 0,
24recipe tinytext,
25drugs_id int,
26customer_id int,
27constraint foreign key(drugs_id) references drugs(id),
28constraint foreign key(customer_id) references customer(id));
29
30create table pharmacist(
31id int primary key auto_increment,
32name varchar(100) not null);
33alter table drugs
34add pharmacist_id int;
35alter table drugs
36add constraint foreign key(pharmacist_id) references pharmacist(id);
37
38insert into pharmacist(name)
39values('Kiril Dinkov'),
40		('Lubo Dimitrov');
41
42insert into drugs(name,vid,doza,price,description,pharmacist_id)
43values('aspirin','hapche',1,1.50,'vzima se po 2 tabletki na den sled qdene',1),
44('diklak','mazilo',50,6.70,'maje se na mqsto kadeto te boli',1),
45('mialgama','hapche',0.5,5.20,'vzima se po 1 tabletki na den  sutrin i vecher',1),
46('fervex','prah',1,0.5,'raztwarq v topla voda 250ml',1);
47
48insert into customer(name)
49values('Pencho Krustev'),
50('Nikola Vapcarov'),
51('Veronika Galinova'),
52('Vqra Kirilova'),
53('Ivan Petrov');
54
55insert into history(withRecipe,recipe,drugs_id,customer_id)
56values(0,null,2,1),
57(0,null,1,2),
58(0,null,4,2),
59(1,' 20 tabletki',3,3),
60(1,'20 tabletki',1,3);
61
62insert into sickness(drugs_id,sickness_name)
63values(1,'glavobolie'),
64(1,'bolki v stomaha'),
65(1,'temperatura'),
66(2,'bolki v stavite i muskulite'),
67(3,'koliki'),
68(4,'nastinka');
69
70#vtora
71select customer.name as Customer
72from customer join drugs
73on customer.id in(
74					select history.customer_id
75                    from history
76                    where history.drugs_id = drugs.id and drugs.name='aspirin');
77                    
78#treta
79select count(drugs.id) as broi, drugs.vid
80from drugs
81group by drugs.vid;
82
83#chetvurta
84select drugs.name as drug, pharmacist.name as pharmacist
85from drugs right join pharmacist
86on drugs.pharmacist_id = pharmacist.id;
87
88#peta
89select customer.name,
90count(history.customer_id) as broikupeniLekarstwa
91from customer join history
92ON customer.id=history.customer_id
93group by id;
94
95#shesta
96use medicine;
97drop procedure if exists task;
98delimiter *
99create procedure task(in bolest varchar(100))
100begin
101declare found1 int default 0;
102declare finished int;
103declare temp_drugs_id int default 0;
104declare temp_name varchar(100);
105declare bolest_cursor cursor for
106select drugs_id,sickness_name
107from sickness;
108declare continue handler for not found set finished=1;
109SET found1=0;
110open bolest_cursor;
111bolest_loop: LOOP
112FETCH bolest_cursor into temp_drugs_id,temp_name;
113if(finished =1 )then leave bolest_loop;
114SELECT 'finished ' as result1;
115end if;
116if(temp_name=bolest)then
117select drugs.name
118from drugs
119where drugs.id=temp_drugs_id;
120SET found1=1;
121end if;
122end Loop;
123if(found1=0)then
124select 'ne namerihme takava bolest' AS Result;
125end if;
126close bolest_cursor;
127end;
128*
129delimiter ;
130set @bolest='temperatura';
131call task('temperatura');