GATE | GATE-CS-2014-(Set-2) | Question 65

SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:

    select * from R where a in (select S.a from S) 

(A) select R.* from R, S where R.a=S.a
(D)
(B) select distinct R.* from R,S where R.a=S.a
(C) select R.* from R,(select distinct a from S) as S1 where
R.a=S1.a
(D) select R.* from R,S where R.a=S.a and is unique R


Answer: (C)

Explanation: The solution of this question lies in the data set(tuples) of Relations R and S we define. If we miss some case then we may get wrong answer.
Let’s say,

Relation R(BCA) with attributes B, C and A contains the following tuples.

B C A
---------
7 2 1
7 2 1
8 9 5
8 9 5

And Relation S(AMN) with attributes A, M, and N contains the following tuples.

A M N
---------
1 6 7
2 8 4
5 9 6
5 5 3

———————————————————————————————————–
Now ,the original Query will give result as:

“select * from R where a in (select S.a from S) ” – The query asks to display every tuple of Relation R where R.a is present in the complete set S.a.

B C A
---------
7 2 1
7 2 1
8 9 5
8 9 5

———————————————————————————————————–

Option A query will result in :

“select R.* from R, S where R.a=S.a”

B C A
---------
7 2 1
7 2 1
8 9 5
8 9 5
8 9 5
8 9 5

———————————————————————————————————–

Option B query will result in :

” select distinct R.* from R,S where R.a=S.a”

B C A
---------
7 2 1
8 9 5

———————————————————————————————————–

Option C query will result in :

“select R.* from R,(select distinct a from S) as S1 where
R.a=S1.a”
B C A
———
7 2 1
7 2 1
8 9 5
8 9 5

———————————————————————————————————–

Option D query will result in : NULL set

“select R.* from R,S where R.a=S.a and is unique R”

———————————————————————————————————-
Hence option C query matches the original result set.

Note : As mentioned earlier, we should take those data sets which can show us the difference in different queries. Suppose in R if you don’t put identical tuples then you will get wrong answers. (Try this yourself, this is left as an exercise for you ).


Quiz of this Question