Problem

Given a database with two tables, 'Employees' and 'Departments', write a SQL query to find the names of all employees who work in the 'Sales' department.
Example input:
Employees:
+----+----------+---------+
| id | name     | dept_id |
+----+----------+---------+
| 1  | John Doe | 1       |
| 2  | Jane Doe | 2       |
| 3  | Joe Smith| 2       |
| 4  | Lisa Lee | 3       |
+----+----------+---------+
Departments:
+----+-------------+
| id | name        |
+----+-------------+
| 1  | Sales       |
| 2  | Marketing   |
| 3  | Engineering |
+----+-------------+
Example output:
+----------+
| name     |
+----------+
| John Doe |
+----------+

Solution

SELECT name
FROM Employees
WHERE dept_id = (SELECT id FROM Departments WHERE name = 'Sales');

/*

The solution is optimal because it uses a subquery to find the id of the department named 'Sales' and then uses that id to find the name of the employee in the Employees table.

*/