ITPEC FE Subject A October 2024 Question 20

John Bate 20000 and Sadat Hossain 50000 — MINUS leaves only department 1, the one department with no location in region L2.

• •SELECT DNO FROM DEPT → {1, 2, 3}
• •SELECT DNO FROM DEPT_LOCS WHERE Region = 'L2' → {2, 3}: department 2 is in L2, department 3 in L3 and L2
• •MINUS: {1, 2, 3} − {2, 3} = {1}

The outer query returns the employees with DNO 1: John Bate (20000) and Sadat Hossain (50000).

Answer (b)

Why not others:

• •(a) NULL — the difference {1} is not empty; and an empty subquery result would return no rows, not a row of NULLs
• •(c) — keeps Katherine Li and Shuvashish Bose of department 3, but department 3 has a location in L2 and is removed, even though it is also in L3
• •(d) — all five employees, as if the MINUS removed nothing and the filter were just SELECT DNO FROM DEPT

Key rule: MINUS (standard SQL EXCEPT) keeps the values of the first query that never appear in the second; a department with any row in L2 is excluded, whatever its other regions.

Databases · AI-written, may contain errors