ITPEC FE Subject A October 2025 Question 21

Topic: Databases

Which of the following is the clause that is inserted into blank A of the SQL statement in order to display the names of employees who work in the same department as any employee located in "New York"?

Employees (employee_id, employee_name, salary, department_id)
Departments (department_id, department_name, location)

[SQL command]
SELECT employee_name FROM Employees WHERE A

Explanation

IN with a subquery on Departments where location = 'New York' — the location lives in Departments, so a subquery on that table supplies the set of department ids to match.

  • •The subquery reads Departments, the only table with a location column, and returns every department_id located in New York.
  • •The outer query keeps each employee whose department_id is a member of that set. IN tests membership in a list of values, so it works whether New York has one department or several.

Answer (a)

Why not others:

  • •(b) — NOT IN inverts the filter and lists the employees of departments outside New York.
  • •(c) — the subquery reads Employees, which has no location column, so it cannot pick out New York departments.
  • •(d) — the same wrong table, and = ALL demands equality with every returned value, which fails as soon as New York has more than one department.

Key rule: Filter one table by a condition held in another with column IN (SELECT column FROM other_table WHERE …), and check that the subquery reads the table that actually holds the condition column.

Databases · AI-written, may contain errors

Practice this exam

This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.