create table Student(stud_lib_card_no number(10) primary key, stud_name varchar2(50) not null, class varchar2(50) not null);
create table Book(Book_id number(10) primary key, category varchar2(50), title varchar2(150), author varchar2(50), price number(6), status varchar2(20));
create table Book_issue( Book_id references Book, stud_lib_card_no references Student, issue_Date date, return_date date, fine_charged number(5,2), fine_paid number(5,2) );
2-
Insert into student values(1,'Ramesh', 'MCA-1-B');
Insert into student values(2,'Suresh', 'MCA-1-B');
Insert into student values(3,'Kavesh', 'MCA-1-B');
Insert into student values(4,'Rajesh', 'MCA-1-B');
Insert into student values(5,'Naresh', 'MCA-1-B');
Insert into book values(1,'Computers', 'ABC', 'LEE', 100, 'Issued');
Insert into book values(2,'Computers', 'DEF', 'CEE', 200, 'Issued');
Insert into book values(3,'Computers', 'FGH', 'BEE', 300, 'Issued');
Insert into book values(4,'Computers', 'IJK', 'DEE', 400, 'Issued');
Insert into book values(5,'Computers', 'LMN', 'FEE', 500, 'Issued');
Insert into book_issue(1,1,'12/Feb/2012','22/Feb/2012',0,0);
Insert into book_issue(2,2,'12/Feb/2012','22/Feb/2012',0,0);
Insert into book_issue(3,3,'12/Feb/2012','22/Feb/2012',0,0);
Insert into book_issue(4,4,'12/Feb/2012','22/Feb/2012',0,0);
Insert into book_issue(5,5,'12/Feb/2012','22/Feb/2012',0,0);
3-
select * from book where author = 'LEE';
4-
select * from book,book_issue,student where student.stud_lib_card_no=book_issue.stud_lib_card_no and book.book_id = book_issue.book_id and price > 150 and class = 'MCA-2-B';
5- Delete from book where status ='LOST';
6-
select count(*) from book where category = 'SYSTEM';
7-
select * from book,student, book_issue where student.stud_lib_card_no=book_issue.stud_lib_card_no and book.book_id = book_issue.book_id and fine_charged=(select max(fine_charged) from book_issue);
8-
update Book_issue set fine_charged=(fine_charged * 0.15) where stud_lib_card_no in (select stud_lib_card_no from book_issue where (sysdate-issue_date)>1);