PL/SQL Question:

write a PL/SQL anonymous block that performs the following tasks.

CREATE TABLE department

( DEPARTMENT_ID NUMBER PRIMARY KEY,

DEPARTMENT_NAME VARCHAR2(50) NOT NULL UNIQUE,

LOCATION VARCHAR2(20) NOT NULL);

INSERT INTO department VALUES(10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO department VALUES(20, 'RESEARCH', 'DALLAS');

INSERT INTO department VALUES(30, 'SALES', 'CHICAGO');

INSERT INTO department VALUES(40, 'IT', 'DALLAS');

INSERT INTO department VALUES(50, 'EXECUTIVE', 'NEW YORK');

INSERT INTO department VALUES(60, 'MARKETING', 'CHICAGO');

COMMIT;

CREATE TABLE employee

( EMPLOYEE_ID NUMBER PRIMARY KEY,

EMPLOYEE_NAME VARCHAR2(20) NOT NULL,

JOB_TITLE VARCHAR2(50) NOT NULL,

SUPERVISOR_ID NUMBER

REFERENCES employee(EMPLOYEE_ID) ON DELETE SET NULL,

HIRE_DATE DATE NOT NULL,

SALARY NUMBER(9, 2) NOT NULL,

COMMISSION NUMBER(9, 2),

DEPARTMENT_ID NUMBER REFERENCES department(DEPARTMENT_ID));

INSERT INTO employee

VALUES(7839, 'KING', 'PRESIDENT', NULL, '20-NOV-01', 5000, NULL, 50);

INSERT INTO employee

VALUES(7596, 'JOST', 'VICE PRESIDENT', 7839, '04-MAY-01', 4500, NULL, 50);

INSERT INTO employee

VALUES(7603, 'CLARK', 'VICE PRESIDENT', 7839, '12-JUN-01', 4000, NULL, 50);

INSERT INTO employee

VALUES(7566, 'JONES', 'CHIEF ACCOUNTANT', 7596, '05-APR-01', 3000, NULL, 10);

INSERT INTO employee

VALUES(7886, 'STEEL', 'PUBLIC ACCOUNTANT', 7566, '08-MAR-03', 2500, NULL, 10);

INSERT INTO employee

VALUES(7610, 'WILSON', 'BUSINESS ANALYST', 7596, '03-DEC-01', 3000, NULL, 20);

INSERT INTO employee

VALUES(7999, 'WOLFE', 'TEST ANALYST', 7610, '15-FEB-02', 2500, NULL, 20);

INSERT INTO employee

VALUES(7944, 'LEE', 'REPORTING ANALYST', 7610, '04-SEP-06', 2400, NULL, 20);

INSERT INTO employee

VALUES(7900, 'FISHER', 'SALES EXECUTIVE', 7603, '06-DEC-01', 3000, 500, 30);

INSERT INTO employee

VALUES(7921, 'JACKSON', 'SALES REPRESENTATIVE', 7900, '25-FEB-05', 2500, 400, 30);

INSERT INTO employee

VALUES(7952, 'LANCASTER', 'SALES CONSULTANT', 7900, '06-DEC-06', 2000, 150, 30);

INSERT INTO employee

VALUES(7910, 'SMITH', 'DATABASE ADMINISTRATOR', 7596, '20-DEC-01', 2900, NULL, 40);

INSERT INTO employee

VALUES(7788, 'SCOTT', 'PROGRAMMER', 7910, '15-JAN-03', 2500, NULL, 40);

INSERT INTO employee

VALUES(7876, 'ADAMS', 'PROGRAMMER', 7910, '15-JAN-03', 2000, NULL, 40);

INSERT INTO employee

VALUES(7934, 'MILLER', 'PROGRAMMER', 7876, '25-JAN-02', 1000, NULL, 40);

INSERT INTO employee

VALUES(8000, 'BREWSTER', 'TBA', NULL, '22-AUG-13', 2500, NULL, NULL);

INSERT INTO employee

VALUES(8100, 'PHILLIPS', 'TBA', 7839, '21-AUG-13', 2800, NULL, NULL);

INSERT INTO employee

VALUES(7400, 'SMITH', 'VICE PRESIDENT', 7839, '16-FEB-01', 4300, NULL, 50);

INSERT INTO employee

VALUES(7700, 'ANDRUS', 'PUBLIC ACCOUNTANT', 7566, '18-FEB-02', 2500, NULL, 10);

INSERT INTO employee

VALUES(7601, 'SAMPSON', 'PROGRAMMER', 7910, '09-JAN-01', 2500, NULL, 40);

INSERT INTO employee

VALUES(7588, 'DODSON', 'TEST ANALYST', 7610, '02-AUG-08', 2500, NULL, 20);

INSERT INTO employee

VALUES(7888, 'SANDY', 'SALES CONSULTANT', 7900, '05-AUG-04', 2500, NULL, 30);

COMMIT;

a) Find all employees who were hired on the days of the week on which the highest number of employees were hired.

b) For each employee found in the previous step, display the following items in the output of your program:

His/her hire date, name, and job title.

The total number of employees who report to him/her directly. (For example, you can find that there are 3 employees who report to Jost directly, Jones, Smith, and Wilson.)

The department name that he/she works. If the employee does not belong to any department, the department name is shown as ****** in your output.

The average salary of the department that he/she works. You must display the average with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56). If the employee does not belong to any department, the average salary is shown as $0.00.

His/her supervisor name and hire date. If the employee was hired before his/her supervisor, the supervisor name is marked with +++. If the employee does not have a supervisor, the supervisor name and hire date are shown as ****** in your output.

Sort your output by days of the week (Monday, Tuesday, , Friday), the hire date, and employee name.

You will lose 10 points if the title lines are missing in your output.

You will lose 10 points if your output is not in the correct format.

Hard coding (e.g., IF v_day = 'Thursday' OR v_day = 'Friday' OR v_max_num = 5 THEN ) will receive 0 points.

You will receive 0 points if you submit more than one PL/SQL program.

Hints:

(1) TRIM(TO_CHAR(hire_date, 'Day'))

(2) TRIM(TO_CHAR(hire_date, 'D')

(3) GROUP BY TO_CHAR(hire_date, 'Day')