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 above, write a PL/SQL anonymous blockthat accepts an employee ID from the user input and displays 1) the employee’s name, job title, hire date, andtotal pay (salary + commission), 2) his/her manager’s name, job title, hire date, and total pay (salary + commission), 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) the number of employees in the company.

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

If the employee ID from the user input is not in the EMPLOYEE table (EMPLOYEE_ID), your program displays a message telling the user that the employee ID is not in the table.

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

The hire date must be displayed in the DD-MON-YYYY format (4-digit year).

If the employee does not have a manager, the manager’s name, job title, hire date and total pay must be shown as “N/A”.

If the employee does not belong to any department, the name and location of the department must be shown as “N/A”, and the number of employees in the department must be shown as “0”.

You will lose 5 points if your output does not have “User Input:”, “My Output:”, “------- Employee -------”, “-------Manager -------”, “------- Department -------”, or “------- Company -------” in your output.

Explanation / Answer

SET SERVEROUTPUT ON ACCEPT p_1 PROMPT 'Please enter the employee id:' DECLARE v_input_emp_id NUMBER := &p_1; v_emp_name employee.employee_name%TYPE; v_dept_name department.department_name%TYPE; v_pay employee.salary%TYPE; v_mangager_id employee.manager_id%TYPE; v_n NUMBER; v_output VARCHAR2(100) := 'The employee id is not in the table'; v_hiredate employee.hire_date%TYPE; BEGIN SELECT COUNT(*) INTO v_n FROM employee WHERE employee_id = v_input_emp_id; IF v_n = 0 THEN dbms_output.put_line(v_input_emp_id || ' is not in the EMPLOYEE table.'); END IF; IF v_n = 1 THEN SELECT employee_name ,salary + nvl(commission, 0) ,hire_date ,manager_id INTO v_emp_name ,v_pay ,v_hiredate ,v_mangager_id FROM employee WHERE employee_id = v_input_emp_id; dbms_output.put_line('USER INPUT:'); dbms_output.put_line(v_input_emp_id); dbms_output.put_line('----------EMPLOYEE----------'); dbms_output.put_line(rpad('NAME:', 10, ' ') || lpad(v_emp_name, 15)); dbms_output.put_line(rpad('HIRE DATE:', 10) || lpad(v_hiredate, 20)); dbms_output.put_line('TOTAL PAY:' || lpad(to_char(round(v_pay, 2), '$9,990.00'), 21)); SELECT COUNT(*) INTO v_n FROM employee WHERE employee_id = v_mangager_id; IF v_n != 0 THEN SELECT employee_name ,salary + nvl(commission, 0) ,hire_date INTO v_emp_name ,v_pay ,v_hiredate FROM employee WHERE employee.employee_id = v_mangager_id; dbms_output.put_line('----------MANAGER----------'); dbms_output.put_line(rpad('NAME:', 10, ' ') || lpad(v_emp_name, 15)); dbms_output.put_line(rpad('HIRE DATE:', 10) || lpad(v_hiredate, 20)); dbms_output.put_line('TOTAL PAY:' || lpad(to_char(round(v_pay, 2), '$9,990.00'), 21)); END IF; dbms_output.put_line('----------MANAGER----------'); dbms_output.put_line(rpad('NAME:', 10, ' ') || lpad(v_emp_name, 15)); ELSE dbms_output.put_line('EMPLOYEE NOT IN THE TABLE'); END IF; END;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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