1. To display Names of Patients, TESTID and Test names for those Patients who were admitted

Question

With reference to the above given table, write commands in SQL for (i) to (iii)

1. 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. To display names of Patients, Test names and Cost of Test for those patients who have ‘Sharma’ in their names.

3. To increase the cost of those tests in the table ‘TEST’ by ₹ 50.00 that have cost below ₹ 200.00

Answer 1

SQL commands for the given statements : 

(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 ;