Eyanosch Week 6

Erich Yanoschik

Week 6

Application

After downloading the zip files and unzipping them I need to rework the files so they can be loaded into postgreSQL

eyanosch@ab201:~$ cat application.txt | sed "s/ *//g" |  sed "s/\t/ /g"

1. pulls up information, removes the spaces, and removes the tabs. Adds a space inbetween each column

cat application.txt | sed "s/. /&,/g"| sed "s/ *//g" |  sed "s/\t/ /g" 

eyanosch@ab201:~$ cat application.txt | sed "s/ *//g" |  sed "s/\t/ /g" | sed "s/ /' /g" | sed "s/. /&'/g" | sed "s/ /,&/g" | sed "s/\r$/'/g" | sed "s/^/&'/g" | sed "s//null/g"

I had to add ' behind and infront of every character and then the empty spaces that were I replaced with the word null

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

This should give the proper syntax for adding from the database the SQL

Product

cat Product.txt |  sed "s/'/\"/g" | sed "s/ *//g" |  sed "s/\t/ /g" | sed "s/ /' /g" | sed "s/. /&'/g" | sed "s/ /,&/g" | sed "s/\r$/');/g" | sed "s/^/&'/g" | sed "s//null/g" | 
sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, productMktStatus, TECode, ReferenceDrug, drugname, activeingred) values (/g" | sed "1D" > ~/public_html/Product.sql.txt

Apply the same principle but there were 's within the text so I changed them into " and replaced them with null

Ran the program in SQL:

Query returned successfully: one row affected, 4916 ms execution time.

#1

create table Application(ApplNo varchar, ApplType varchar, SponsorApplicant varchar, MostRecentLabelAvailableFlag boolean, CurrentPatentFlag boolean, ActionType varchar, Chemical_Type varchar, Ther_Potential varchar, Orphan_Code varchar)

create table product(ApplNo int, ProductNo int, Form varchar, Dosage varchar, productMktStatus varchar, TECode varchar, ReferenceDrug varchar, drugname varchar, activeingred varchar)

#2

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

cat Product.txt |  sed "s/'/\"/g" | sed "s/ *//g" |  sed "s/\t/ /g" | sed "s/ /' /g" | sed "s/. /&'/g" | sed "s/ /,&/g" | sed "s/\r$/');/g" | sed "s/^/&'/g" | sed "s//null/g" | 
sed "s/^/insert into product(ApplNo, ProductNo, Form, Dosage, productMktStatus, TECode, ReferenceDrug, drugname, activeingred) values (/g" | sed "1D" > ~/public_html/Product.sql.txt

#3

wc application.txt

Results: 19757 147418 1616281 application.txt

wc Product.txt

Results: 32796 369649 2858064 Product.txt

19757 and 32796 records in each respective file

#4

select count(*) from application

select count(*) from product

both give the number of records in each table

#5

The numbers should be the same, since each column corresponds to a specific record in the database

#6

• a.

The Drug Names are:

"LOVENOX" "VELCADE" "VIDAZA" "ENOXAPARINSODIUM" "ACTEMRA" "AZACITIDINE"

• b.

select drugname from product where form like '%INJECTABLE;INTRAVENOUS,SUBCUTANEOUS%';

#7

• a.

"ATROPEN" "ATROPEN" "ATROPEN" "ATROPEN" "ATROPINE"

Atropen and Atropine are the drug products whose active ingredient is Atropine

• b.

select drugname from product where activeingred like 'ATROPINE';

#8

• a.

The Dosages are: "50MG" "12.5MG/5ML" "25MG" "10MG/ML" "50MG/ML"

The Forms are: "CAPSULE;ORAL" "ELIXIR;ORAL" "CAPSULE;ORAL" "INJECTABLE;INJECTION" "INJECTABLE;INJECTION"

• b.

select form from product where drugname like 'BENADRYL'; select dosage from product where drugname like 'BENADRYL';

#9

• a.

"DIETHYLSTILBESTROL" x 11

"STILBESTROL" x 12

"DIENESTROL"

• b.

select drugname from product where drugname ~ 'ESTROL$';

#10

• a.

The characters within ther_potential and their counts

"S*";3 "S";3460 "P";928 "P*";26

• b.

select ther_potential,count(*) from application group by ther_potential

#11

• a.

Known values for Chemical_type and the known values for each

"8";27 "9";1 "2";139 "23";8 "34";9 "5";1047 "3";1464 "1";1272 "7";35 "24";8 "14";34 "4";323 "6";87 "10";2

• b. select chemical_type,count(*) from application group by chemical_type

#12

For this the databases are going to have to be joined

a. ERROR: operator does not exist: integer = character varying I need to go in and change my table data

b.

#13

Class Journals

Class Journal Week 1

Class Journal Week 2

Class Journal Week 3

Class Journal Week 4

Class Journal Week 5

Class Journal Week 6

Class Journal Week 7

Class Journal Week 8

Class Journal Week 9

Class Journal Week 10

Class Journal Week 11

Weekly Assignments

Week 1

Week 2

Week 3

Week 4

Week 5

Week 6

Week 7

Week 8

Week 9

Week 10

Week 11

Week 12

Week 13

Personal Journal

Eyanosch Week 2

Eyanosch Week 3

Eyanosch Week 4

Eyanosch Week 5

Eyanosch Week 6

Eyanosch Week 7

Eyanosch Week 8

Eyanosch Week 9

Eyanosch Week 10

Eyanosch Week 11

Eyanosch Week 12

Eyanosch Week 13

Eyanosch Week 14

Eyanosch Week 15

Electronic Notes (E-notes)

Week 3 E-notes Eyanosch

Week 4 E-notes Eyanosch

Week 5 E-notes Eyanosch

Week 6 E-notes Eyanosch

Week 7 E-notes Eyanosch

Week 8 E-notes Eyanosch

Week 9 E-notes Eyanosch

Week 10 E-notes Eyanosch

Week 11 E-notes Eyanosch

Week 12 E-notes Eyanosch

Week 13 E-notes Eyanosch

Week 14 E-notes Eyanosch

Week 15 E-notes Eyanosch

Class (personal) Notes

Week 3 Notes

Week 4 Notes

Week 5 Notes

Week 6 Notes

Week 7 Notes

Week 8 Notes

Week 9 Notes

Week 10 Notes

Week 11 Notes

Week 12 Notes

Week 13 Notes