3) Create table DEPARTMENT as described below. CREATE TABLE department ( DEPARTM
ID: 3867066 • Letter: 3
Question
3) Create table DEPARTMENT as described below.
CREATE TABLE department
( DEPARTMENT_ID NUMBER(4) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(20) NOT NULL UNIQUE,
LOCATION VARCHAR2(20) NOT NULL);
4) Populate the DEPARTMENT table.
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;
5) Please make sure that there are 6 rows in your DEPARTMENT table.
SELECT * FROM department;
6) Create table EMPLOYEE as described below.
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));
7) Populate the EMPLOYEE table.
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, 400, 30);
INSERT INTO employee
VALUES(7952, 'LANCASTER', 'SALES CONSULTANT', 7900, '06-DEC-06', 2000, 150, 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);
COMMIT;
a) Find all employees who were hired on the days of the week on which the highest number of employees were hired.
b) For each employee found in the previous step, display the following items in the output of your program:
His/her hire date, name, job title, and salary.
The total number of employees who report to him/her directly. (For example, you can find that there are 3 employees who report to Jost directly, Jones, Smith, and Wilson.)
The department name that he/she works. If the employee does not belong to any department, the department name is shown as “------” in your output.
His/her manager name and salary. If the employee does not have a manager, the manager name and salary are shown as “------” in your output.
Sort your output by days of the week (Monday, Tuesday, …, Friday), the hire date, and employee name.
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 ($) sign, a comma, and two decimal places (e.g., $1,234.56).
Hard coding (e.g., IF v_day = 'Thursday' OR v_day = 'Friday' OR v_max_num = 4 THEN …) will receive 0 points.
You will receive 0 points if you submit more than one PL/SQL program.
Hints:
(1) TO_CHAR(hire_date, 'Day')
(2) TRIM(TO_CHAR(hire_date, 'Day'))
(3) TRIM(TO_CHAR(hire_date, 'D')
(4) GROUP BY TO_CHAR(hire_date, 'Day')
Explanation / Answer
a.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.