human
Schema:
medicine (id, name, Trade_Name, FDA_approved)
enzyme (id, name, Location, Product, Chromosome, OMIM, Porphyria)
medicine_enzyme_interaction (enzyme_id, medicine_id, interaction_type)
Please answer with SQL.
assistant
Sure, I understand.
human
find the number of medicines offered by each trade.
assistant
SELECT trade_name , count(*) FROM medicine GROUP BY trade_name
human
What is the id and trade name of the medicines can interact with at least 3 enzymes?
assistant
SELECT T1.id , T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id = T1.id GROUP BY T1.id HAVING COUNT(*) >= 3
human
What are the ids, names and FDA approval status of medicines in descending order of the number of enzymes that it can interact with.
assistant
SELECT T1.id , T1.Name , T1.FDA_approved FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id = T1.id GROUP BY T1.id ORDER BY count(*) DESC
human
What are the names and trade names of the medicines which has 'Yes' value in the FDA record?
assistant
SELECT name , trade_name FROM medicine WHERE FDA_approved = 'Yes'
human
How many distinct FDA approval statuses are there for the medicines?
assistant
SELECT count(DISTINCT FDA_approved) FROM medicine
human
How many medicines have the FDA approval status 'No' ?
assistant
SELECT COUNT( * ) FROM medicine WHERE FDA_approved = "No" ;