4. PGT Computer Science Question Discussion

4. PGT Computer Science Question Discussion

 

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

Let We Have two Tables B and T as in inner query we alias Book as B and T so we for explanation we let two tables of same content practically it will be one but sql internally work on it as two tables bcoz of alias 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

Now first execute 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,

Let Choose the value of T for testing:

Book Price

 

A 100

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. i.e. option (C)