Considering this tables in SQL

CREATE TABLE department ( DEPARTMENT_ID NUMBER(4) PRIMARY KEY, DEPARTMENT_NAME VARCHAR2(20) 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(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));

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

1)Based on the DEPARTMENT and EMPLOYEE tables created in above, write a PL/SQL anonymous block that accepts a department name from the user input and displays a) the department ID of that department, b) the location of that department, c) the number of employees in that department, d) the number of employees in that department whose hire date is after 31-DEC-2004, e) the average total pay (salary + commission) for that department, f) the number of employees in that department whose total pay (salary + commission) is less than the average total pay (salary + commission) for that department, and g) the number of employees in that department whose total pay (salary + commission) is less than the average total pay (salary + commission) of all employees in the company.

 Submitting more than one PL/SQL program will receive 0 points. 

If the department name from the user input is not in the DEPARTMENT table (DEPARTMENT_NAME), your program displays a message telling the user that the department name is not in the table.

Department name is not case sensitive (e.g., SALES = Sales). You will lose 5 points if you do not use the UPPER (or LOWER) function in your program.

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

You will lose 5 points if your output does not have User Input: or My Output:.