Based on the tables created in Assignment #1, write a PL/SQL anonymous block that performs the following tasks.

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, job title, and salary.

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.

His/her manager name and salary. If the employee does not have a manager, the manager name and salary 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.

Your program must display the salary with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56).

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

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

Hints:

(1) TO_CHAR(hire_date, 'Day')

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

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

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

(This question should take less than 60 minutes to solve.)

The output of your program must match the following:

CREATE TABLE department

( DEPARTMENT_ID NUMBER(4) PRIMARY KEY,

DEPARTMENT_NAME VARCHAR2(20) NOT NULL UNIQUE,

LOCATION VARCHAR2(20) NOT NULL);

4) Populate the DEPARTMENT table.

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;

5) Please make sure that there are 6 rows in your DEPARTMENT table.

SELECT * FROM department;

6) Create table EMPLOYEE as described below.

CREATE TABLE employee

( EMPLOYEE_ID NUMBER(4) PRIMARY KEY,

EMPLOYEE_NAME VARCHAR2(20) NOT NULL,

JOB_TITLE VARCHAR2(50) NOT NULL,

MANAGER_ID NUMBER(4)

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(4) REFERENCES department(DEPARTMENT_ID));

7) Populate the EMPLOYEE table.

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

COMMIT;

8) Please make sure that there are 16 rows in your EMPLOYEE table.