GATE CS 2005 Q77 — Tuple Calculus and SQL

To understand what the SQL query returns, let's analyze the correlated subquery:

select count(*)
from book as T
where T.price > B.price

For any book $B$ in the outer query, this subquery counts how many books $T$ have a price strictly greater than the price of $B$. Let this count be $C$.

The outer query selects the title of book $B$ if and only if $C < 5$. Since no two books have the same price, we can rank the books by price in descending order:

• 1^st most expensive book: There are $0$ books more expensive than it ($C = 0$). Since $0 < 5$, it is selected.
• 2^nd most expensive book: There is $1$ book more expensive than it ($C = 1$). Since $1 < 5$, it is selected.
• 3^rd most expensive book: There are $2$ books more expensive than it ($C = 2$). Since $2 < 5$, it is selected.
• 4^th most expensive book: There are $3$ books more expensive than it ($C = 3$). Since $3 < 5$, it is selected.
• 5^th most expensive book: There are $4$ books more expensive than it ($C = 4$). Since $4 < 5$, it is selected.
• 6^th most expensive book: There are $5$ books more expensive than it ($C = 5$). Since $5 \not< 5$, it is not selected.

Thus, the query selects the titles of the five most expensive books.