The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+32 votes
6.9k 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 (59.7k points)
edited by | 6.9k views
+1
will be easy to see if we think like ( B.price <T.price )

5 Answers

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

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 ?

+3
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 !!
+11

@ 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
0

In database Nested query runs like nested for loop in C programming.

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

will be easy to see if we think like ( B.price <T.price )

I will give, Titles of the six most expensive books

+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 Loyal (6.3k points)
+3 votes

CAUTION: LONG EXPLANATION.

Say, we have tables 
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 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 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, 
Book  Price
   
A 100
 
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 y/n
       
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 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:
Book 
C
D
E
F
G


These are the titles of most expensive 5 Books.
 

answered by (151 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 Active (5k points)
0
what do u want to say!
0
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 Active (3.3k points)
0
m' agree here for this option,

please help ?
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?
Answer:

Related questions



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

44,284 questions
49,774 answers
164,288 comments
65,856 users