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
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 id and name of the enzyme with most number of medicines that can interact as 'activator'?
assistant
SELECT T1.id , T1.name FROM enzyme AS T1 JOIN medicine_enzyme_interaction AS T2 ON T1.id = T2.enzyme_id WHERE T2.interaction_type = 'activitor' GROUP BY T1.id ORDER BY count(*) DESC LIMIT 1
human
How many kinds of enzymes are there?
assistant
SELECT count(*) FROM enzyme
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
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 ;