27)Consider the following Entities and Relationships [30 Marks]
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.
Create a Database in 3NF & write queries for following.
• Delete items having quantity less than 2.
• Display total number of suppliers who are supplying ‘Refrigerator’.
• Display all suppliers supplying ‘Washing Machine’ with minimum cost.
• Give supplier details who give maximum discount on each item.
• List suppliers supplying maximum number of item.
-->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='Refrigeratpor';
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 Mashine'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,adddress,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;
/
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='Refrigeratpor';
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 Mashine'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,adddress,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;
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