Considering this tables in SQL CREATE TABLE department ( DEPARTMENT_ID NUMBER(4)

Question

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.

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:”.

Explanation / Answer

SET SERVEROUTPUT ON;

declare
deptName department.department_name%TYPE;
deptId department.department_id%TYPE;
deptLoc department.location%TYPE;
empCount number;
empAfterDate number;
avgSalary number(9,2);
empCount2 number;
empCount3 number;
begin

deptName := '&DEPARTMENT';

DBMS_OUTPUT.PUT_LINE('User Input : ' || deptName);

DBMS_OUTPUT.PUT_LINE('User Output : ');

SELECT department_id, location into deptId, deptLoc from DEPARTMENT
where lower(deptName) = lower(department_name);

DBMS_OUTPUT.PUT_LINE('Department ID : ' || deptId);
DBMS_OUTPUT.PUT_LINE('Department Location : ' || deptLoc);

SELECT COUNT(*) INTO empCount FROM employee
where department_id = deptId;

DBMS_OUTPUT.PUT_LINE('Number of Employees : ' || empCount);

SELECT COUNT(*) INTO empAfterDate from EMPLOYEE
where department_id = deptId
and hire_date > to_date('31-DEC-2004','DD-MON-YYYY');

DBMS_OUTPUT.PUT_LINE('Number of Employees joined after 31-DEC-2004 : ' || empAfterDate);

SELECT NVL(AVG( SALARY + NVL( COMMISSION, 0) ),0) INTO avgSalary
FROM EMPLOYEE
WHERE DEPARTMENT_ID = deptId;

DBMS_OUTPUT.PUT_LINE('Average total pay : $' || avgSalary);

SELECT COUNT(*) INTO empCount2 FROM EMPLOYEE
WHERE department_id = deptId
AND (salary + nvl(commission,0)) <
(SELECT AVG( SALARY + NVL( COMMISSION, 0) )
FROM EMPLOYEE
WHERE DEPARTMENT_ID = deptId);

DBMS_OUTPUT.PUT_LINE('Number of Employees whose total pay is less than the department average : ' || empCount2);

SELECT COUNT(*) INTO empCount3 FROM EMPLOYEE
WHERE department_id = deptId
AND (salary + nvl(commission,0)) <
(SELECT AVG( SALARY + NVL( COMMISSION, 0) )
FROM EMPLOYEE);

DBMS_OUTPUT.PUT_LINE('Number of Employees in the department having total pay less than the company average pay : ' || empCount3);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: No such department as ' || deptName);
end;
/

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.