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

edited | 6.9k views
+1
will be easy to see if we think like ( B.price <T.price )

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

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.

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.

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?

1
2
3
4
5
6