Difference between revisions of "Eyanosch Week 6"

From LMU BioDB 2015
Jump to: navigation, search
(Week 6 personal journal entry)
 
(→‎#9)
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
[[User:Eyanosch | Erich Yanoschik]]
 
[[User:Eyanosch | 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"
 +
 +
# 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 ===
  
  

Latest revision as of 05:27, 15 October 2015

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

Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2015/index.php?title=Eyanosch_Week_6&oldid=2574"