Consider the following ORACLE relations : One (𝑥,𝑦)={<2, 5>, <1, 6>, <1, 6>,…
2016
Consider the following ORACLE relations : One (𝑥,𝑦)={<2, 5>, <1, 6>, <1, 6>, <1, 6>, <4, 8>, <4, 8>}
Two (𝑥,𝑦)={<2, 55>, <1, 1>, <4, 4>, <1, 6>, <4, 8>, <4, 8>, <9, 9>, <1, 6>}
Consider the following two 𝑆𝑄𝐿 queries 𝑆𝑄1 and 𝑆𝑄2 :
SQ1 :
SELECT * FROM One)
EXCEPT
(SELECT * FROM Two);
SQ2 :
SELECT * FROM One)
EXCEPT ALL
(SELECT * FROM Two);
For each of the SQL queries, what is the cardinality (number of rows) of the result obtained when applied to the instances above ?
- A.
2 and 1 respectively
- B.
1 and 2 respectively
- C.
2 and 2 respectively
- D.
1 and 1 respectively
Attempted by 81 students.
Show answer & explanation
Correct answer: B
Answer: 1 row for the plain EXCEPT, and 2 rows for EXCEPT ALL.
Reasoning:
Plain EXCEPT operates on the set of distinct rows: it returns each distinct row from the first relation that does not appear (as a value) in the second relation. Duplicates in the first relation are collapsed.
EXCEPT ALL operates on multisets: for each distinct row value v, the result contains max(0, count_in_first(v) - count_in_second(v)) copies of v.
Apply this to the given instance: suppose the first relation contains two copies of a tuple t1 and one copy of a different tuple t2, while the second relation contains one copy of t1.
For plain EXCEPT: consider distinct values in the first relation {t1, t2} and remove any that appear in the second ({t1}). The remaining distinct value set is {t2}, so 1 row.
For EXCEPT ALL: subtract multiplicities. For t1: 2 - 1 = 1 copy remains. For t2: 1 - 0 = 1 copy remains. Total rows = 1 + 1 = 2.
A video solution is available for this question — log in and enroll to watch it.