Welcome to Moddingverse! You Can Get Android Tips,Tricks ,Games and Apps

DBMS-Slip5

 Slip5

QUESTIONS

Consider the following Entities and Relationships 

Book (Book_no, title, author, price, year_published)

Customer (cid, cname, addr)

Relation between Book and Customer 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 customer details from 'Mumbai'.

SQL>Select * from Customerb where addr='mumbai';


Display author wise details of book.

SQL>Select author ,title from BOOK order by author;


Display all customers who have purchased the books published in the year 2013.

SQL>Select * from Customerb,BOOK, Customerb_BOOK where  Customerb.cid=Customerb_BOOK.cid and BOOK.BNO= Customerb_BOOK.BNO and year_published='2013';


Display customer name that has purchased more than 3 books.

SQL>select count(BOOK.BNO),cname from Customerb,BOOK, Customerb_BOOK where  Customerb.cid=Customerb_BOOK.cid and BOOK.BNO=Customerb_BOOK.BNO and BOOK.BNO>3 group by cname;

Displaybook names having price between 100 and 200 and published in the year 2013.

SQL>Select BOOK.title from Customerb,BOOK, Customerb_BOOK  where  price>=100 and   price<=200 and Customerb.cid=Customerb_BOOK.cid and BOOK.BNO= Customerb_BOOK.BNO;

The Imitation Game,Ender's Game,Theory of everything.Vladimir Putin: Life Coach,LIFE OF PI.Believer

Post a Comment