GATE CSE 2005 | Question: 77, ISRO2016-55

Question

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 book
• D. Titles of the five most expensive books

Comments

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

---

This question again got repeated in yesterday’s BARC exam 2020 second attempt

Answer 1

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.

Comments

In the 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 )

It will give, Titles of the six most expensive books.

---

@srestha ma'am

why, 600 count will return 0 ----> why not NULL

please clarify me...

---

@satyaAchar $Count(*)$ always count  the number of tuples and it returns Value not $NULL$ as other aggreagated functions returns

Answer 2

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. :)

Comments

thanks…

how do i give upvote ? i mean no upvote button is present

---

clear explanation thanks

---

🔥

Answer 3

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.

Answer 4

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 5

best answer

Answer 6

${\color{Blue} 5} $ ${\color{Blue} most} $ ${\color{Blue}expensive } $ ${\color{Blue}books } $

Just make a random table, and then, use two pointers T and B, now, just iterate the T pointer for each of B pointer

You will realise, this is like a nested for loop implementation.

This property is the soul of nested sub queries

You have to take example and solve

Now How easy it becomes

 

Thanks

Shashank