ISRO 2024

Question

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:

1. DELETE FROM teacher
WHERE dept_name IN ‘CSE’;
2. DELETE FROM department
WHERE building = ‘CSE’;
3. DELETE FROM teacher
WHERE dept_name IN (
   SELECT dept_name
   FROM department
   WHERE building = ‘CSE);
4. DELETE FROM teacher
WHERE dept_name IN ‘CSE’;

Answer 1

Option c)

Inner query selects all the department names from the relation ‘department’ where the department is in CSE building.

Outer query will now take all the teachers associated (via the inner query) and delete them.

Answer 2

Correct Answer is C

DELETE FROM teacher
WHERE dept_name IN (
   SELECT dept_name
   FROM department
   WHERE building = 'CSE'
);