DBMS-Slip27

Slip27
Consider the following Entities and Relationships                                     
item (item_no, item_name, quantity)
Suppliers (sup_no, sup_name, address, city, phone_no)
Relation between Item and Supplier is Many to Many with rate and
discount as descriptive attribute.Constraint: Primary key.

Solution:-

Create a Database in 3NF & write queries for following.
•Delete items having quantity less than 2.
SQL>delete from item where quantity<2;

•Display total number of suppliers who are supplying ‘Refrigerator’.
SQL>select count(sup_name)from item,suppliers,item_sup where item.it_no=item_sup.it_no and suppliers.sup_no=item_sup.sup_no and it_name='Refrigerator'

•Display all suppliers supplying ‘Washing Machine’ with minimum cost.
SQL>select sup_name,min(rate) from item,suppliers,item_sup where item.it_no=item_sup.it_no and suppliers.sup_no=item_sup.sup_no  and it_name='Washing Machine'group by sup_name;

•Give supplier details who give maximum discount on each item.
SQL>select sup_name,address,city,ph_no,it_name,max(discount)from item,suppliers,item_sup where item.it_no=item_sup.it_no and suppliers.sup_no=item_sup.sup_no group by sup_name,address,city,ph_no,it_name;

•List suppliers supplying maximum number of item.
SQL>select sup_name,max(it_no) from item,suppliers,item_sup  where item.it_no=item_sup.it_no and suppliers.sup_no=item_sup.sup_no group by  sup_name;