LIBRARY INFORMATION SYSTEM

create table library(cardno number(10),bookname varchar2(15),author
varchar2(10),subject varchar2(10),no_of_copies number(5),issue date,return
date,fine number(3));
insert into library values(101,'data structure','Abbas','computer',4,'1-jan-10','20-
jan-10',null);
insert into library values(102,'Visual Basic','Abbas','computer',1,'20-jan-10','25-
jan10',null);
insert into library values(103,'Mathilukal','Basheer','Novel',5,'25-jan-10','30-
jan-10',null);
insert into library values(104,'matrix','R.A Japan','Mathematics',2,'1-feb-10','10-
feb-10',null);
insert into library values(105,'Alkemist','Paulocoyle','Novel',3,'10-feb-10','20-
feb-10',null);
insert into library values(106,'James Bond','Fleming','Detective',1,'20-feb-10','28-
feb-10',null);
insert into library values(107,'Romeo Juliet','Shakespere','love story',1,'28-feb-10','20-
march-10',null);
insert into library values(108,'Warpeace','Tolstoy','Socialism',2,'20-march-10','25-
march-10',null);
insert into library values(109,'DBMS','Abbas','computer',2,'25-march-10','27-march-10',null);
insert into library values(110,'Microbiology','Ushakumari','Botany',1,'27-march-10','30-
march-10',null);
declare
cursor c1 is
select*from library order by cardno;
vlib c1%rowtype;
mdate int;
begin
dbms_output.put_line('date');
dbms_output.put_line('---------------------------');
dbms_output.put_line('farook college calicut');
dbms_output.put_line('cardno bookname author subject no_of_copies issue return
fine');
open c1;
loop
fetch c1 into vlib;
exit when c1%notfound;
mdate:=vlib.return-vlib.issue;
if(mdate<=8) then
vlib.fine:=2;
else if(mdate<=15) then
vlib.fine:=5;
else
vlib.fine:=10;
end if;
end if;
update library set fine=vlib.fine where cardno=vlib.cardno;
dbms_output.put_line('vlib.cardno,vlib.bookname,vlib.author,vlib.subject,vlib.no_of
_copies,vlib.issue,vlib.return,vlib.fine');
end loop;
dbms_output.put_line('--------------------------------------------------------------------------
---------------------------');
close c1;
end;
/

OUTPUT

------- --------------- ---------- ---------- ------------ --------- --------- ---------------- ----
CARDNO BOOKNAME AUTHOR SUBJECT NO_OF_COPIES
ISSUE RETURN FINE
------- --------------- ---------- ---------- ------------ --------- --------- --------- ------- ----
101 data structure Abbas computer 4
01-JAN-10 20-JAN-10 10
102 Visual Basic Abbas computer 1
20-JAN-10 25-JAN-10 2
103 Mathilukal Basheer Novel 5
25-JAN-10 30-JAN-10 2
105 Alkemist Paulocoyle Novel 3
10-FEB-10 20-FEB-10 5
106 James Bond Fleming Detective 1
20-FEB-10 28-FEB-10 2
107 Romeo Juliet Shakespere love story 1
28-FEB-10 20-MAR-10 10
108 Warpeace Tolstoy Socialism 2
20-MAR-10 25-MAR-10 2
109 DBMS Abbas computer 2
25-MAR-10 27-MAR-10 2
110 Microbiology Ushakumari Botany 1
27-MAR-10 30-MAR-10 2
------- --------------- ---------- ---------- ------------ --------- --------- --------- -------