CREATE TABLE TEMP1 AS SELECT FRAG,GENE,NAME FROM CONTAINS, GENE WHERE GENE.ID=CONTAINS.GENE AND GENE.NAME="AMP";

CREATE TABLE TEMP2 AS SELECT RCP FROM TEMP1,PRODUCES WHERE TEMP1.FRAG=PRODUCES.FRAG;

SELECT DISTINCT RECIPE.RCP, RECIPE.NAME, RECIPE.FILE, RECIPE.OWNER FROM TEMP2, RECIPE WHERE TEMP2.RCP = RECIPE.RCP;

SELECT DISTINCT RECIPE.RCP, RECIPE.NAME, RECIPE.FILE, RECIPE.OWNER FROM GENE, CONTAINS, PRODUCES, RECIPE WHERE GENE.NAME = 'AMP' AND GENE.ID = CONTAINS.GENE AND CONTAINS.FRAG = PRODUCES.FRAG AND PRODUCES.RCP = RECIPE.RCP;

DROP TABLE TEMP1;
DROP TABLE TEMP2;

CREATE UNIQUE INDEX  GENE_IDX on GENE (ID);

