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 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 and names of the medicine that can interact with two or more enzymes?
assistant
SELECT T1.id , T1.Name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id = T1.id GROUP BY T1.id HAVING count(*) >= 2
human
List the name of enzymes in descending lexicographical order.
assistant
SELECT name FROM enzyme ORDER BY name 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
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
How many kinds of enzymes are there?
assistant
SELECT COUNT( * ) FROM enzyme ;