Untitled
unknown
plain_text
a year ago
1.6 kB
0
Indexable
Never
use t31143db; DROP TABLE IF EXISTS Fines; DROP TABLE IF EXISTS Borrowers; DROP PROCEDURE IF EXISTS fine; CREATE TABLE Borrowers ( roll_nos INT, names VARCHAR(20), date_of_issue DATE, name_of_books VARCHAR(20), statu VARCHAR(1) ); CREATE TABLE Fines (roll_nos INT, date_of_return DATE, amt INT,day INT); INSERT INTO Borrowers values ('1', 'Tanmay', '2022-09-28', 'C++', 'I'); INSERT INTO Borrowers values ('2', 'Ruturaj', '2022-08-28', 'Python', 'I'); INSERT INTO Borrowers values ('3', 'Sanika', '2022-07-28', 'Java', 'I'); INSERT INTO Borrowers values ('4', 'Dinesh', '2022-09-1', 'CP', 'I'); INSERT INTO Borrowers values ('5', 'Mrunmay', '2022-08-48', 'Leetcode', 'I'); INSERT INTO Borrowers values ('6', 'Bhadange', '2022-09-25', 'Hadoop', 'I'); Delimiter // CREATE PROCEDURE fine (IN roll_no INT, IN name_of_book VARCHAR(20)) BEGIN Declare days INT; Declare exit handler for NOT FOUND SELECT 'Not Found' as message; SELECT DATEDIFF (CURRENT_DATE, date_of_issue) into days from Borrowers WHERE roll_nos = roll_no and name_of_books = name_of_book; if days > 15 AND days < 30 THEN INSERT INTO Fines values (roll_no, CURRENT_DATE, (days -15) * 5,days); elseif days > 30 THEN insert into Fines values (roll_no, CURRENT_DATE, (days -30) * 50 + 15 * 5,days); END IF; UPDATE Borrowers Set statu = 'R' WHERE roll_nos = roll_no and name_of_books = name_of_book; END // Delimiter ; call fine ('2', 'Python'); call fine('2','asd'); SELECT * from Borrowers; SELECT * from Fines;