6.3k views

The relation book (title,price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list?

select title
from book as B
where (select count(*)
from book as T
where T.price>B.price) < 5

1. Titles of the four most expensive books

2. Title of the fifth most inexpensive book

3. Title of the fifth most expensive book

4. Titles of the five most expensive books

edited | 6.3k views

The outer query selects all titles from book table. For every selected book, the subquery returns count of those books which are more expensive than the selected book. The where clause of outer query will be true for 5 most expensive book. For example count (*) will be 0 for the most expensive book and count(*) will be 1 for second most expensive book.
selected
+3
why not most expensive 4 books?

See here B and T both returning same table. when T table getting a value greater than value of  B table, it will return that row. right?

and here count <5 right?
+7
For the 5th most expensive book, there will be 4 books which are more expensive than it. So, count(*) will be 4 for it.
+5

firstly T.price>B.price

Here for T.A i.e.100 , it will get (50,20) from B.,count will be =2

Am I doing mistake?

but getting min 5 value :(

+3
B is the outer relation. So, do for every tuple of B.
+4
yes , it should be for every row of B , whole table of T should be scanned.

thanks sir :)
+1

srestha pls check this

here for 600 in B count value will be 0 which is less than 5 we should include this ?

+2
for

600 count will return 0

500 count will return 1

300 count will return 2

200 count will return 3

100 count will return 4

right?

It is asking like for value of table B=100 which T table value greater than this?
+1
i'm not getting how count for 600 is returning 0. T.A > B.A so 600 is greater than all of them so count should be 5 for 600? i'm not sure what i'm doing wrong help !!
+8

@ utkarsh , you are going in the opposite direction ,

first run the outer query .. in the outer query we select a tuple from B . Suppose we selected 100 from B.

now come inside the inner query .. it says ..

where T.price>B.price

it means .. for that selected tuple  of B , how many tuples in T are greater ..

thus it will return 4 for 100 and 0 for 600 .

its a really nice question :)

0
oh okay thanks
0
This is best to understand the correlated nested query

Note:Correlated Subquery is a sub-query that uses values from the outer query
0
@shaurya very nicely explained thanks for that..

I am also going in opposite way..

Thanks..
0
Can't understand anything. Why can't A be the answer, it's clearly visible.
0
nice [email protected] rajarshi sir
The query will select book X with the property that there are 0 or 1 or ... or 4 expensive books than X.
If X has 0 more expensive book = X is most expensive.
If X has 1 " " = X is 2nd most expensive.
.
.
.
If X has 4 more expensive = X is 5th expensive.
Therefore total books are fetched 5 and that too are 5 most expensive. Therefore D.
If we consider [100,200,300,50,400,500] as book prices,

so for books-

A($100)- count value=4... B($200)- count value=3
C($300)- count value=2... D($50)- count value=5
E($400)- count value=1... F($500)- count value=0

Am I right?
0
what do u want to say!
0
I took one example consisting five books!
(a)

condition can be said as less than 5 books should have higher price than the current book

since we have <5, then atleast we will gave 4 books,
0
m' agree here for this option,

0
Run the query on sample DB. D is answer !
0
<5 means --> 4 books, but +1book that is being compared = i.e. 5 most expensive books

,m' i right?