ITPEC FE Subject A October 2024 Question 20

SQL MINUS (set difference) — find departments not present in a specific region.

Subquery breakdown:

• •SELECT DNO FROM DEPT → {1, 2, 3}
• •SELECT DNO FROM DEPT_LOCS WHERE Region='L2' → {2, 3}
• •MINUS: {1, 2, 3} - {2, 3} = {1}

Result: employees with DNO = 1 → John Bate (20000) and Sadat Hossain (50000)

Why not others:
- (a) NULL — MINUS does not produce an empty set here; department 1 remains
- (c) 4 rows — incorrectly includes departments 3, which exist in region L2
- (d) 5 rows — no filtering applied at all

Key rule: MINUS returns rows from the first query that are not in the second query (set difference). It is the SQL standard equivalent of EXCEPT.

AI-generated — may contain errors