( NOTE that STOCK is a primary Key, otherwise options can't be matched, i hope they forgotten to specify that information but in the GATE exam they will be Clearly specify )
As outer query i
| Inventory_NAME |
STOCK |
| A |
5 |
| B |
4 |
| C |
3 |
| D |
2 |
| E |
1 |
As inner query j
| Inventory_NAME |
STOCK |
| A |
5 |
| B |
4 |
| C |
3 |
| D |
2 |
| E |
1 |
take a row from outer query, then evaluate inner query.
(A,5) from i ===> there is no row in j such that j.stock > i.stock ==> result is 0 from inner query ===> 0 < 3 condition satisfies
∴ (A,5) from i should be in the output.
(B,4) from i ===> there is only one row in j (i.e., (A,5) of j ) such that j.stock > i.stock ==> result is 1 from inner query ===> 1 < 3 condition satisfies
∴ (B,4) from i should be in the output.
(C,3) from i ===> there is only two rows in j (i.e., (A,5),(B,4) of j ) such that j.stock > i.stock ==> result is 2 from inner query ===> 2 < 3 condition satisfies
∴ (C,3) from i should be in the output.
(D,2) from i ===> there is only three rows in j (i.e., (A,5),(B,4),(C,3) of j ) such that j.stock > i.stock ==> result is 3 from inner query ===> 3 < 3 condition fails
∴ (D,2) from i should be not in the output.
(E,1) from i ===> there is only four rows in j (i.e., (A,5),(B,4),(C,3),(D,2) of j ) such that j.stock > i.stock ==> result is 4 from inner query ===> 4 < 3 condition fails
∴ (E,1) from i should be not in the output.
