Orphaned and Inconsistent Foreign References

Audit project assignments. organization_id and employee_id are mandatory; team_id may be NULL. Classify each invalid assignment as missing_employee, wrong_employee_organization, missing_team, or wrong_team_organization. If several apply, use that precedence order. Use robust joins rather than NOT IN. Return columns: assignment_id, issue.

Preparing the interactive SQL workspace...

Challenge overview

What you will practice

Audit project assignments. organization_id and employee_id are mandatory; team_id may be NULL. Classify each invalid assignment as missing_employee, wrong_employee_organization, missing_team, or wrong_team_organization. If several apply, use that precedence order. Use robust joins rather than NOT IN. Return columns: assignment_id, issue.

Difficulty: Expert
SQL concept: CTE