CREATE TABLE DOCTOR
(DOC_ID NUMBER(3),
DOC_NAME VARCHAR2(9),
DATEHIRED DATE,
SALPERMON NUMBER(12),
AREA VARCHAR2(20),
SUPERVISOR_ID NUMBER(3),
CHGPERAPPT NUMBER(3),
ANNUAL_BONUS NUMBER(5),
CONSTRAINT DOCTOR_DOC_ID_PK PRIMARY KEY (DOC_ID));
INSERT INTO DOCTOR VALUES(432, 'Harrison' , TO_DATE('05-DEC-94'), 12000,
'Pediatrics', 100, 75, 4500);
INSERT INTO DOCTOR VALUES(509, 'Vester' , TO_DATE('09-JAN-00'), 8100,
'Pediatrics', 432, 40, null);
INSERT INTO DOCTOR VALUES(389, 'Lewis' , TO_DATE('21-JAN-96'), 10000,
'Pediatrics', 432, 40, 2250);
INSERT INTO DOCTOR VALUES(504, 'Cotner' , TO_DATE('16-JUN-98'), 11500,
'Neurology', 289, 85, 7500);
INSERT INTO DOCTOR VALUES(235, 'Smith' , TO_DATE('22-JUN-98'), 4550,
'Family Practice', 100, 25, 2250);
INSERT INTO DOCTOR VALUES(356, 'James' , TO_DATE('01-AUG-98'), 7950,
'Neurology', 289, 80, 6500);
INSERT INTO DOCTOR VALUES(558, 'James' , TO_DATE('02-MAY-95'), 9800,
'Orthopedics', 876, 85, 7700);
INSERT INTO DOCTOR VALUES(876, 'Robertson' , TO_DATE('02-MAR-95'), 10500,
'Orthopedics', 100, 90, 8900);
INSERT INTO DOCTOR VALUES(889, 'Thompson' , TO_DATE('18-MAR-97'), 6500,
'Rehab', 100, 65, 3200);
INSERT INTO DOCTOR VALUES(239, 'Pronger' , TO_DATE('18-DEC-99'), 3500,
'Rehab',889, 40, null);
INSERT INTO DOCTOR VALUES(289, 'Borque' , TO_DATE('30-JUN-89'), 16500,
'Neurology', 100, 95, 6500);
INSERT INTO DOCTOR VALUES(100, 'Stevenson' , TO_DATE('30-JUN-79'), 23500,
'Director', null,null,null );
from scripts below, using sql oracle find in the image using nesting functions
Extracted text: 5. The hospital has decided for each year the doctor has worked, helshe will be given an extra RM1000. Write a query that will calculate the bonus for each doctor according to this requirement. Note: Use Nesting Functions DATEHIRED SALPERMON NOOFYR NEW_BONUS 05-DEC-94 12000 26 38000 09-JAN-00 8100 21 29100 21-JAN-96 10000 25 35000 16-JUN-98 11500 23 34500 22-JUN-98 4550 23 27550 01-AUG-98 7950 23 30950 02-MAY-95 9800 26 35800 02-MAR-95 10500 26 36500 18-MAR-97 6500 24 30500 18-DEC-99 3500 21 24500 30-JUN-89 16500 32 48500 30-JUN-79 23500 42 65500