DBMS-Slip21

Slip21

Consider the following Entities and Relationships                                    
Book (book_no, book_name, price)
Publisher (pno, pname, city)
Relation between Book and Publisher is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.

Solution:-

Create a Database in 3NF & write queries for following.

•Display total quantity of each book .
SQL>Select sum(quantity),bname from Book,Publisher, Book_Publisher where Book.bno= Book_Publisher.bno and Publisher.pno= Book_Publisher.pno group by bname;

•Display Publisher names from 'Pune'.
SQL>select pname from Publisher where city=’PUNE’ order by pname;

•Display all publisher publishing more than two books.
SQL>Select pname,bname,price  from Book,Publisher, Book_Publisher where Book.bno= Book_Publisher.bno and Publisher.pno= Book_Publisher.pno group by pname,bname,price  having count(Book.bno)>2;

•Display publisher having average books price less than average books price of ‘BPV Publications’.
SQL>Select avg(price)from Book,Publisher, Book_Publisher where Book.bno= Book_Publisher.bno and Publisher.pno= Book_Publisher.pno and book.price<=(select avg(price) from Book,Publisher, Book_Publisher where Book.bno= Book_Publisher.bno and Publisher.pno= Book_Publisher.pno and pname=’BPV’);
SQL>select pname from Book,Publisher, Book_Publisher where Book.bno= Book_Publisher.bno and Publisher.pno= Book_Publisher.pno and  pname=‘BPV’ having avg< price;

•Displaypublisher wise book details.
SQL>Select pname,bname,price  from Book,Publisher, Book_Publisher where Book.bno= Book_Publisher.bno and Publisher.pno= Book_Publisher.pno order by pname;