UGC-NET | UGC NET CS 2016 Aug – III | Question 11

Consider the following ORACLE relations :
One (x, y) = {, , , , , }
Two (x, y) = {, , , , , , , }
Consider the following two SQL queries SQ1 and SQ2 :

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

Answer: (B)
Explanation: SQ1 : SELECT * FROM One)
EXCEPT
(SELECT * FROM Two);
It will result into single tuple (2, 5) because all duplicate tuples will be removed.
SQ2 : SELECT * FROM One)
EXCEPT ALL
(SELECT * FROM Two);
It will result into 2 tuples because it will not remove duplicate.
So, option (B) is correct.
Quiz of this Question