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.
Overview: 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.
Using SQL, return all rows from EMPLOYEE whose did is not present in DEPARTMENT.
Tables
EMPLOYEE(eid INTEGER, did INTEGER, ename VARCHAR)
DEPARTMENT(did INTEGER, dname VARCHAR)
Hints
- Use an anti-join: LEFT JOIN DEPARTMENT d ON d.did = e.did and filter WHERE d.did IS NULL.
- Or use NOT EXISTS to check absence of a matching department row.