[rllab4@fedora ~]$ show databases;
show: Doesn't look like nmh is installed. Run install-mh to do so.
[rllab4@fedora ~]$ sudo mysql
[sudo] password for rllab4:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.34 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| abc |
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
| xyz |
+--------------------+
7 rows in set (0.00 sec)
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student |
+-------------------+
1 row in set (0.01 sec)
mysql> use student;
Database changed
mysql> create table borrower(rollno int primary key,name varchar(20),dateofissue
date,nameofbook
-> varchar(20),status varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> create table fine(rollno int,foreign key(rollno) references borrower(rollno),returndate date,
-> amount int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into borrower values(1,'abc','2021-06-01','SEPM','I');
Query OK, 1 row affected (0.01 sec)
mysql> insert into borrower values(2,'xyz','2021-05-01','OOP','I');
Query OK, 1 row affected (0.01 sec)
mysql> insert into borrower values(3,'pqr','2021-06-15','DBMS','I');
Query OK, 1 row affected (0.01 sec)
mysql> insert into borrower values(4,'def','2021-06-30','DSA','I');
Query OK, 1 row affected (0.01 sec)
mysql> insert into borrower values(5,'lmn','2021-07-05','ADS','I');
Query OK, 1 row affected (0.01 sec)
mysql> select * from borrower;
+--------+------+-------------+------------+--------+
| rollno | name | dateofissue | nameofbook | status |
+--------+------+-------------+------------+--------+
| 1 | abc | 2021-06-01 | SEPM |I |
| 2 | xyz | 2021-05-01 | OOP |I |
| 3 | pqr | 2021-06-15 | DBMS |I |
| 4 | def | 2021-06-30 | DSA |I |
| 5 | lmn | 2021-07-05 | ADS |I |
+--------+------+-------------+------------+--------+
5 rows in set (0.00 sec)
Program:
mysql> delimiter $
mysql> create procedure calc_fine_lib(in roll int)
-> begin
-> declare fine1 int;
-> declare noofdays int;
-> declare issuedate date;
-> declare exit handler for SQLEXCEPTION select 'create table definition';
-> select dateofissue into issuedate from borrower where rollno=roll;
-> select datediff(curdate(),issuedate) into noofdays;
-> if noofdays>15 and noofdays<=30 then
-> set fine1=noofdays*5;
-> insert into fine values(roll,curdate(),fine1);
-> elseif noofdays>30 then
-> set fine1=((noofdays-30)*50) + 30*5;
-> insert into fine values(roll,curdate(),fine1);
-> else
->
-> insert into fine values(roll,curdate(),0);
-> end if;
-> update borrower set status='R' where rollno=roll;
-> end $
Query OK, 0 rows affected (0.01 sec)
mysql> call calc_fine_lib(1);
-> call calc_fine_lib(2);
-> call calc_fine_lib(3);
-> call calc_fine_lib(4);
-> call calc_fine_lib(5);
-> select * from fine;
-> call calc_fine_lib(1)$
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
+--------+------------+--------+
| rollno | returndate | amount |
+--------+------------+--------+
| 1 | 2023-10-12 | 41800 |
| 2 | 2023-10-12 | 43350 |
| 3 | 2023-10-12 | 41100 |
| 4 | 2023-10-12 | 40350 |
| 5 | 2023-10-12 | 40100 |
+--------+------------+--------+
5 rows in set (0.04 sec)
Query OK, 0 rows affected (0.04 sec)
mysql> call calc_fine_lib(2)$
Query OK, 0 rows affected (0.01 sec)
mysql> call calc_fine_lib(3)$
Query OK, 0 rows affected (0.01 sec)
mysql> select * from borrower;
-> $
+--------+------+-------------+------------+--------+
| rollno | name | dateofissue | nameofbook | status |
+--------+------+-------------+------------+--------+
| 1 | abc | 2021-06-01 | SEPM |R |
| 2 | xyz | 2021-05-01 | OOP |R |
| 3 | pqr | 2021-06-15 | DBMS |R |
| 4 | def | 2021-06-30 | DSA |R |
| 5 | lmn | 2021-07-05 | ADS |R |
+--------+------+-------------+------------+--------+
5 rows in set (0.00 sec)
mysql> select * from fine$
+--------+------------+--------+
| rollno | returndate | amount |
+--------+------------+--------+
| 1 | 2023-10-12 | 41800 |
| 2 | 2023-10-12 | 43350 |
| 3 | 2023-10-12 | 41100 |
| 4 | 2023-10-12 | 40350 |
| 5 | 2023-10-12 | 40100 |
| 1 | 2023-10-12 | 41800 |
| 2 | 2023-10-12 | 43350 |
| 3 | 2023-10-12 | 41100 |
+--------+------------+--------+
8 rows in set (0.00 sec)
