===Questions to Answer===

===Questions to Answer===

*Provide the DDL (create table) statements that you used for your application and product tables.

*Provide the DDL (create table) statements that you used for your application and product tables.

**create table application (ApplNo int primary key, ApplType varchar, SponsorApplicant varchar, MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, Chemical_Type int, Ther_Potential varchar, Orphan_Code varchar)

**<code>create table application (ApplNo int primary key, ApplType varchar, SponsorApplicant varchar, MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, Chemical_Type int, Ther_Potential varchar, Orphan_Code varchar) </code>

**create table Product (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus varchar, TECode varchar, ReferenceDrug int, drugname varchar, activeingred varchar)

**<code>create table Product (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus varchar, TECode varchar, ReferenceDrug int, drugname varchar, activeingred varchar)</code>

*Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.

*Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.

** <code> cat application.txt | sed "1D" | sed "s/ //g" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\r/\tnull\r/g" | sed "s/\t/,'/1" | sed "s/\t/','/1" | sed "s/\t/','/1" | sed "s/\t/','/1" | sed "s/\t/','/1" | sed "s/\t/',/1" | sed "s/\t/,'/1" | sed "s/\t/','/g" | sed "s/\r/'\r/g" | sed "s/'null'/null/g" | sed "s/^/insert into application(AppleNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | sed "s/\r/);\r/g" > ~/public_html/application.sql.txt</code>

** <code> cat application.txt | sed "1D" | sed "s/ //g" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\r/\tnull\r/g" | sed "s/\t/,'/1" | sed "s/\t/','/1" | sed "s/\t/','/1" | sed "s/\t/','/1" | sed "s/\t/','/1" | sed "s/\t/',/1" | sed "s/\t/,'/1" | sed "s/\t/','/g" | sed "s/\r/'\r/g" | sed "s/'null'/null/g" | sed "s/^/insert into application(AppleNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | sed "s/\r/);\r/g" > ~/public_html/application.sql.txt</code>

*# <code>select drugname from product where Form = 'INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';</code>

*# <code>select drugname from product where Form = 'INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';</code>

*What are the names of the drug products whose active ingredient (activeingred) is ATROPINE?

*What are the names of the drug products whose active ingredient (activeingred) is ATROPINE?

*# "ATROPEN", "ATROPINE"

*# "ATROPEN" (x4), "ATROPINE"

*# <code> select drugname from product where activeingred = 'ATROPINE'; </code>

*# <code> select drugname from product where activeingred = 'ATROPINE'; </code>

*In what forms and dosages can the drug product named BENADRYL be administered?

*In what forms and dosages can the drug product named BENADRYL be administered?

*#<code> select Form from product where drugname = 'BENADRYL'; </code> <code> select dosage from product where drugname = 'BENADRYL'; </code>

*#<code> select Form from product where drugname = 'BENADRYL'; </code> <code> select dosage from product where drugname = 'BENADRYL'; </code>

*Which drug products have a name ending in ESTROL?

*Which drug products have a name ending in ESTROL?

*#

*#"DIETHYLSTILBESTROL"(x11), "STILBESTROL"(x6), "DIENESTROL"(x1), "STILBESTROL"(x12)

*#

*#<code>select drugname from Product where drugname ~'ESTROL$'</code>

*Produce a table listing all of the known values for the therapeutic_potential column in the application table and how many application records there are of each. (Side note: The therapeutic_potential codes are explained in the ReviewClass_Lookup.txt file, in case you’re interested.)

*Produce a table listing all of the known values for the therapeutic_potential column in the application table and how many application records there are of each. (Side note: The therapeutic_potential codes are explained in the ReviewClass_Lookup.txt file, in case you’re interested.)