Slip12
Consider the following Entities and Relationships
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.
Solution:-
Create a Database in 3NF & write queries for following.
•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=md.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 having maximum quantity ofCrocin.
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;
