12)Consider the following Entities and Relationships [30 Marks]
Medical_store (mno, mname, city, phno)
Drug (dno, dname, type, company, price)
Relation between Medical_store and Drug as Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Create a Database in 3NF & write queries for following.
• Update price of drug by 5 % of 'ABC' Company.
• Display names of all medical store where ‘Crocin’ is available.
• Count total number of drug of ‘SunPharma’ company in ‘Sai medical’ store.
• Delete all drugs supplied by ‘SunPharma‘Company in ‘Sai medical’ store.
• Display the details of medical store having maximum quantity of Crocin
-->
Update price of drug by 5 % of 'ABC' Company.
sql>update drugs set price=price+price*0.05 where company='ABC';
Display names of all medical store where ‘Crocin’ is available.
sql>select mname from mstore,drugs,md where mstore.mid=mid and drugs.did=md.did and dname='crocin';
Count total number of drug of ‘SunPharma’ company in ‘Sai medical’ store.
sql>select count(drugs.dname),company from mstore,drugs,md where mstore.mid=md.mid and drugs.did=md.did group by dname,company;
Delete all drugs supplied by ‘SunPharma‘Company in ‘Sai medical’ store.
sql>delete from md where did =(select did from drugs where company='sunpharma');
display the details of medical store haveing maximum quntity of crocin.
sql>select max(md.quantity),mstore.mname,mstore.city from mstore,drugs,md where mstore.mid=md.mid and drugs.did=md.did and dname='crocin' group by mstore.mname,mstore.city;
/
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