Eyanosch Week 6 - Revision history

Eyanosch: /* #9 */

2015-10-15T05:27:29Z

‎#9

Eyanosch: /* #8 */

2015-10-15T04:37:34Z

‎#8

Eyanosch: /* #7 */

2015-10-15T04:31:20Z

‎#7

Eyanosch: /* a. */

2015-10-15T04:11:56Z

‎a.

Eyanosch: /* b. */

2015-10-15T04:11:23Z

‎b.

Eyanosch: Finished question 6

2015-10-15T04:10:31Z

Finished question 6

Eyanosch: /* #4 */ adding the SQL code to find the number of records in each table

2015-10-15T03:57:55Z

‎#4: adding the SQL code to find the number of records in each table

Eyanosch: Answering Questions, taking info from already previously done SQL and Command line work

2015-10-15T03:49:23Z

Answering Questions, taking info from already previously done SQL and Command line work

Eyanosch at 03:31, 15 October 2015

2015-10-15T03:31:28Z

← Older revision		Revision as of 05:27, 15 October 2015
Line 128:		Line 128:

	=== #9 ===		=== #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 ===

← Older revision		Revision as of 04:37, 15 October 2015
Line 106:		Line 106:
	=== #8 ===		=== #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 ===		=== #9 ===

← Older revision		Revision as of 04:31, 15 October 2015
Line 91:		Line 91:
	=== #7 ===		=== #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 ===		=== #8 ===

@@ Line 76: / Line 76: @@
 === #6 ===
-====a.====
+*a.
 The Drug Names are:
@@ Line 86: / Line 86: @@
 "AZACITIDINE"
-*====b.====
+*b.
 select drugname from product where form like '%INJECTABLE;INTRAVENOUS,SUBCUTANEOUS%';

@@ Line 86: / Line 86: @@
 "AZACITIDINE"
-====b.====
+*====b.====
 select drugname from product where form like '%INJECTABLE;INTRAVENOUS,SUBCUTANEOUS%';

← Older revision		Revision as of 04:10, 15 October 2015
Line 72:		Line 72:
	=== #5 ===		=== #5 ===

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

	=== #6 ===		=== #6 ===

		+	====a.====
		+	The Drug Names are:

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

		+	====b.====
		+	select drugname from product where form like '%INJECTABLE;INTRAVENOUS,SUBCUTANEOUS%';
		+
		+	=== #7 ===
		+
		+
		+
		+
		+	=== #8 ===
		+
		+
		+
		+	=== #9 ===

← Older revision		Revision as of 03:57, 15 October 2015
Line 64:		Line 64:
	=== #4 ===		=== #4 ===

		+	select count(*) from application

		+	select count(*) from product

		+	both give the number of records in each table

	=== #5 ===		=== #5 ===

← Older revision		Revision as of 03:49, 15 October 2015
Line 34:		Line 34:
	Ran the program in SQL:		Ran the program in SQL:
	Query returned successfully: one row affected, 4916 ms execution time.		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 ===
		+
		+
		+
		+
		+	=== #5 ===
		+
		+
		+	=== #6 ===
		+
		+
		+
		+

@@ Line 25: / Line 25: @@
 This should give the proper syntax for adding from the database the SQL
-=== Product ==
+=== 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" |