Professor-Course Cross-Department Query | Dataford Interview Questions

Your question is Professor-Course Cross-Department Query. 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

Catalist maintains an internal course catalog for technical and research training. Write a PostgreSQL query that returns each professor's name and every course they teach outside their own department.

Requirements

Join professors to their teaching assignments and the associated courses.
Keep only assignments where the professor's department differs from the course department.
Return each professor and course pair once, even if duplicate assignment records exist.
Sort the output by professor name, then course name, both ascending.

A professor or course with a NULL department should not qualify as an outside-department match because the comparison is unknown.

Schema

professors

Column	Type	Description
professor_idPK	INT	Unique professor identifier
professor_name	VARCHAR(100)	Professor's full name
department	VARCHAR(100)	Professor's academic department

courses

Column	Type	Description
course_idPK	INT	Unique course identifier
course_name	VARCHAR(150)	Course title
department	VARCHAR(100)	Department that owns the course

professor_courses

Column	Type	Description
professor_id	INT	Assigned professor identifier
course_id	INT	Assigned course identifier