Use FULL JOIN for Complete Coverage | Dataford Interview Questions

Your question is Use FULL JOIN for Complete Coverage. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

IBM HR Analytics needs a reconciliation report between employee assignments and the department master data. Write a PostgreSQL query using a FULL JOIN so that no employee or department record is omitted.

Requirements

Return employee ID, employee name, department ID, and department name.
Use COALESCE to display the department ID from whichever table contains it.
Add a record_status column identifying Matched, Unmatched employee, or Unmatched department.
Sort by department ID ascending, then employee ID ascending, with null values last.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INT	Assigned department identifier
job_title	VARCHAR(100)	Employee job title

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
location	VARCHAR(100)	Department office location