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
List the name of enzymes in descending lexicographical order.
assistant
SELECT name FROM enzyme ORDER BY name DESC
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 medicine name and trade name which can both interact as 'inhibitor' and 'activitor' with enzymes.
assistant
SELECT T1.name , T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id = T1.id WHERE interaction_type = 'inhibitor' INTERSECT SELECT T1.name , T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id = T1.id WHERE interaction_type = 'activitor'
human
What are the distinct name, location and products of the enzymes which has any 'inhibitor' interaction?
assistant
SELECT DISTINCT T1.name , T1.location , T1.product FROM enzyme AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.enzyme_id = T1.id WHERE T2.interaction_type = 'inhibitor'
human
List the names and the locations that the enzymes can make an effect.
assistant
SELECT name , LOCATION FROM enzyme ;