Topic: Database Management Systems
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:
- A. DELETE FROM teacher WHERE dept_name IN 'CSE';
- B. DELETE FROM department WHERE building='CSE';
- C. DELETE FROM teacher WHERE dept_name IN (SELECT dept_name FROM department WHERE building = 'CSE');
- D. None of the above
Answer: C. DELETE FROM teacher WHERE dept_name IN (SELECT dept_name FROM department WHERE building = 'CSE');
Explanation: To delete records from the `teacher` table based on a condition matching the `department` table, a subquery is needed. Option (c) correctly identifies the relevant departments using `SELECT dept_name FROM department WHERE building = 'CSE'` and deletes the associated teachers.