SQL Query List drug information (Pharmaceutical company name, trade name, formul

Question

SQL Query

List drug information (Pharmaceutical company name, trade name, formula) that
has been prescribed by at least one doctor.

Retrieve the pharmacies that sell more than 2 types of drugs. List pharmacy name
and number of types of drugs sold. (Type is distinguished by “trade_name”.
However, if two drugs with the same trade name are produced by two different
pharmaceutical companies, they are considered as two different types of drugs.)

Doctor octorDSSN DoctorName Specialty PrescriptionDoCSSN Pre No Date Consist OfDocSSN Pre No CoName TradeName Drug CoNameTradeNae Formula SellsCoName radeName PharmacyName Price Pharmacy PharmacyNameAddress Phone Pharmaceutical Company CompanyName Phone

Explanation / Answer

Q1. List drug information (Pharmaceutical company name, trade name, formula) that has been prescribed by at least one doctor.

Ans :
   select * from Drug where TradeName in (select distinct TradeName from Consist_Of)
       or
   select CoName, TradeName, Formula from Drug d, Consist_Of c where d.TradeName = c.TradeName

Q2. Retrieve the pharmacies that sell more than 2 types of drugs.
Ans :
   select PharmacyName from Sells group by PharmacyName having count(TradeName) > 2

Q3. List pharmacy name and number of types of drugs sold.
(Type is distinguished by “trade_name”. However, if two drugs with the same trade name are produced by two different
pharmaceutical companies, they are considered as two different types of drugs)

Ans : It's asking for sold items. i.e; its there in Consists_Of (what ever doctor prescribed)

   select PharmacyName, count(TradeName) from Sells group by PharmacyName

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.