Identify Employees with Invalid Department References
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
EMPLOYEE
+-----+-----+--------+
| eid | did | ename |
+-----+-----+--------+
| 1 | 10 | Alice |
| 2 | 11 | Bob |
| 3 | 99 | Carol |
+-----+-----+--------+
DEPARTMENT
+-----+---------+
| did | dname |
+-----+---------+
| 10 | Sales |
| 11 | Finance |
+-----+---------+
##### Scenario
A company database holds EMPLOYEE and DEPARTMENT tables. Management needs to find employees referencing non-existent departments so the data can be cleaned.
##### Question
Using SQL, return all rows from EMPLOYEE whose did is not present in DEPARTMENT.
##### Hints
Think anti-join patterns such as LEFT JOIN … WHERE department.did IS NULL, or use NOT EXISTS/NOT IN.
Quick Answer: This question evaluates SQL data manipulation skills and data-quality reasoning, specifically assessing understanding of referential integrity and the detection of orphaned foreign-key references between related tables.