Which of the following SQL query deletes all tuples in the teacher relation for those teachers associated with a department located in the building with name CSE and which is in department relation:
DELETE FROM teacher WHERE dept_name IN 'CSE';DELETE FROM department WHERE building='CSE';DELETE FROM teacher WHERE dept_name IN (SELECT dept_name FROM department WHERE building = 'CSE');None of the above
ANSWER
The correct option is (c): delete from teacher where dept_name is in the subquery that selects the departments located in building 'CSE'.
Official answer: CSource: ISRO's revised and final answer key of 16 February 2024
The rows to delete are in teacher, but the building name is stored in department. So the DELETE must target teacher and use a subquery on department to pick the matching department names.
Option (a): IN needs a parenthesised list or a subquery after it. IN 'CSE' is a syntax error. It also compares dept_name with a building name.
Option (b): it deletes from department, not from teacher. The wrong relation loses its tuples and no teacher is deleted.
Option (c): the inner query returns the dept_name of every department whose building is 'CSE'. The outer statement deletes exactly those teacher tuples whose dept_name is in that set.
DELETE FROM teacher
WHERE dept_name IN (SELECT dept_name
FROM department
WHERE building = 'CSE');Option (c) does what the question asks, so (d) is false.
KEY IDEA
When the condition for a DELETE depends on another table, delete from the target table and filter it with IN (SELECT ...) on the related table.
WATCH OUT FOR
Deleting from the table that holds the condition (department) instead of the table whose rows must go (teacher), or writing IN followed by a bare value instead of a list or subquery.
From the ISRO CS 2023 paper. Every question of this paper · The full paper (PDF, ISRO) · All 4 ISRO SQL questions, with notes