Anuvarsh Week 6 - Revision history

Anuvarsh: /* Questions to Answer */ updated application pipe

2015-10-13T18:30:50Z

‎Questions to Answer: updated application pipe

Anuvarsh: /* Electronic Lab Notebook */ small changes to application pipe

2015-10-13T18:27:05Z

‎Electronic Lab Notebook: small changes to application pipe

Anuvarsh: /* Questions to Answer */ added more detail to question 5

2015-10-12T21:57:13Z

‎Questions to Answer: added more detail to question 5

Anuvarsh: /* Questions to Answer */ added more information to record count question

2015-10-12T21:55:33Z

‎Questions to Answer: added more information to record count question

Anuvarsh: /* Questions to Answer */ answered the last question!

2015-10-12T21:11:50Z

‎Questions to Answer: answered the last question!

@@ Line 45: / Line 45: @@
 # Provide the <code>sed</code> command sequences that you used to convert the raw text files into sequences of SQL <code>insert</code> statements.
 #* For application.txt
-#** <code>cat application.txt | sed "s/\t/~/g" | sed -r "s/( ){2}//g" | sed "s/ \~/~/g" | sed "s/\~ /~/g" | sed "s/\~\~/~null~/g" | sed "s/\~\~/~null~/g" | sed "s/\~\r$/~null/g" | sed "s/~/,'/1" | sed "s/~/','/1" | sed "s/~/',/1" | sed "s/~/,/1" | sed "s/~/,'/1" | sed "s/~/',/1" | sed "s/~/,'/1" | sed "s/~/','/1" |sed "s/~/,'/1" | sed "s/\r$/'/g" | sed "s/'null'/null/g" | sed "s/'null/null/g" | sed "s/^/insert into application(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | sed "s/$/);/g" | sed 1D > ~/public_html/application.sql.txt</code>
+#** <code>cat application.txt | sed "s/\t/~/g" | sed -r "s/( ){2}//g" | sed "s/ ~/~/g" | sed "s/~ /~/g" | sed "s/~~/~null~/g" | sed "s/~~/~null~/g" | sed "s/~\r$/~null/g" | sed "s/~/,'/1" | sed "s/~/','/1" | sed "s/~/',/1" | sed "s/~/,/1" | sed "s/~/,'/1" | sed "s/~/',/1" | sed "s/~/,'/1" | sed "s/~/','/1" |sed "s/~/,'/1" | sed "s/\r$/'/g" | sed "s/'null'/null/g" | sed "s/'null/null/g" | sed "s/^/insert into application(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | sed "s/$/);/g" | sed 1D > ~/public_html/application.sql.txt</code>
 #* For product.txt
 #** <code>cat Product.txt | sed "s/\*\*Federal Register determination that product was not discontinued or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/\*\*Federal Register determination that product was not discontinued or withdrawn for safety or efficacy\*\*//g" | sed "s/\*\*Federal Register notice determination that product was not discontinued or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/\*\*Federal Registration determination that product was not discontinued or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/See current Annual Edition, 1.8 Description of Special Situations, Levothyroxine Sodium//g" | sed "s/'/(double apostrophe)/g" | sed "s/\t/*/g" | sed -r "s/( ){2}//g" | sed "s/ \*/*/g" | sed "s/\* /*/g" | sed "s/\*\*/*null*/g" | sed "s/\*\*/*null*/g" | sed "s/\*/,/1" | sed "s/\*/,'/1" | sed "s/\*/','/1" | sed "s/\*/',/1" | sed "s/\*/,'/1" | sed "s/\*/',/1" | sed "s/\*/,'/1" | sed "s/\*/','/1" | sed "s/\r$/'/g" | sed "s/^/insert into product(ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TECode,ReferenceDrug,drugname,activeingred) values(/g" | sed "s/$/);/g" | sed 1D > ~/public_html/Product.sql.txt</code>

@@ Line 14: / Line 14: @@
      cat application.txt | sed "s/\t/~/g" | sed -r "s/( ){2}//g" | sed "s/ \~/~/g" | sed "s/\~ /~/g" |
-     sed "s/\~\~/~null~/g" | sed "s/\~\~/~null~/g" | sed "s/\~\r$/~null/g" | sed "s/~/,'/1" | sed "s/~/','/1" | sed "s/~/',/1" |
+     sed "s/~~/~null~/g" | sed "s/~~/~null~/g" | sed "s/~\r$/~null/g" | sed "s/~/,'/1" | sed "s/~/','/1" | sed "s/~/',/1" |
      sed "s/~/,/1" | sed "s/~/,'/1" | sed "s/~/',/1" | sed "s/~/,'/1" | sed "s/~/','/1" |sed "s/~/,'/1" | sed "s/\r$/'/g" |
      sed "s/'null'/null/g" | sed "s/'null/null/g" | sed "s/^/insert into application(ApplNo,ApplType,SponsorApplicant,

@@ Line 65: / Line 65: @@
 #** 32770 records
 # In your database, are these numbers the same or different? Explain why you think so.
-#* After subtracting the line that contains the column from the command line answer, the numbers are the same.
+#* After subtracting the line that contains the column from the command line answer, the numbers are the same. They are the same because ever line in each document (except the heading) was turned into an entry in the respective table.
 #: ''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'' <code>INJECTABLE;INTRAVENOUS, SUBCUTANEOUS</code>?

@@ Line 52: / Line 52: @@
 #* For application.txt
 #** <code>wc application.txt</code>
 #* For product.txt
 #** <code>wc Product.txt</code>
 #* Then subtract 1 from the first number listed in both cases(since it represents the number of lines, and the first line consists of column headings while all other lines are records).
 # Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL <code>select</code> statement.
 #* For application.txt
-#** <code>select * from application</code>
+#** <code>select count(*) from application</code>
 #* For product.txt
-#** <code>select * from product</code>
+#** <code>select count(*) from product</code>
 # In your database, are these numbers the same or different? Explain why you think so.
 #* After subtracting the line that contains the column from the command line answer, the numbers are the same.

@@ Line 132: / Line 132: @@
 #* b)<code>select drugname from product inner join application on (product.applno = application.applno) where sponsorapplicant = 'MERCK' group by drugname;</code>
 # Which ''sponsor applicant'' companies have the text <code>LABS</code> in their names and have products whose active ingredients (''activeingred'') include ''both'' <code>ASPIRIN</code> and <code>CAFFEINE</code>?
-#* a)
+#* a)"ACTAVIS LABS UT INC", "WATSON LABS"
-#* b)
+#* b)<code>select sponsorapplicant from application inner join product on (application.applno = product.applno) where sponsorapplicant like '%LABS%' and activeingred like '%ASPIRIN%' and activeingred like '%CAFFEINE%' group by sponsorapplicant;</code>
 {{Template:Anuvarsh}}