Consider the following tables PATIENT and TEST and answer the questions that follow : Table: PATIENT PCODE NAME PHONE DTADMIT
Consider the following tables PATIENT and TEST and answer the questions that follow :
Table: PATIENT
| PCODE | NAME | PHONE | DTADMIT | TESTID |
| 6473 | Amit Sharma | 912355899 | 15-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 | 37-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.
(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).
(ii) To display Names of Patients, Test names and Cost of Test for those Patients who have 'Sharma' in their names.
(iii) To increase the cost of those tests in the table "TEST" by Rs 50.00 that have cost below Rs 200.00.
1 Answers
(i) SELECT NAME, TESTID, TESTNAME
FROM PATIENT, TEST
WHERE PATIENT.TESTID = TEST. TESTID
AND DTADMIT BETWEEN '01-DEC-2017' AND '15-DEC -2017';
(ii) SELECT NAME, TESTNAME, COST
FROM PATIENT, TEST
WHERE PATIENT. TESTID = TEST.TESTID
AND NAME LIKE '%sharma%';
(iii) UPDATE TEST SET COST = COST + 50
WHERE COST < 200;