Considering this tables in SQL CREATE TABLE department ( DEPARTMENT_ID NUMBER(4)
ID: 3833390 • Letter: C
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);
Based on the tables created in Assignment #1, write a PL/SQL anonymous block that performs the following tasks.
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, and job title.
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.
Hard coding (e.g., IF v_day = 'Thursday' OR v_day = 'Friday' OR v_max_num = 4 THEN …) will receive 0 points.
Submitting more than one PL/SQL program will receive 0 points.
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')
The output of your program must match the following: ms Output x Db Buffer size 20000 THURSDAY (4 employees) é OF EMP DEP. NAME MANAGER NAME MANAGER SALARY JOB TITLE HIRE DATE NAME 05-APR-01 JONES CHIEF ACCOUNTANT $4,500.00 ACCOUNTING JOST 06-DEC-01 FISHER SALES EXECUTIVE 2 SALES $4,000.00 CLARK DATABASEADMINISTRATOR 2 IT $4,500.00 20-DEC-01 SMITH JOST 22-AUG-13 BREWSTER TBA FRIDAY (4 employees) JOB TITLE HIRE DATE NAME é OF EMP DEPARTMENT NAME MANAGER NAME MANAGER SALARY VICE PRESIDENT 3 EXECUTIVE 04-MAY-01 JOST KING $5,000.00 0 IT $2,000.00 25-JAN-02 MILLER PRO ADAMS TEST ANALYST $3,000.00 15-FEB-02 WOLFE RESEARCH WILSON $3,000.00 25-FEB-05 JACKSON SALES REPRESENTATIVE SALES FISHERExplanation / Answer
Hi,
Please find below the answer-
Ans 1-
BEGIN
select * from employee where to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY') in(
select day from(select count(employee_ID) as cnt,to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY') as day from employee
group by to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY')
having count(employee_ID)=(select max(cnt) from(
select count(employee_ID) as cnt,to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY') as day from employee
group by to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY') ) )))
END;
Ans 2 -
select employee_name,job_title,hire_date from employee where to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY') in(
select day from(select count(employee_ID) as cnt,to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY') as day from employee
group by to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY')
having count(employee_ID)=(select max(cnt) from(
select count(employee_ID) as cnt,to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY') as day from employee
group by to_char(to_date(hire_date,'dd/mm/yyyy'), 'DAY') ) )))
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.