DBMS-Slip28

Slip28

Consider the following Entities and Relationships                                   
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to Many
with quantity as descriptive attribute.
Constraint: Primary key, rate should be > 0.

Solution:-

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

•Display wholesaler from 'Pune' city and supplying 'Monitor'.
SQL>select  w_name from wholesaler,product,w_prod  where wholesaler.w_no=w_prod.w_no and product.p_no=w_prod.p_no   and p_name='Moniter'and city='pune';

•Display total number of wholesaler of each product.
SQL> select p_name,count(w_name) from wholesaler,product,w_prod   where Wholesaler.w_no=w_prod.w_no and product.p_no=w_prod.p_no   group by p_name;

•Display all wholesalers who are supplying ‘Keyboard’ with maximum price.
SQL>select max(rate),w_name from wholesaler,product,w_prod   where Wholesaler.w_no=w_prod.w_no and product.p_no=w_prod.p_no  and p_name=’keyboard’ group by w_name;

•Display total quantity of each product sold by ‘Mr. Khabia’.
SQL> select p_name,sum(quantity)from wholesaler,product,w_prod   where wholesaler.w_no=w_prod.w_no and product.p_no=w_prod.p_no   and w_name='Mr.Khabia'  group by p_name;

•Decrementrate of all products by 5% supplied by wholesaler from 'Pune ' city.
SQL>update wholesaler,product,w_prod SET    where wholesaler.w_no=w_prod.w_no and product.p_no=w_prod.p_no   and rate=rate-0.05 and city='pune';