Using this table:

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, 600, 30);

INSERT INTO employee

VALUES(7952, 'LANCASTER', 'SALES CONSULTANT', 7900, '06-DEC-06', 2000, 1600, 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', 2600, NULL, 30);

COMMIT;

3) (60 points) Based on the tables created in Assignment #1, write a PL/SQL program that accepts an employee ID from the user input and displays 1) the employee's name, job title, hire date, and salary, 2) the employee's supervisor's ID, name, job title, and hire date, 3) the name and location of the department where the employee works, 4) the number of employees in the department where the employee works, and 5) all employees (alone with their job titles, hire dates, and salary) who work in the same department as the given employee and were hired after the given employee (or "******"). Sort your output by the employee name Hard coding (e.g., IF v emp id 7596 THEN v 1:- . ..) will receive 0 points *If the given employee does not have a supervisor, the supervisor's ID, name, job title, and hire date are shown as"******" in your output. *If the given employee does not belong to any department, the name and location of the department are shown as "******" in your output. The number of employees in the department is shown as 0 *If there is no employees who work in the same department as the given employee and were hired after the given employee, you display "******" in your output. *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 (S) sign, a comma, and two decimal places (e.g., $1,234.56) *You will receive 0 points if you submit more than one PL/SQL program. To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9, plus (+) or minus (-) sign, and Enter (This question should take less than 60 minutes to solve.) Test your program. You must ensure that the output of your program matches the following sample output: Case 1) Enter Value Please enter the employee ID: OK Cancel Output: Dbms Output x aBuffer Size:20000 User Input: 123 123 is not in the employee table. Case 2) 3) (60 points) Based on the tables created in Assignment #1, write a PL/SQL program that accepts an employee ID from the user input and displays 1) the employee's name, job title, hire date, and salary, 2) the employee's supervisor's ID, name, job title, and hire date, 3) the name and location of the department where the employee works, 4) the number of employees in the department where the employee works, and 5) all employees (alone with their job titles, hire dates, and salary) who work in the same department as the given employee and were hired after the given employee (or "******"). Sort your output by the employee name Hard coding (e.g., IF v emp id 7596 THEN v 1:- . ..) will receive 0 points *If the given employee does not have a supervisor, the supervisor's ID, name, job title, and hire date are shown as"******" in your output. *If the given employee does not belong to any department, the name and location of the department are shown as "******" in your output. The number of employees in the department is shown as 0 *If there is no employees who work in the same department as the given employee and were hired after the given employee, you display "******" in your output. *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 (S) sign, a comma, and two decimal places (e.g., $1,234.56) *You will receive 0 points if you submit more than one PL/SQL program. To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9, plus (+) or minus (-) sign, and Enter (This question should take less than 60 minutes to solve.) Test your program. You must ensure that the output of your program matches the following sample output: Case 1) Enter Value Please enter the employee ID: OK Cancel Output: Dbms Output x aBuffer Size:20000 User Input: 123 123 is not in the employee table. Case 2)