SQL query

Question

Answer given on virtual gate is a1,a3,a2

I'm getting a1,a3,a2,a1 as duplicate values are allowed in SQL. What is my mistake here ?

Comments

@just_bhavana

I am also getting a1, a2, a2, a1. Please check.

---

@joshi_nitish @mohit can you elaborate your solution?

---

@just_bhavana

As @joshi_nitish mentioned, the query is correlated. So, for every tuple of 'R' you have to go through every tuple of S. It is similar to a nested loop.

for(1 to n){

    for(1 to m){

    }

}

In the above pseudo code, you will run the inner loop 'm' times for every value of outer loop. The given query works in a similar manner.

1st row of R-

c2 is not present in any tuple of S. So, nothing gets selected from inner query.Thus, the outer query will produce a1.

Repeat this procedure for all the tuples of R, you will get the same answer as mine.

Answer 1

Answer will be  a1 a2 a2 a1

The above query is correlated query which means for every tuple of outside query the inner query will be executed completely.

Meaning of the symbol <> is not equal to

Now,

For the tuple a1 b1 c2 we will check inner query.. C2  of table R does not match with any value in S. C therefore it will return zero attribute.. Which is not equal to b1 therefore a1 will be selected.

For tuple a2 b4 c1 : c1 matches in table S corresponding value is b1 in table S which does not match with b4 therefore condition is true and a2 is selected..

Similarly a3 not selected and a2 a1 selected again.. Final answer will be a1 a2 a2 a1

Answer 2

a1,a2,a2,a1 will be the answer,as you have to select those A whose B is not equal to any B of S in which R.c=S.c

Comments

plz explain outer query how it executed??/