The relation book ( title , price) contains the titles and prices of different…
2005
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 - A.
Titles of the four most expensive books
- B.
Title of the fifth most inexpensive book
- C.
Title of the fifth most expensive bookTitles of the five most expensive books
- D.
Titles of the five most expensive books
Attempted by 63 students.
Show answer & explanation
Correct answer: D
Explanation: For each row B the subquery counts how many books have a price greater than B.price.
The inner query (select count(*) ... where T.price > B.price) returns the number of books priced higher than the current book B.
The outer WHERE condition requires that this count is less than 5, so at most four books can be more expensive than B.
Therefore B has rank 1 through 5 when books are ordered by descending price — i.e., B is among the five most expensive books.
Note: Because prices are unique, exactly five rows will satisfy the condition when the table has at least five books. If the table has fewer than five books, the query returns all titles.
Answer: Titles of the five most expensive books.
A video solution is available for this question — log in and enroll to watch it.