Database: medicine_enzyme_interaction

CREATE TABLE "medicine" (
"id" int,
"name" text,
"Trade_Name" text,
"FDA_approved" text,
primary key ("id")
);
CREATE TABLE "enzyme" (
"id" int,
"name" text,
"Location" text,
"Product" text,
"Chromosome" text,
"OMIM" int,
"Porphyria" text,
primary key ("id")
);
CREATE TABLE "medicine_enzyme_interaction" (
"enzyme_id" int,
"medicine_id" int,
"interaction_type" text,
primary key ("enzyme_id", "medicine_id"),
foreign key ("enzyme_id") references `enzyme`("id"),
foreign key ("medicine_id") references `medicine`("id")
);

Question: How many kinds of enzymes are there?
SQL: SELECT count(*) FROM enzyme

Question: What is the total count of enzymes?
SQL: SELECT count(*) FROM enzyme

Question: List the name of enzymes in descending lexicographical order.
SQL: SELECT name FROM enzyme ORDER BY name DESC

Question: What are the names of enzymes in descending order?
SQL: SELECT name FROM enzyme ORDER BY name DESC

Question: List the names and the locations that the enzymes can make an effect.
SQL: SELECT name ,  LOCATION FROM enzyme

Question: What are the names and locations of all enzymes listed?
SQL: SELECT name ,  LOCATION FROM enzyme

Question: What is the maximum Online Mendelian Inheritance in Man (OMIM) value of the enzymes?
SQL: SELECT max(OMIM) FROM enzyme

Question: What is the maximum OMIM value in the database?
SQL: SELECT max(OMIM) FROM enzyme

Question: What is the product, chromosome and porphyria related to the enzymes which take effect at the location 'Cytosol'?
SQL: SELECT product ,  chromosome ,  porphyria FROM enzyme WHERE LOCATION  =  'Cytosol'

Question: What is the product, chromosome, and porphyria of the enzymes located at 'Cytosol'?
SQL: SELECT product ,  chromosome ,  porphyria FROM enzyme WHERE LOCATION  =  'Cytosol'

Question: What are the names of enzymes who does not produce 'Heme'?
SQL: SELECT name FROM enzyme WHERE product != 'Heme'

Question: What are the names of enzymes whose product is not 'Heme'?
SQL: SELECT name FROM enzyme WHERE product != 'Heme'

Question: What are the names and trade names of the medicines which has 'Yes' value in the FDA record?
SQL: SELECT name ,  trade_name FROM medicine WHERE FDA_approved  =  'Yes'

Question: What are the names and trade names of the medcines that are FDA approved?
SQL: SELECT name ,  trade_name FROM medicine WHERE FDA_approved  =  'Yes'

Question: What are the names of enzymes in the medicine named 'Amisulpride' that can serve as an 'inhibitor'?
SQL: SELECT T1.name FROM enzyme AS T1 JOIN medicine_enzyme_interaction AS T2 ON T1.id  =  T2.enzyme_id JOIN medicine AS T3 ON T2.medicine_id  =  T3.id WHERE T3.name  =  'Amisulpride' AND T2.interaction_type  =  'inhibitor'

Question: What are the names of the enzymes used in the medicine  Amisulpride that acts as inhibitors?
SQL: SELECT T1.name FROM enzyme AS T1 JOIN medicine_enzyme_interaction AS T2 ON T1.id  =  T2.enzyme_id JOIN medicine AS T3 ON T2.medicine_id  =  T3.id WHERE T3.name  =  'Amisulpride' AND T2.interaction_type  =  'inhibitor'

Question: What are the ids and names of the medicine that can interact with two or more enzymes?
SQL: 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

Question: For every medicine id, what are the names of the medicines that can interact with more than one enzyme?
SQL: 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

Question: What are the ids, names and FDA approval status of medicines in descending order of the number of enzymes that it can interact with.
SQL: 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

Question: What are the ids, names, and FDA approval status for medicines ordered by descending number of possible enzyme interactions?
SQL: 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

Question: What is the id and name of the enzyme with most number of medicines that can interact as 'activator'?
SQL: 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

Question: What is the id and name of the enzyme that can interact with the most medicines as an activator?
SQL: 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

Question: What is the interaction type of the enzyme named 'ALA synthase' and the medicine named 'Aripiprazole'?
SQL: SELECT T1.interaction_type FROM medicine_enzyme_interaction AS T1 JOIN medicine AS T2 ON T1.medicine_id  =  T2.id JOIN enzyme AS T3 ON T1.enzyme_id  =  T3.id WHERE T3.name  =  'ALA synthase' AND T2.name  =  'Aripiprazole'

Question: What is the type of interaction for the enzyme named 'ALA synthase' and the medicine named 'Aripiprazole'?
SQL: SELECT T1.interaction_type FROM medicine_enzyme_interaction AS T1 JOIN medicine AS T2 ON T1.medicine_id  =  T2.id JOIN enzyme AS T3 ON T1.enzyme_id  =  T3.id WHERE T3.name  =  'ALA synthase' AND T2.name  =  'Aripiprazole'

Question: What is the most common interaction type between enzymes and medicine? And how many are there?
SQL: SELECT interaction_type ,  count(*) FROM medicine_enzyme_interaction GROUP BY interaction_type ORDER BY count(*) DESC LIMIT 1

Question: What are the most common types of interactions between enzymes and medicine, and how many types are there?
SQL: SELECT interaction_type ,  count(*) FROM medicine_enzyme_interaction GROUP BY interaction_type ORDER BY count(*) DESC LIMIT 1

Question: How many medicines have the FDA approval status 'No' ?
SQL: SELECT count(*) FROM medicine WHERE FDA_approved  =  'No'

Question: How many medicines were not approved by the FDA?
SQL: SELECT count(*) FROM medicine WHERE FDA_approved  =  'No'

Question: How many enzymes do not have any interactions?
SQL: SELECT count(*) FROM enzyme WHERE id NOT IN ( SELECT enzyme_id FROM medicine_enzyme_interaction );

Question: What is the count of enzymes without any interactions?
SQL: SELECT count(*) FROM enzyme WHERE id NOT IN ( SELECT enzyme_id FROM medicine_enzyme_interaction );

Question: What is the id and trade name of the medicines can interact with at least 3 enzymes?
SQL: 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

Question: What are the ids and trade names of the medicine that can interact with at least 3 enzymes?
SQL: 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

Question: What are the distinct name, location and products of the enzymes which has any 'inhibitor' interaction?
SQL: 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'

Question: What are the different names, locations, and products of the enzymes that are capable inhibitor interactions?
SQL: 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'

Question: List the medicine name and trade name which can both interact as 'inhibitor' and 'activitor' with enzymes.
SQL: 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'

Question: What are the medicine and trade names that can interact as an inhibitor and activitor with enzymes?
SQL: 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'

Question: Show the medicine names and trade names that cannot interact with the enzyme with product 'Heme'.
SQL: SELECT name ,  trade_name FROM medicine EXCEPT SELECT T1.name ,  T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id  =  T1.id JOIN enzyme AS T3 ON T3.id  =  T2.enzyme_id WHERE T3.product  =  'Protoporphyrinogen IX'

Question: What are the medicine and trade names that cannot interact with the enzyme with the product 'Heme'?
SQL: SELECT name ,  trade_name FROM medicine EXCEPT SELECT T1.name ,  T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id  =  T1.id JOIN enzyme AS T3 ON T3.id  =  T2.enzyme_id WHERE T3.product  =  'Protoporphyrinogen IX'

Question: How many distinct FDA approval statuses are there for the medicines?
SQL: SELECT count(DISTINCT FDA_approved) FROM medicine

Question: How many different FDA approval statuses exist for medicines?
SQL: SELECT count(DISTINCT FDA_approved) FROM medicine

Question: Which enzyme names have the substring "ALA"?
SQL: SELECT name FROM enzyme WHERE name LIKE "%ALA%"

Question: What are the names of enzymes that include the string 'ALA'?
SQL: SELECT name FROM enzyme WHERE name LIKE "%ALA%"

Question: find the number of medicines offered by each trade.
SQL: SELECT trade_name ,  count(*) FROM medicine GROUP BY trade_name

Question: How many medicines are offered by each trade name?
SQL: SELECT trade_name ,  count(*) FROM medicine GROUP BY trade_name