The added wrinkle for this one is that it has apostrophes within some of the data so to make sure those are included simple replace all apostrophes with a double apostrophe before the inserting of commas and apostrophes begins.  Other than that it is the same basic format and was rather easy to come up with using what I did for application.txt.

The added wrinkle for this one is that it has apostrophes within some of the data so to make sure those are included simple replace all apostrophes with a double apostrophe before the inserting of commas and apostrophes begins.  Other than that it is the same basic format and was rather easy to come up with using what I did for application.txt.

<code>cat Product.txt | sed "1D" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\r/\tnull\r/g" | sed "s/'/''/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/','/1" | sed "s/\r/'\r/g" | sed "s/'null'/null/g" | sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, ProductMktStatus, TECode, ReferenceDrug, drugname, activeingred) values(/g" | sed "s/\r/);\r/g" > ~/public_html/product.sql.txt

<code>cat Product.txt | sed "1D" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\r/\tnull\r/g" | sed "s/'/<nowiki>''</nowiki>/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/','/1" | sed "s/\r/'\r/g" | sed "s/'null'/null/g" | sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, ProductMktStatus, TECode, ReferenceDrug, drugname, activeingred) values(/g" | sed "s/\r/);\r/g" > ~/public_html/product.sql.txt

</code>

</code>

===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> cat Product.txt | sed "1D" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\r/\tnull\r/g" | sed "s/'/''/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/','/1" | sed "s/\r/'\r/g" | sed "s/'null'/null/g" | sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, ProductMktStatus, TECode, ReferenceDrug, drugname, activeingred) values(/g" | sed "s/\r/);\r/g" > ~/public_html/product.sql.txt</code>

**<code> cat Product.txt | sed "1D" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\t/\tnull\t/g" | sed "s/\t\r/\tnull\r/g" | sed "s/'/<nowiki>''</nowiki>/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/','/1" | sed "s/\r/'\r/g" | sed "s/'null'/null/g" | sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, ProductMktStatus, TECode, ReferenceDrug, drugname, activeingred) values(/g" | sed "s/\r/);\r/g" > ~/public_html/product.sql.txt</code>

*Using the command line, how can you determine the number of records in each file? Provide the command.

*Using the command line, how can you determine the number of records in each file? Provide the command.

**

** The number or records is exactly the same as the number of lines in our modified file, therefore a simple piped <code> wc </code> command in place of the <code> > ~/public_html/product.sql.txt </code> will yield the result on either file.

*Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL select statement.

*Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL select statement.

** <code> </code>

** Using <code> Select count(*) from product </code> or <code> Select count(*) from application </code> will yield the number of records in the table.

*In your database, are these numbers the same or different? Explain why you think so.

*In your database, are these numbers the same or different? Explain why you think so.

*For the remaining questions, supply (a) the answer to the question and (b) the SQL statement that you used to answer the question.

*For the remaining questions, supply (a) the answer to the question and (b) the SQL statement that you used to answer the question.

*What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?

*What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?

*#

*# "LOVENOX", "VELCADE", "VIDAZA", "ENOXAPARIN SODIUM", "ACTEMRA", "AZACITIDINE"

*#

*# <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" (x4), "ATROPINE"

*#

*# <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?

*#

*#Forms: "CAPSULE;ORAL", "ELIXIR;ORAL", "CAPSULE;ORAL", "INJECTABLE;INJECTION", "INJECTABLE;INJECTION" dosage: "50MG","12.5MG/5ML","25MG","10MG/ML", "50MG/ML"

*#

*#<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.)