Difference between revisions of "Anuvarsh Week 6"
(minor change to the first command) |
(added questions to answer section and second command) |
||
| Line 1: | Line 1: | ||
4 hours later... part one done!!!!!!! | 4 hours later... part one done!!!!!!! | ||
| − | '''Application.txt --> SQL commands''' <br> | + | '''Application.txt --> SQL commands''' <br> <!-- written by Anu with modifications by Kristen --> |
cat application.txt | sed "s/\t/*/g" | sed -r "s/( ){4}//g" | sed -r "s/( ){2}//g" | sed "s/ \*/*/g" | sed "s/\* /*/g" | sed "s/\*P\*\*/\*P\*/g" | sed "s/\*S\*\*/\*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 | cat application.txt | sed "s/\t/*/g" | sed -r "s/( ){4}//g" | sed -r "s/( ){2}//g" | sed "s/ \*/*/g" | sed "s/\* /*/g" | sed "s/\*P\*\*/\*P\*/g" | sed "s/\*S\*\*/\*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 | ||
| + | |||
| + | '''Product.txt --> SQL commands''' <br> <!-- written by Kristen with modifications by Anu --> | ||
| + | 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/'/''/g" | sed "s/\t/*/g" | sed -r "s/( ){4}//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 | ||
| + | |||
| + | === Questions to Answer === | ||
| + | |||
| + | # Provide the DDL (<code>create table</code>) statements that you used for your ''application'' and ''product'' tables. | ||
| + | # Provide the <code>sed</code> command sequences that you used to convert the raw text files into sequences of SQL <code>insert</code> statements. | ||
| + | # Using the command line, how can you determine the number of records in each file? Provide the command. | ||
| + | # Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL <code>select</code> statement. | ||
| + | # 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.'' | ||
| + | # What are the names of the drug products that are administered in the ''form'' <code>INJECTABLE;INTRAVENOUS, SUBCUTANEOUS</code>? | ||
| + | # What are the names of the drug products whose active ingredient (''activeingred'') is <code>ATROPINE</code>? | ||
| + | # In what ''form''s and ''dosage''s can the drug product named <code>BENADRYL</code> be administered? | ||
| + | # Which drug products have a name ''ending'' in <code>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 ''chemical_type'' column in the ''application'' table and how many ''application'' records there are of each. (''Side note:'' The ''chemical_type'' codes are explained in the ''ChemTypeLookup.txt'' file, in case you’re interested.) | ||
| + | # What are the names of the drug products that are sponsored (''sponsor applicant'' column) by <code>MERCK</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>? | ||
Revision as of 06:06, 12 October 2015
4 hours later... part one done!!!!!!!
Application.txt --> SQL commands
cat application.txt | sed "s/\t/*/g" | sed -r "s/( ){4}//g" | sed -r "s/( ){2}//g" | sed "s/ \*/*/g" | sed "s/\* /*/g" | sed "s/\*P\*\*/\*P\*/g" | sed "s/\*S\*\*/\*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
Product.txt --> SQL commands
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/'//g" | sed "s/\t/*/g" | sed -r "s/( ){4}//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
Questions to Answer
- Provide the DDL (
create table) statements that you used for your application and product tables. - Provide the
sedcommand sequences that you used to convert the raw text files into sequences of SQLinsertstatements. - Using the command line, how can you determine the number of records in each file? Provide the command.
- Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL
selectstatement. - 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.
- 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 whose active ingredient (activeingred) is
ATROPINE? - In what forms and dosages can the drug product named
BENADRYLbe administered? - Which drug products have a name ending in
ESTROL? - 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 chemical_type column in the application table and how many application records there are of each. (Side note: The chemical_type codes are explained in the ChemTypeLookup.txt file, in case you’re interested.)
- What are the names of the drug products that are sponsored (sponsor applicant column) by
MERCK? - Which sponsor applicant companies have the text
LABSin their names and have products whose active ingredients (activeingred) include bothASPIRINandCAFFEINE?
