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 ); Based on script below, using sql oracle need to calculate newbonus and 'noofyr' starting datehired till today 31-3-2021.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
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 );
Based on script below, using sql oracle need to calculate newbonus and 'noofyr' starting datehired till today 31-3-2021.
![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](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2Fc0796caf-7544-4879-8a0e-fa47a81adc1f%2F3ca77ac1-f66d-4979-a807-10f8d0589b8c%2F36dctl_processed.png&w=3840&q=75)
![](/static/compass_v2/shared-icons/check-mark.png)
Step by step
Solved in 2 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
![Oracle 12c: SQL](https://www.bartleby.com/isbn_cover_images/9781305251038/9781305251038_smallCoverImage.gif)
![A Guide to SQL](https://www.bartleby.com/isbn_cover_images/9781111527273/9781111527273_smallCoverImage.gif)
![Oracle 12c: SQL](https://www.bartleby.com/isbn_cover_images/9781305251038/9781305251038_smallCoverImage.gif)
![A Guide to SQL](https://www.bartleby.com/isbn_cover_images/9781111527273/9781111527273_smallCoverImage.gif)