The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+28 votes
4.5k 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

 

 

asked in Databases by Veteran (69k points)
edited by | 4.5k views

4 Answers

+42 votes
Best answer
Answer: D

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.
answered by Veteran (36.4k points)
selected by
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?
For the 5th most expensive book, there will be 4 books which are more expensive than it. So, count(*) will be 4 for it.

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

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

thanks sir :)

 srestha pls check this

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

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?
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 !!

@ 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 :)

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

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

I am also going in opposite way..

Thanks..
+7 votes
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.
answered by Boss (6.5k points)
–3 votes
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?
answered by Boss (5.5k points)
what do u want to say!
I took one example consisting five books!
–5 votes
(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,
answered by Loyal (3.4k points)
m' agree here for this option,

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

,m' i right?


Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

33,687 questions
40,231 answers
114,271 comments
38,803 users