in Databases edited by
495 views
1 vote
1 vote

What will be the result of this query?

Select item from inventory i where 
(Select count(*) from inentory j where j.stock>i.stock)<3;
  1. Bottom 3 items of the stock
  2. Top 3 items of the stock
  3. Top 2 items of the stock
  4. Bottom 2 items of the stock

 

Isn't the answer A?

But B is provided as an answer.

in Databases edited by
495 views

3 Comments

adarsh_1997 this one also :D

0
0
it should be B only... take the sample table then you will understand
0
0
Tried but couldn't figure it out. Please explain
0
0

1 Answer

3 votes
3 votes
Best answer

( 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.

selected by

2 Comments

Great, Thank You :)
0
0
its like nested for loops
0
0