Untitled

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;