edited by
30,378 views
69 votes
69 votes

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 by

5 Answers

Best answer
90 votes
90 votes
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.
selected by
188 votes
188 votes

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,

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 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:
Book 
C
D
E
F
G


These are the titles of most expensive 5 Books.


If this explanation is helpful to you in any way, please upvote. :)

edited by
8 votes
8 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.
1 votes
1 votes

For every book in B find the no of books costlier in T.

Print all those books where this no is less than 5.

Print where 0, 1, 2, 3, 4 books are costlier than the book in B.

Answer:

Related questions

38 votes
38 votes
1 answer
2
Kathleen asked Sep 23, 2014
16,788 views
Consider the join of a relation $R$ with a relation $S$. If $R$ has $m$ tuples and $S$ has $n$ tuples then the maximum and minimum sizes of the join respectively are$m+n$...
33 votes
33 votes
4 answers
4
Kathleen asked Sep 11, 2014
13,857 views
A clustering index is defined on the fields which are of typenon-key and orderingnon-key and non-orderingkey and orderingkey and non-ordering