Untitled
unknown
plain_text
2 years ago
1.2 kB
1
Indexable
Never
drop table Fine; drop table Borrower; set @amt = 0; Create Table Borrower(roll_no int, name varchar(60), date_of_issue date, name_of_book varchar(60), status varchar(20)); Create table Fine(roll_no int, return_date date, amt int); Alter table Borrower add primary key(roll_no,name_of_book); alter table Fine add foreign key(roll_no) references Borrower(roll_no); Insert into Borrower values(1,'Rutu', '2021/08/13', 'How to', 'I'); Insert into Borrower values(2,'Tanish', '2022/08/01', 'I am good', 'I'); Insert into Borrower values(3,'Tanmya', '2018/08/09', 'Python is good', 'I'); select * from Borrower; drop procedure calc; delimiter @@ create procedure calc( In b_name varchar(30) ) Begin declare amt int; declare roll int; select DATEDIFF(current_date, date_of_issue),roll_no into amt, roll from Borrower where name_of_book = b_name; if amt>15 and amt<=30 then insert into Fine values(roll,current_date,(amt-15)*5); elseif amt>30 then insert into Fine values(roll,current_date,(amt-30)*50 + 15*5); end if; update Borrower set status='R' where roll_no=roll; End@@ delimiter ; call calc('How to'); call calc('I am good'); select * from Fine; select * from Borrower;