Using this table: CREATE TABLE department ( DEPARTMENT_ID NUMBER PRIMARY KEY, DE
ID: 3715993 • Letter: U
Question
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;
write a PL/SQL anonymous block to find all departments that have more than three employees whose salaries are above 68% of the maximum (highest) salary of their respective departments. For each of these departments, your program must display
the department name
the latest hire date in the department
the number of employees in the department who were hired on the latest hire date in the department
the maximum (highest) salary of the department
68% of the maximum (highest) salary of the department
the number of employees whose salaries are above 68% of the maximum (highest) salary in the department
Sort your output in ascending order by the department name.
Hard coding the department name (e.g., EXECUTE, IT, and RESEARCH), the number of departments (e.g., 3), the number of employees (e.g., 4), or the maximum salary willreceive 0 points.
Hint: using 1 cursor is enough.
Explanation / Answer
Hi, hope this help you! Please ask if any clarifications before rating straightaway.
"write a PL/SQL anonymous block to find all departments that have more than three employees whose salaries are above 68% of the maximum (highest) salary of their respective departments." : Following will do this for you.
Further filtering and projection can be done as needed. The Following query will give you a good insight.
select DEPARTMENT_NAME from department d where (select count(*) from employee e where e.DEPARTMENT_ID = d.DEPARTMENT_ID and e.SALARY>(select (0.68)*max(SALARY) from (select SALARY from employee where DEPARTMENT_ID = e.DEPARTMENT_ID)))>3 ORDER BY d.DEPARTMENT_ID;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.