Given relations r(w, x) and s(y, z), the result of the query SELECT DISTINCT…
2000
Given relations r(w, x) and s(y, z), the result of the query
SELECT DISTINCT w, x
FROM r, s;
is guaranteed to be the same as r, provided:
- A.
r has no duplicates and s is non-empty
- B.
r and s have no duplicates
- C.
s has no duplicates and r is non-empty
- D.
r and s have the same number of tuples
Attempted by 56 students.
Show answer & explanation
Correct answer: A
The correct answer is: r has no duplicates and s is non-empty.
The query uses FROM r, s, so SQL forms the Cartesian product of r and s. Then SELECT DISTINCT w, x projects only the attributes from r and removes duplicate projected rows.
If s is non-empty, each tuple of r appears at least once in the Cartesian product. After projecting w and x, every tuple of r is present in the result.
However, SELECT DISTINCT removes duplicate tuples. Therefore, for the result to be exactly the same as r, relation r must already have no duplicate tuples.
If s is empty, the Cartesian product is empty, so the result is empty. If r has duplicates, DISTINCT removes them. Hence both conditions are necessary: r has no duplicates and s is non-empty.