Employee department names

SQL data engineering interview problem. Difficulty: beginner. Pattern: Joins. About 8 minutes. Free to practice.

The employees table stores only a department number (department_id), which is not very readable. The real department names live in a second table called departments. Join the 2 tables on department_id so you can show each employee's name next to the name of their department. Return name and department_name, sorted by name. Only return employees whose department exists in the departments table.

Requirements

• Join employees and departments where the two department_id values are equal.
• Use an INNER JOIN, so only employees with a matching department appear.
• Return the employee name and the department_name.
• Sort by name from A to Z.

Constraints

• The 2 tables are employees and departments.
• departments contains three departments: Engineering, Sales and Marketing.
• Return only these 2 columns, in this order: name, department_name.

Examples

Input: employees departments name | department_id department_id | department_name Alice Chen | 1 1 | Engineering Omar Ali | 2 2 | Sales 3 | Marketing Output: name | department_name Alice Chen | Engineering Omar Ali | Sales Why this passes: INNER JOIN keeps only employees with a matching department_id. Marketing has no employees in the seed.

Topics: lakebench, sql, inner join.

More SQL interview questions · All interview problems · Learn data engineering