Page 14 - IP

(b) Consider the following tables PATIENT and TEST and answer the
questions that follow :
Table : PATIENT

PCODE NAME PHONE DTADMIT TESTID

6473 Amit Sharma 912356899 19-JUN-2017 T102

7134 Rose Mathew 886744536 12-NOV-2017 T101

8786 Tina Sharma Arora 889088765 06-DEC-2017 T102

6477 Vijay Shah 714567445 07-DEC-2017 T502

7658 Venkat Fazal 865545343 31-DEC-2017 T101

Note : NAME holds the Names of patients.

DTADMIT holds Dates on which a patient was admitted to
hospital.

TESTID holds Ids of Medical tests done on patients.

Table : TEST
TESTID TESTNAME COST

T101 Platelet Count 200.00
T102 Hemogram 190.00

T301 Malaria Detection 350.00

T502 Glucose Test 150.00
Name the Primary keys in both the tables and foreign key in
‘PATIENT’ table.
State the reason for your choice. 2

(c) With reference to the above given tables (in Q6 b), write commands

in SQL for (i) to (iii).
(i) To display Names of Patients, TESTID and Test names for
those Patients who were admitted between ‘01-DEC-2017’ and
‘15-DEC-2017’ (both dates inclusive). 2

(ii) To display Names of Patients, Test names and Cost of Test for
those Patients who have ‘‘Sharma’’ in their names. 2
(iii) To increase the cost of those tests in the table ‘‘TEST’’ by
.
.
< 50 00 that have cost below < 200 00 2
90 10