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
Show the id, name, and FDA approval for medicines.
assistant
SELECT id , name , FDA_approved FROM medicine;
human
Only ones that have some interaction with an enzyme.
assistant
SELECT id , name , FDA_approved FROM medicine WHERE id IN ( SELECT medicine_id FROM medicine_enzyme_interaction );
human
Order that by descending order of number of enzymes the medicine interacts 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 ;