dba1

create table publisher(pub_id int primary key,pub_name varchar(20) unique,address varchar(20),phone int);
create table book(book_id int primary key,title varchar(20),pub_name varchar(20),pub_year int,foreign key(pub_name) references publisher(pub_name) on delete cascade );
create table library_program(pgm_id int primary key,pgm_name varchar(20),address varchar(20));
create table book_authors(book_id int,author_name varchar(20),foreign key(book_id) references book(book_id) on delete cascade);
create table book_copies(book_id int,pgm_id int,no_of_copies int,foreign key(book_id) references book(book_id) on delete cascade,foreign key(pgm_id) references library_program(pgm_id) on delete cascade)
create table book_lending(book_id int,pgm_id int,card_no int,date_out date,date_due date,foreign key(book_id) references book(book_id) on delete cascade,foreign key(pgm_id) references library_program(pgm_id) on delete cascade)
insert into publisher values(501,'shravya','xxx',123456);
insert into publisher values(502,'shravan','yyy',12345);
insert into publisher values(503,'vicky','zzz',1234);
insert into publisher values(504,'vishnu','aaa',1256);
insert into publisher values(505,'td','bbb',13456);
insert into book values(1,'chem','shravya',2001);
insert into book values(2,'phy','shravan',2003);
insert into book values(3,'math','vicky',2005);
insert into book values(4,'waste','vishnu',2007);
insert into book values(5,'heavy','td',2009);
insert into library_program values(101,'books','banglore');
insert into library_program values(102,'books_a','mangore');
insert into library_program values(103,'books_b','banglore');
insert into library_program values(104,'books_c','mysure');
insert into library_program values(105,'books_d','bangladesh');
insert into book_authors values(1,'santhosh');
insert into book_authors values(2,'ashwini');
insert into book_authors values(3,'vani');
insert into book_authors values(4,'tanish');
insert into book_authors values(5,'carol');
insert into book_copies values(1,101,40);
insert into book_copies values(2,102,30);
insert into book_copies values(3,103,20);
insert into book_copies values(4,104,10);
insert into book_copies values(5,105,60);
insert into book_lending values(1,101,5001,'02-jan-2017','04-april-2017');
insert into book_lending values(2,101,5001,'12-jan-2017','24-april-2017');
insert into book_lending values(3,101,5001,'22-jan-2017','14-april-2017');
insert into book_lending values(4,101,5001,'13-jan-2017','13-april-2017');
insert into book_lending values(5,101,5003,'02-jan-2018','04-april-2018');
select b.book_id,b.title,b.pub_name,a.author_name,c.no_of_copies from book b,book_authors a,book_copies c,library_program l where b.book_id=a.book_id and b.book_id=c.book_id and c.pgm_id=l.pgm_id group by b.book_id,b.title,b.pub_name,a.author_name,c.no_of_copies;
select card_no from book_lending where date_out>'01-jan-2017' and date_out<'01-june-2017' group by card_no having count(*)>3;
delete from book where book_id=1;
select * from book
select pub_year from book;
create view bookss as select distinct b.book_id,b.title,c.no_of_copies from book b,book_copies c where b.book_id=c.book_id;
select * from bookss;
drop view bookss;