QUESTION 7

Question 7
PUBLISHER(publ_id, publ_name, contact_person, contact_addr, contact_phone)
CATEGORY(cat_id, cat_details, max_books, duration)
BOOK_MASTER(book_id, bname, isbn_no, total_copies, publ_id)
MEMBER(member_id, mname, cat_id, mem_ship_dt)
ISSUE(ISSUE_id, member_id, book_id, issu_ret, issue_ret_dt)

CREATE TABLE

Publisher:

create table publisher

(

      publ_id varchar2(10) primary key check (publ_id LIKE 'p%'),

      publ_name varchar2(10),

      cantact_person varchar2(10),

      contact_addr varchar2(10),

      contact_phone number(10)

);

Category:

create table category

(

      cat_id varchar2(10) primary key check(cat_id LIKE 'c%'),

      cat_details varchar2(10),

      max_books number(4),

      duration number(4)

);

Book_master:

create table book_master

(

      book_id number primary key,

      bname varchar2(20),

      isbn_no number(10),

      total_copies number(5),

      publ_id varchar2(10) references publisher(publ_id)

);

Member

create table member

(

      member_id number(5) primary key,

      mname varchar2(10),

      cat_id varchar2(10) references category(cat_id),

      mem_ship_dt date

);

Issue

create table issue

(

      issue_id number(10) primary key,

      member_id number(5) references member(member_id),

      book_id number references book_master(book_id),

      issu_ret varchar2(4),

      issue_ret_dt date

);

INSERT RECORDS

-- Insert Into Publisher

insert into publisher values('p01','pankaj','ashish','bglore',9992225554);

insert into publisher values('p02','geeta','shweta','rajkot',9922448891);

insert into publisher values('p03','ami','parul','pune',8884565789);

insert into publisher values('p04','mehul','monica','delhi',7896541289);

-- Insert Into Category

insert into category values('c01','science',120,2004);

insert into category values('c02','computer',110,2002);

insert into category values('c03','novel',40,2008);

insert into category values('c04','physics',90,2003);

insert into category values('c05','halloween',77,2007);

insert into category values('c06','civics',50,2010);

-- Insert Into Book_Master

insert into book_master values(001,'HP',160054,100,'p01');

insert into book_master values(002,'godaan',160159,150,'p02');

insert into book_master values(003,'RDPD',160357,50,'p03');

insert into book_master values(004,'S1',160789,88,'p04');

insert into book_master values(005,'H1',160152,95,'p02');

insert into book_master values(006,'GK',160789,200,'p04');

insert into book_master values(007,'FAQ',160152,65,'p01');

-- Insert Into Member

insert into member values(01,'rahul','c03','01-jan-2010','jamnagar');

insert into member values(02,'preeti','c04','21-jun-2010','rajkot');

insert into member values(03,'amar','c04','15-apr-2010','delhi');

insert into member values(04,'ashish','c05','30-aug-2010','pune');

insert into member values(05,'abhishek','c03','23-oct-2010','madras');

insert into member values(06,'arun','c06','20-sep-2010','bgolore');

-- Insert Into Issue

insert into issue values(01,02,002,'I','22-jun-2010');

insert into issue values(02,03,001,'I','16-apr-2010');

insert into issue values(07,04,004,'R','14-oct-2010');

insert into issue values(03,02,005,'R','05-jul-2010');

insert into issue values(04,04,002,'I','25-nov-2010');

insert into issue values(05,02,006,'I','02-aug-2010');

insert into issue values(06,06,007,'I','30-sep-2010');

insert into issue values(07,05,007,'I','25-nov-2010');

insert into issue values(08,04,007,'I','15-dec-2010');

insert into issue values(09,03,007,'I','30-dec-2010');

In the above tables duration is in years and it stores the membership duration for that
category.

1. Change the table design of ISSUE table to add a constraint, which will allow only ‘I’ or ‘R’ to be entered in the ISSUE_RET column, which stores the action whether the book is being issued or returned.
   
   alter table issue add check(issu_ret in ('I','R'));
   
2. Add a column to the MEMBER table, which will allow us to store the address of the member.
   
   alter table member add (addr varchar2(10));
   
3. Create a table LIBRARY_USERS which has a structure similar to that of the MEMBER table but with no records.
   
   create table library_users as select * from member where 1=2;
   
4. Give details about members who have issued books, which contain ‘DATA’ somewhere in their titles.
   
   select i.member_id,m.mname from issue i, book_master b, member m
   where i.book_id=b.book_id and bname LIKE '%P%' and i.issu_ret='I' and m.member_id=i.member_id
   
5. Display the books that have been issued at the most three times in the year 2003.
   
   select book_id , count(*) from issue where issu_ret='I' and to_char(issue_ret_dt,'yyyy')= 2010 group by book_id having count(*)<=2 order by book_id
   
6. Display which books of publisher PHI that are issued right now.
   
   
   select p.publ_id,b.bname from issue i,publisher p,book_master b
   where p.publ_id=b.publ_id and b.book_id=i.book_id and i.issu_ret='I' and p.publ_name='pankaj' and to_char(issue_ret_dt,’dd-mon-yyyy’)=to_char(sysdate,’dd-mon-yyyy’);
   
   
   
7. Display details about books whose all copies are issued.
   
   select  * from book_master where (book_id,total_copies)  in
   (select book_id,count(*) from issue group by book_id);
   
8. Display the book details and members for books, which have been issued between 1st Oct 2005 and 15th Nov 2005.
   
   select i.book_id,i.member_id "MEMBER ID",m.mname "MEMBER NAME",
   
   b.bname,b.isbn_no,b.total_copies,b.publ_id
   from issue i,member m,book_master b
   where i.issue_ret_dt> '01-sep-2010' and i.issue_ret_dt<'01-dec-2010' and i.book_id= b.book_id and i.member_id=m.member_id and i.issu_ret='I';
   
9. Display all staff members who have issued at least two books.
   
   select mname from member where member_id in(select i.member_id from issue I group by i.member_id having count(i.book_id)>=3);
   
10. Display details about those publishers whose more than 100 books are available in the library.
    
    select publ_id,publ_name from publisher where publ_id in ( select b.publ_id from book_master b,publisher p where p.publ_id=b.publ_id group by b.publ_id having count(b.book_id)>=2);
    
11. Delete all those members whose membership has expired.
    
    delete from category c where duration < to_char(to_date(sysdate),'yyyy');
    
12. How many members registered in the last three months ?
    
    select count(*) "TOTAL NO" from member where to_char(mem_ship_dt,'mon') in ('dec','oct','nov')
    
13. Display since how many months has each staff member registered.
    
    select member_id, round((sysdate-mem_ship_dt)/30) "TOTAL MONTH" from member;