Slip 8
Consider
the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints.
Book (Book_no, title, author, price, year_published)
Customer (cid, cname, addr)
Solution
SQL>Create table Book (Book_No int Primary key,title varchar(10),author varchar(10),price int ,year_published int);
SQL>Insert into Book values(101,’cprog’,’yashwant-kanetkar’,200,1990);
SQL>Create table Customer (cid dint primary key, cname varchar(10)
, addr varchar(10)) ;
SQL>Insert into Customer values(201,’archana’,’pune’);
SQL>Create table Book_Customer(Book_No int ,cid
int,Quantity int);
SQL>Insert into Book_Customer values(101 ,201,4);
Relation between Book and Customer is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be >0.
Consider the above tables and execute the following queries:
Display the name of book whose author is “Mr. Gadhave”.
SQL>Select title from Book where author=’ Mr. Gadhave’;
Add column EMailId into customer table.
SQL>ALTER TABLE customer ADD emailid varchar(10);
Consider
the above tables and execute the following queries:
1.Display customer details from 'Mumbai'.
SQL>Select * from
customer where addr=’Mumbai’;
2.Display author wise details of book.
SQL>Select * from Book order by author;
3.Display customer name that has purchased more than 3 books.
SQL>Select * from Book , Customer,Book_Customer where
Book.bid=Book_Customer.bid and Customer.cid=Book_Customer.cid and Quantity >3;
4.Display book names having price between 100 and 200 and published year is 2019.
SQL>SELECT * FROM Book WHERE price BETWEEN 100 AND 200
and year_published=2019; ;
5. Update the title of book to “DBMS” whose author is “Mr. Talore”.
UPDATE BOOK SET title =
DBMS WHERE author=’Mr.Talore’;
