SQL Question;

CREATE TABLE department

(DEPARTMENT_ID NUMBER (4) PRIMARY KEY,

DEPARTMENT_NAME VARCHAR2(20) NOT NULL UNIQUE,

LOCATION VARCHAR2(20) NOT NULL);

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 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, '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;

Question:

Most recent hire from each department.

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