CAUTION: LONG EXPLANATION.
Say, we have tables B and T
Table B
Book |
Price |
A |
100 |
B |
200 |
C |
500 |
D |
600 |
E |
800 |
F |
1200 |
G |
1500 |
Table T
Book |
Price |
A |
100 |
B |
200 |
C |
500 |
D |
600 |
E |
800 |
F |
1200 |
G |
1500 |
It is the case of Correlated Nested Query where the attribute of the outer table is used for comparison in the inner query [sub query].
(select count(*) from book as T where T.price>B.price)
B.Price has been used in the sub query but B.Price is an attribute of outer query i.e Table B. In case of correlated nested query,for every tuple chosen in outer query, "sub query" runs one time to give the result. So,
choose the value of B for testing: say,
Now, for this tuple of B, run the subquery :
select count(*)
from book as T
where T.price>B.price
Now, here, B.Price is equal to 100 and the above query is counting the no. of tuples in T where T.Price > B.Price i.e.
"How many books are expensive than A ?"
Table T
Book |
Price |
Check if |
Yes/No |
|
|
|
|
A |
100 |
100>100 |
No |
B |
200 |
200>100 |
Yes |
C |
500 |
500>100 |
Yes |
D |
600 |
600>100 |
Yes |
E |
800 |
800>100 |
Yes |
F |
1200 |
1200>100 |
Yes |
G |
1500 |
1500>100 |
Yes |
So, Count(*) = 6.
The Outer Query reduces to
select title
from book as B
where 6 < 5 //Where condition returns "false" for Book A So, this chosen row needs to be dropped as it failed the test.
Similarly, We run the tests for tuples B,C,D,E,F,G in Table B and check if it passes the condition or not.
Book |
Price |
Count(*) |
Check if "Where" Condition Passes |
Include it or Not |
A |
100 |
6 |
6<5 |
No |
B |
200 |
5 |
5<5 |
No |
C |
500 |
4 |
4<5 |
Yes |
D |
600 |
3 |
3<5 |
Yes |
E |
800 |
2 |
2<5 |
Yes |
F |
1200 |
1 |
1<5 |
Yes |
G |
1500 |
0 |
0<5 |
Yes |
Final Answer:
These are the titles of most expensive 5 Books.
If this explanation is helpful to you in any way, please upvote. :)