21)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.
Create a Database in 3NF & write queries for following.
• Display total quantity of each book .
• Display Publisher names from 'Pune'.
• Display all publisher publishing more than two books.
• Display publisher having average books price less than average books price of ‘BPV Publications’.
• Display publisher wise book details.
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
• Display publisher 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;.
/
• 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
• Display publisher 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;.
Search
Recent Posts
Mail Management System
Posted on 2019-07-18Online food ordering system
Posted on 2019-07-18Library Management System
Posted on 2019-07-17Health center system project
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17furniture management system
Posted on 2019-07-17Electronic shop management system
Posted on 2019-07-17Automobile Workshop Management
Posted on 2019-07-17Online Visa Processing System
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17petrol-management system
Posted on 2019-07-17Cloths management system
Posted on 2019-07-17Society Management system
Posted on 2019-07-17Mall management system
Posted on 2019-07-17school management system
Posted on 2019-07-17Sales Order Processing System
Posted on 2019-07-17Retail sales management
Posted on 2019-07-17Raw Materials Management
Posted on 2019-07-17railway reservation system
Posted on 2019-07-17purchase and sales management system
Posted on 2019-07-17Placement Management System
Posted on 2019-07-17Pet Shop Management System
Posted on 2019-07-17petrol pump management system
Posted on 2019-07-17Patient Information System
Posted on 2019-07-17news agency system
Posted on 2019-07-17Cinema Booking System
Posted on 2019-07-17Medical Store System
Posted on 2019-07-17leave management System
Posted on 2019-07-17Laboratory Information Management System
Posted on 2019-07-17content management system
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17Institute Management System
Posted on 2019-07-17Hotel management System
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17Garage Management System
Posted on 2019-07-17Furniture shop management system
Posted on 2019-07-17Fisheries management System
Posted on 2019-07-17Fertilizer scheduling system
Posted on 2019-07-17online eye care system
Posted on 2019-07-17Dental Clinic Management System
Posted on 2019-07-17Cyber Café Management
Posted on 2019-07-17Milk Billing System
Posted on 2019-07-17Colddrink management system
Posted on 2019-07-17Cable management System
Posted on 2019-07-17Beauty parlor management system
Posted on 2019-07-17Facebook Clone
Posted on 2019-05-28Dance Class Management System
Posted on 2019-05-24Library Management System
Posted on 2019-05-24Cab Management System
Posted on 2019-05-23Blood Bank Management system
Posted on 2019-05-23Beauty Parlour Management System
Posted on 2019-05-23vissa proccesing system
Posted on 2019-05-23Toll Plazza
Posted on 2019-05-23BILLING APPLICATION
Posted on 2019-05-23FLORICULTURE MANAGEMENT SYSTEM
Posted on 2019-05-23Car On Rent
Posted on 2019-05-23E-commers Shop
Posted on 2019-05-23