Find employees who are assigned to department but their department is unavailable in department table.
.jpg)
SET DEFINE OFF
CREATE TABLE EMPLOYEE ( EMP_ID INTEGER,
EMP_NAME VARCHAR2(20),
SALARY NUMBER(8, 2),
DEPT_ID INTEGER,
MANAGER_ID INTEGER);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (1, 'Emma Thompson', 12071.0, 1, 6);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (2, 'Daniel Rodriguez', 14488.0, 1, 6);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (3, 'Olivia Smith', 23799.0, 1, 6);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (4, 'Noah Johnson', 10288.0, 2, 8);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (5, 'Sophia Martinez', 21972.0, 1, 2);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (6, 'Liam Brown', 20701.0, 3, 2);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (7, 'Ava Garcia', 19173.0, 3, 2);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (8, 'William Davis', 15892.0, 2, NULL);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (9, 'Robin Kulkarni', 17000.0, 4, 1);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (10, 'Rahul Bose', 19222.0, NULL, NULL);
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, SALARY, DEPT_ID, MANAGER_ID)
VALUES (11, 'Harish Bose', 23002.0, 5, NULL);
select e.emp_id,e.emp_name,e.dept_id from employee e left join dept d
ON E.DEPT_ID = D.DEPT_ID
where d.dept_id is null and e.dept_id is not null;
Output:
11 Harish Bose 5
Comments
Post a Comment