SQL CREATE TABLE department ( DEPARTMENT_ID NUMBER(4) PRIMARY KEY, DEPARTMENT_NA
ID: 3859600 • Letter: S
Question
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);
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;
Questions
Write a SQL SELECT statement to display the employee ID, name, job title, and hire date for all employees along with their managers’ names, job titles, and hire dates. Make sure that employees without managers are included as well.
If an employee does not have a manager, the manager’s name is shown as “------”, the manager’s job title is shown as “------”, and the manager’s hire date is shown as 31-DEC-9999 in your output.
The hire date must be displayed in the DD-MON-YYYY format (4-digit year).
You cannot use hard-coded employee names (e.g., WHERE employee_name = 'KING') in your SELECT statement.
Sort your output in ascending order by employee ID.
No more than one SELECT statement.
Hints: 1) You may need to use an OUTER JOIN and a SELF-JOIN.
2) NVL(TO_CHAR(column_x, 'DD-MON-YYYY'), '31-DEC-9999')
EMPLOYEE ID EMPLOYEE NAME EMPLOYEE JOB TITLE EMPLOYEE HIRE DATE MANAGER NAME MANAGER JOB TITLE MANAGER HIRE DATE
----------- -------------------- -------------------------------------------------- ------------------ -------------------- -------------------------------------------------- -----------------
7566 JONES CHIEF ACCOUNTANT 05-APR-2001 JOST VICE PRESIDENT 04-MAY-2001
7596 JOST VICE PRESIDENT 04-MAY-2001 KING PRESIDENT 20-NOV-2001
7603 CLARK VICE PRESIDENT 12-JUN-2001 KING PRESIDENT 20-NOV-2001
7610 WILSON BUSINESS ANALYST 03-DEC-2001 JOST VICE PRESIDENT 04-MAY-2001
7788 SCOTT PROGRAMMER 15-JAN-2003 SMITH DATABASE ADMINISTRATOR 20-DEC-2001
7839 KING PRESIDENT 20-NOV-2001 ------ ------ 31-DEC-9999
……
2)
Write a SQL SELECT statement to display 1) all department names, 2) the maximum total pay (salary + commission) for each department, 3) the minimum hire date for each department, and 4) the total number of employees in each department.
Any employee who does not belong to any department is excluded from your output.
You must display the maximum total pay with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56). If a department does not have any employee, the maximum total pay is shown as $0.00.
The minimum hire date must be displayed in the DD-MON-YYYY format (4-digit year). If a department does not have any employee, the minimum hire date is shown as 31-DEC-9999.
Sort your output in ascending order by department name.
No more than one SELECT statement.
Hint: You may need to use an OUTER JOIN.
Your statement’s output should match the following format:
DEPARTMENT NAME MAXIMUM TOTAL PAY MINIMUM HIRE DATE TOTAL NUMBER OF EMPLOYEES
-------------------- ----------------- ----------------- -------------------------
ACCOUNTING $3,000.00 05-APR-2001 2
EXECUTIVE $5,000.00 04-MAY-2001 3
IT $2,900.00 20-DEC-2001 4
MARKETING $0.00 31-DEC-9999 0
RESEARCH $3,000.00 03-DEC-2001 3
SALES $3,500.00 06-DEC-2001 3
Explanation / Answer
Hi,
Below is the answer-
Ans 1 - select e.employee_id,e.employee_name,e.job_title, to_char(e.hire_date,'DD-MON-YYYY') as HIRE_DATE,
NVL(m.employee_name,'------'),NVL(m.job_title,'------'), NVL(to_char(m.hire_date,'DD-MON-YYYY'), '31-DEC-9999') from
employee e right outer join employee m
on e.employee_id=m.mamager_id
order by e.employee_id asc;
Ans 2 - select d.department_name,
'$'||NVL(round(to_char(max(e.salary+e.commission),'999,999,999,999'),2),0.00) as total_pay,
NVL(to_char(min(e.hire_date),'DD-MON-YYYY'),'31-DEC-9999') as HIRE_DATE,
count(Employee_id)
from employee e
right outer join department d
on e.department_id=d.department_id
group by d.department_id
Regards,
Vinay Singh
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.