SQL Query for Employees in Troy | Dataford Interview Questions

Your question is SQL Query for Employees in Troy. Start with the requirements and the three 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

Dynatrace uses employee and office records to support workforce views in its internal engineering systems. Write a PostgreSQL query that returns all employees assigned to an office in Troy.

Requirements

Join employees to offices using office_id.
Return only employees whose office city is exactly Troy.
Include the employee's name, job title, office city, country, and department name. Preserve Troy employees even when their department is not assigned.
Order the results by last name, then first name, and employee ID for deterministic output.

Schema

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
first_name	VARCHAR(60)	Employee first name
last_name	VARCHAR(60)	Employee last name
job_title	VARCHAR(120)	Employee job title
office_id	INTEGER	Assigned office identifier
department_id	INTEGER	Optional department identifier

offices

Column	Type	Description
office_idPK	INTEGER	Unique office identifier
city	VARCHAR(80)	Office city
country	VARCHAR(80)	Office country

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(120)	Department name