Correct answer is Option (A).
Explanation :
Query 1 and Query 3 are giving exactly same result i.e identical rows. Similarly, Query 2 and Query 4 are also giving identical rows. But all the 4 queries are same in terms of identical row sets.
Identical row Set - it means a set in which all elements (here rows) are same but no duplicate rows are present. Since it is defined in terms of set, and set don't allow duplicate entries.
So all the query will give same identical row set but (Q1,Q3) will give same rows and (Q2,Q4) will give same rows.
Lets take an example:
Table- ENROLLED
STUDENT
|
COURSE |
A |
1 |
A |
2 |
B |
1 |
B |
2 |
C |
1 |
C |
2 |
Table- PAID
STUDENT
|
AMOUNT
|
A |
10 |
B |
11 |
C |
12 |
Query1:
select student from enrolled where student in (select student from paid)
Output - inner query- (a,b,c) Final result- (a,a,b,b,c,c)
Query2:
select student from paid where student in (select student from enrolled)
Output - inner query- (a,a,b,b,c,c) Final result- (a,b,c)
Query3:
select E.student from enrolled E, paid P where E.student = P.student
Output- 1st Cartesian product of (Enrolled , Paid) . Then Final result- (a,a,b,b,c,c)
Query4:
select student from paid where exists
(select * from enrolled where enrolled.student = paid.student)
Output- It's a corelated subquery. So, a tuple of paid is selected even if there is atleast 1 tuple in enrolled table such that there is match in student attribute. Final result- (a,b,c)
Thus, no of rows may be different. But Row set of all the query is (a,b,c).
Answer is option 1: All queries return identical row sets for any database