Monday 8 June 2020

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             66<5 No
 B 200 5 5<5 No
 C     500 4 4<5 Yes
 D 600 33<5 Yes
 E 800 22<5 Yes
 F 1200 11<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)

No comments:

Post a Comment