SQL

Given two tables below with 'Dept' of Employee indicating the department an employee belongs to. If Dept is NULL, the employee either does not belong to any department or his belonging department is unknown.

Which employee(s) belongs to a department that doesn't exist in Department table? Display his/her EmpID, Dept, and Gender. If the output contains more than one employee, they should be sorted by their EmpID in ascending order.

This query can be solved in many different ways, including (1) ~ (6) described below. Do not include hard coded data values such as 'Sales' and 'E219' in the solution statement.

(1) By a SELECT statement with a regular non-correlated subquery and no table joins, no set operators. (2) By a SELECT statement with a correlated subquery and keyword EXISTS but no table joins, no set operators. (3) By a SELECT statement which uses a CTE [all_dept] with a regular non-correlated subquery but no table joins, no set operators. [all_dept] is defined by "select Dname from Department". (4) Same as (3) but the CTE is used in a table join. No subqueries and no set operators are used in the SELECT statement. (5) By a SELECT statement with a table join and no subqueries, no set operators. (6) By a SELECT statement with set operators but no table joins, no subqueries.

DepartmentI Employee Dname Office EmpID Dept Gender K-71E003 Sales M-22 E012 HR R&D Sales Shipping M-23E354 NULL IT K-71E066 Sals E219 IT E008 Services M