Consider the following Entities and Relationships [30 Marks]
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.
Create a Database in 3NF & write queries for following.
• Display wholesaler from 'Pune' city and supplying 'Monitor'.
• Display total number of wholesaler of each product.
• Display all wholesalers who are supplying ‘Keyboard’ with maximum price.
• Display total quantity of each product sold by ‘Mr. Khabia’.
• Decrement rate of all products by 5% supplied by wholesaler from 'Pune ' city.
? 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=’Monitor’ and city=’pune’;
Display total number of wholesaler of each product.
SQL>select p_name,count(w_name) from wholesaler,product,w_prod where Wholeseler.w_no=w_prod.w_no and product.p_no=w_prod.p_no group bu 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;
Decrement rate 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';
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=’Monitor’ and city=’pune’;
Display total number of wholesaler of each product.
SQL>select p_name,count(w_name) from wholesaler,product,w_prod where Wholeseler.w_no=w_prod.w_no and product.p_no=w_prod.p_no group bu 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;
Decrement rate 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';
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