sciz6XL0

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; #създаваме хендрър с който казваме ако финишед е 1 да продължи изпълнението на кода,финишед ще стане едно като спре да се върти курсора
109SET found1=0; 
110open bolest_cursor; #отваряме курсора
111bolest_loop: LOOP # създаваме етикет bolest_loop и започваме цикъл loop 
112FETCH bolest_cursor into temp_drugs_id,temp_name; #записваме с курсора във временните променливи
113if(finished =1 )then leave bolest_loop; # ако финишед е едно тоест курсора е свършил, да напусне етикета и отива директно на ред 123
114SELECT 'finished ' as result1; #извежда finished като резултат
115end if;
116if(temp_name=bolest)then #ако името от конкретния ред на който е курсора съвпада с името подадено като параметър на процедурата влиза в if
117select drugs.name
118from drugs
119where drugs.id=temp_drugs_id;  #избира и извежда името на лекарството
120SET found1=1; #found  е 1 защото е намерил болестта и лекарството
121end if; 
122end Loop; #край на цикъла
123if(found1=0)then  #ако не е намерил болестта пише че няма болест като влиза в if
124select 'ne namerihme takava bolest' AS Result;
125end if;
126close bolest_cursor; #затваря курсора
127end;
128* # знак за край
129delimiter ;   
130set @bolest='temperatura';   #създаваме глобална променлива и и даваме стойност температура
131call task('temperatura');   #викаме процедурата