CREATE TABLE department (DEPARTMENT_ID NUMERIC (4) PRIMARY KEY, DEPARTMENT_NAME

Question

CREATE TABLE department
(DEPARTMENT_ID NUMERIC (4) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(20) NOT NULL UNIQUE,
LOCATION VARCHAR(20) NOT NULL);

CREATE TABLE employee
(EMPLOYEE_ID NUMERIC (4) PRIMARY KEY,
EMPLOYEE_NAME VARCHAR(20) NOT NULL,
JOB_TITLE VARCHAR(50) NOT NULL,
MANAGER_ID NUMERIC (4) REFERENCES employee(EMPLOYEE_ID) ON DELETE SET NULL,
HIRE_DATE DATE NOT NULL,
SALARY NUMERIC (9, 2) NOT NULL,
COMMISSION NUMERIC (9, 2),
DEPARTMENT_ID NUMERIC (4) REFERENCES department(DEPARTMENT_ID));
  
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;

INSERT INTO employee VALUES (7839, 'KING', 'PRESIDENT', NULL, STR_TO_DATE('20-NOV-01','%d-%M-%Y'), 5000, NULL, 50);
INSERT INTO employee VALUES (7596, 'JOST', 'VICE PRESIDENT', 7839, STR_TO_DATE('04-MAY-01','%d-%M-%Y'), 4500, NULL, 50);
INSERT INTO employee VALUES (7603, 'CLARK', 'VICE PRESIDENT', 7839, STR_TO_DATE('12-JUN-01','%d-%M-%Y'), 4000, NULL, 50);
INSERT INTO employee VALUES (7566, 'JONES', 'CHIEF ACCOUNTANT', 7596, STR_TO_DATE('05-APR-01','%d-%M-%Y'), 3000, NULL, 10);
INSERT INTO employee VALUES (7886, 'STEEL', 'PUBLIC ACCOUNTANT', 7566, STR_TO_DATE('08-MAR-03','%d-%M-%Y'), 2500, NULL, 10);
INSERT INTO employee VALUES (7610, 'WILSON', 'BUSINESS ANALYST', 7596, STR_TO_DATE('03-DEC-01','%d-%M-%Y'), 3000, NULL, 20);
INSERT INTO employee VALUES (7999, 'WOLFE', 'TEST ANALYST', 7610, STR_TO_DATE('15-FEB-02','%d-%M-%Y'), 2500, NULL, 20);
INSERT INTO employee VALUES (7944, 'LEE', 'REPORTING ANALYST', 7610, STR_TO_DATE('04-SEP-06','%d-%M-%Y'), 2400, NULL, 20);
INSERT INTO employee VALUES (7900, 'FISHER', 'SALES EXECUTIVE', 7603, STR_TO_DATE('06-DEC-01','%d-%M-%Y'), 3000, 500, 30);
INSERT INTO employee VALUES (7921, 'JACKSON', 'SALES REPRESENTATIVE', 7900, STR_TO_DATE('25-FEB-05','%d-%M-%Y'), 2500, 400, 30);
INSERT INTO employee VALUES (7952, 'LANCASTER', 'SALES CONSULTANT', 7900, STR_TO_DATE('06-DEC-06','%d-%M-%Y'), 2000, 150, 30);
INSERT INTO employee VALUES (7910, 'SMITH', 'DATABASE ADMINISTRATOR', 7596, STR_TO_DATE('20-DEC-01','%d-%M-%Y'), 2900, NULL, 40);
INSERT INTO employee VALUES (7788, 'SCOTT', 'PROGRAMMER', 7910, STR_TO_DATE('15-JAN-03','%d-%M-%Y'), 2500, NULL, 40);
INSERT INTO employee VALUES (7876, 'ADAMS', 'PROGRAMMER', 7910, STR_TO_DATE('15-JAN-03','%d-%M-%Y'), 2000, NULL, 40);
INSERT INTO employee VALUES (7934, 'MILLER', 'PROGRAMMER', 7876, STR_TO_DATE('25-JAN-02','%d-%M-%Y'), 1000, NULL, 40);
INSERT INTO employee VALUES (8000, 'BREWSTER', 'TBA', NULL, STR_TO_DATE('22-AUG-13','%d-%M-%Y'), 2500, NULL, NULL);

commit;

my current query works however all employees are returned. How do I only show most recent hired from each department?

select
d.department_name,
d.department_id,
e.employee_name,
e.employee_id,
e.job_title,
NVL(to_char(MAX(e.HIRE_DATE),'DD-MON-YYYY'),'31-DEC-9999') as hire_date
from department d join employee e
on e.department_id=d.department_id
group by d.department_id, d.department_name, e.employee_name, e.employee_id, e.job_title
order by d.department_name,e.employee_name;

Write a SQL SELECT statement to find out the most recently hired employees in each department. Your SELECT statement must display the department ID, department name, employee ID, employee name, job title, and hire date. Any employee who does not belong to any department is excluded from your output. .The hire date must be displayed in the DD-MON-YYYY format (4-digit year). · You cannot use hard-coded values (e.g., WHERE department id = 10) in your SELECT statement .Sort your output in ascending order by department name and then employee name. No more than one SELECT statement. Hint A subquery may be needed in your SELECT statement Your statement's output should match the following format: DEPARTIENT ID DEPARTYENT NAEEMPLOYEE ID EMPLOTEE NAME JOB TITLE HIRE DATE 10 7886 STEEL PUBLIC ACCOUNTANT 08-MAR-2003

Explanation / Answer

Please run the below query

========================
SELECT
D.DEPARTMENT_NAME,
D.DEPARTMENT_ID,
E.EMPLOYEE_NAME,
E.EMPLOYEE_ID,
E.JOB_TITLE,
E.HIRE_DATE
FROM DEPARTMENT D JOIN EMPLOYEE E
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

WHERE (E.DEPARTMENT_ID,E.HIRE_DATE)

IN

(SELECT E.DEPARTMENT_ID, MAX(E.HIRE_DATE) FROM EMP GROUP BY E.DEPARTMENT_ID);

=========================================

If you have any quiries Please comment

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---