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
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 is the most common interaction type between enzymes and medicine? And how many are there?
assistant
SELECT interaction_type , count(*) FROM medicine_enzyme_interaction GROUP BY interaction_type ORDER BY count(*) DESC LIMIT 1
human
List the name of enzymes in descending lexicographical order.
assistant
SELECT name FROM enzyme ORDER BY name DESC
human
List the names and the locations that the enzymes can make an effect.
assistant
SELECT name , LOCATION FROM enzyme
human
How many enzymes do not have any interactions?
assistant
SELECT count(*) FROM enzyme WHERE id NOT IN ( SELECT enzyme_id FROM medicine_enzyme_interaction );
human
What is the total count of enzymes?
assistant
SELECT COUNT( * ) FROM enzyme ;