|
|
| | ==Questions== | | ==Questions== |
| | | | |
| | + | # '''Provide the DDL (create table) statements that you used for your application and product tables.''' |
| | + | #* Application: |
| | + | #** <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> |
| | + | #* Product |
| | + | #** <code>create table Product (ApplNo int, ProductNo int, Form varchar, Dosage varchar, ProductMktStatus varchar, TEcode varchar, ReferenceDrug int, drugname varchar, activeingredient varchar)</code> |
| | + | # '''Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.''' |
| | + | #* Application: |
| | + | #** <code>cat application.txt | sed "s/\t/~/g" | sed -r "s/( ){2}//g" | sed "s/ ~/~/g" | sed "s/~~/~null~/g" | sed "s/~~/~null~/g" | sed "s/~\r$/~null/g" | sed "s/~/~'/1" | sed "s/~/~'/2" | sed "s/~/~'/5" | sed "s/~/~'/7" | sed "s/~/~'/8" | sed "s/~/'~/2" | sed "s/~/'~/3" | sed "s/~/'~/6" | sed "s/~/'~/8" | sed "s/$/'/g" | sed "s/'null'/null/g" | sed "s/~/,/g" | sed "s/^/insert into application(ApplNo,ApplType,SponsorApplicant,MostRecentLabelAvailableFlag,CurrentPatentFlag,ActionType,Chemical_Type,Ther_Potential,Orphan_Code) values(/g" | sed "s/$/);/g" > ~/public_html/application.sql.txt</code> |
| | + | #* Product: |
| | + | #** <code>cat Product.txt | sed "s/'/\"/g" | sed "s/\t/~/g" | sed "s/\*\*Federal Register determination that product was not discontinued or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/\*\*Federal Register notice determination that product was not discontinued or withdrawn for safety or efficacy reasons\*\*//g" | sed "s/~~/~null~/g" | sed "s/~/~'/2" | sed "s/~/~'/3" | sed "s/~/~'/5" | sed "s/~/~'/7" |sed "s/~/~'/8" | sed "s/~/'~/3" | sed "s/~/'~/4" | sed "s/~/'~/6" | sed "s/~/'~/8" | sed "s/\r$/'/g" | sed "s/'null'/null/g" | sed "s/~/,/g" | sed "s/^/insert into Product (ApplNo,ProductNo,Form,Dosage,ProductMktStatus,TEcode,ReferenceDrug,drugname,activeingredient) values(/g" | sed "s/$/);/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.''' |
| | + | #* <code>cat application.txt | wc</code> will give the number of lines, words and characters. |
| | + | #* Application |
| | + | #**The number of lines is 19747, so the number of entries is 19746 (because the first line isn't an entry). |
| | + | #* Product |
| | + | #** The number of lines is 32771, so the number of entries is 32770. |
| | + | # '''Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL select statement.''' |
| | + | #* <code>select count(*) from application</code> |
| | + | #** Returns 19746 (same as before, minus the previously mentioned first line) |
| | + | #* <code>select count(*) from Product</code> |
| | + | #** Returns 32770 entries (same as before, minus the previously mentioned first line) |
| | + | # '''In your database, are these numbers the same or different? Explain why you think so. |
| | + | #* They're the same, because I already took into account the fact that the first line is not an actual entry. |
| | + | # [[file:kw_week6_Screenshot6.png|right|thumb]] '''What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?''' |
| | + | #* <code>select drugname from product where form = 'INJECTABLE;INTRAVENOUS, SUBCUTANEOUS'</code> |
| | + | #* LOVENOX, VELCADE, VIDAZA, ENOXAPARIN SODIUM, ACTEMRA and AZACITIDINE. |
| | + | # '''What are the names of the drug products whose active ingredient (activeingred) is ATROPINE?''' |
| | + | #* <code>select drugname from product where activeingredient = 'ATROPINE'</code> |
| | + | #** ''Note, the column name in my table is <code>activeingredient</code> instead of <code>activeingred</code>. |
| | + | #* ATROPINE and ATROPEN. |
| | + | # '''In what forms and dosages can the drug product named BENADRYL be administered? |
| | + | #* <code>select form, dosage from Product where drugname = 'BENADRYL'</code> |
| | + | #* [[file:kw_week6_Screenshot7.png|thumb|center]] |
| | + | # '''Which drug products have a name ending in ESTROL?''' |
| | + | #* <code>select drugname from Product where drugname like '%ESTROL%'</code> |
| | + | #* DIETHYLSTILBESTROL, STILBESTROL, DIENESTROL, MEGESTROL and ACETATE. |
| | + | # '''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.''' |
| | + | #* <code>select ther_potential, count (*) from application where ther_potential like '%' group by ther_potential</code> |
| | + | #* [[file:Kw week6 Screenshot8.png|thumb|center]] |
| | + | # '''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.''' |
| | + | #* <code>select chemical_type, count (*) from application where chemical_type >=0 or chemical_type <0 group by chemical_type</code> |
| | + | #* [[file:kw_week6_Screenshot9.png|center|thumb]] |
| | + | # '''What are the names of the drug products that are sponsored (sponsor applicant column) by MERCK?''' |
| | + | #* <code>select drugname from product inner join application on (product.ApplNo = application.ApplNo) where SponsorApplicant = 'MERCK' group by drugname</code> |
| | + | #* "DECADRON", "HUMORSOL", "NEO-HYDELTRASOL", "PRINIVIL", "MAXALT-MLT", "HYDROCORTONE", "PERIACTIN", "PROPECIA", "PROSCAR", "CLINORIL", "PRINZIDE", "ELSPAR", "ALDOMET", "ALDORIL D30", "EMEND", "DIUPRES-500", "NEODECADRON", "ALDOCLOR-250", "MEFOXIN IN DEXTROSE 5% IN PLASTIC CONTAINER", "ARAMINE", "TIAMATE", "BLOCADREN", "CANCIDAS", "CHIBROXIN", "CORTONE", "PEPCID", "TRUSOPT", "REDISOL", "VIOXX", "FLOROPRYL", "ALPHAREDISOL", "DECADRON-LA", "MEFOXIN IN SODIUM CHLORIDE 0.9% IN PLASTIC CONTAINER", "PEPCID RPD", "NOROXIN", "ZOCOR", "LERITINE", "DECADERM", "DOLOBID", "MANNITOL 25%", "DECADRON W/ XYLOCAINE", "ALDORIL D50", "TIMOLIDE 10-25", "CYCLAINE", "HYDROPRES 25", "AMINOHIPPURATE SODIUM", "MEVACOR", "MODURETIC 5-50", "ALDORIL 25", "SINGULAIR", "COLBENEMID", "DIUPRES-250", "HYDELTRA-TBA", "PRIMAXIN", "BENEMID", "MAXALT", "FOSAMAX PLUS D", "HYDRODIURIL", "HYDELTRASOL", "HYDROPRES 50", "ZOLINZA", "ALDORIL 15", "FOSAMAX", "DECASPRAY", "COGENTIN", "ALDOCLOR-150", "PEPCID PRESERVATIVE FREE" |
| | + | # '''Which sponsor applicant companies have the text LABS in their names and have products whose active ingredients (activeingred) include both ASPIRIN and CAFFEINE''' |
| | + | #* <code>select SponsorApplicant, from application inner join product on (application.ApplNo = product.ApplNo) where SponsorApplicant like '%LABS%' and activeingredient like '%ASPIRIN%' and activeingredient like '%CAFFEINE%' group by SponsorApplicant</code> |
| | + | #* ACTAVIS LABS UT INC and WATSON LABS. |
| | | | |
| − | ==Electronic Journal== | + | ==Protocol== |
| | | | |
| − | ===application.txt=== | + | ===Protocol - application.txt=== |
| | | | |
| | *Veronica and I worked quite a bit with Anu, initially. To no avail, we spent approximately 3.5 hours attempting to convert application.txt into an SQL-friendly file (Sunday, 10/11/15).** | | *Veronica and I worked quite a bit with Anu, initially. To no avail, we spent approximately 3.5 hours attempting to convert application.txt into an SQL-friendly file (Sunday, 10/11/15).** |
|
|
| | ====A New Command==== | | ====A New Command==== |
| | | | |
Exception encountered, of type "Error"
[f5222e71] /biodb/fall2015/index.php?diff=cur&oldid=2285&title=Kevin_Wyllie_Week_6 Error from line 434 of /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php: Call to undefined function each()
Backtrace:
#0 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(544): DiffEngine->diag()
#1 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(344): DiffEngine->compareSeq()
#2 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(227): DiffEngine->diffLocal()
#3 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(721): DiffEngine->diff()
#4 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(859): Diff->__construct()
#5 /apps/xmlpipedb/biodb/fall2015/includes/diff/DairikiDiff.php(980): MappedDiff->__construct()
#6 /apps/xmlpipedb/biodb/fall2015/includes/diff/TableDiffFormatter.php(194): WordLevelDiff->__construct()
#7 /apps/xmlpipedb/biodb/fall2015/includes/diff/DiffFormatter.php(140): TableDiffFormatter->changed()
#8 /apps/xmlpipedb/biodb/fall2015/includes/diff/DiffFormatter.php(111): DiffFormatter->block()
#9 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(888): DiffFormatter->format()
#10 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(802): DifferenceEngine->generateTextDiffBody()
#11 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(733): DifferenceEngine->generateContentDiffBody()
#12 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(662): DifferenceEngine->getDiffBody()
#13 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(632): DifferenceEngine->getDiff()
#14 /apps/xmlpipedb/biodb/fall2015/includes/diff/DifferenceEngine.php(453): DifferenceEngine->showDiff()
#15 /apps/xmlpipedb/biodb/fall2015/includes/page/Article.php(795): DifferenceEngine->showDiffPage()
#16 /apps/xmlpipedb/biodb/fall2015/includes/page/Article.php(506): Article->showDiffPage()
#17 /apps/xmlpipedb/biodb/fall2015/includes/actions/ViewAction.php(44): Article->view()
#18 /apps/xmlpipedb/biodb/fall2015/includes/MediaWiki.php(395): ViewAction->show()
#19 /apps/xmlpipedb/biodb/fall2015/includes/MediaWiki.php(273): MediaWiki->performAction()
#20 /apps/xmlpipedb/biodb/fall2015/includes/MediaWiki.php(566): MediaWiki->performRequest()
#21 /apps/xmlpipedb/biodb/fall2015/includes/MediaWiki.php(414): MediaWiki->main()
#22 /apps/xmlpipedb/biodb/fall2015/index.php(44): MediaWiki->run()
#23 {main}