GATE | GATE-CS-2006 | Question 67

Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. ties are not broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned

Query1:
  select A.customer, count(B.customer)
  from account A, account B
  where A.balance <=B.balance
  group by A.customer

Query2:
  select A.customer, 1+count(B.customer)
  from account A, account B
  where A.balance < B.balance
  group by A.customer 

Consider these statements about Query1 and Query2.

1. Query1 will produce the same row set as Query2 for 
   some but not all databases.
2. Both Query1 and Query2 are correct implementation 
   of the specification
3. Query1 is a correct implementation of the specification
   but Query2 is not
4. Neither Query1 nor Query2 is a correct implementation
   of the specification
5. Assigning rank with a pure relational query takes 
   less time than scanning in decreasing balance order 
   assigning ranks using ODBC. 

Which two of the above statements are correct?
(A) 2 and 5
(B) 1 and 3
(C) 1 and 4
(D) 3 and 5


Answer: (C)

Explanation: Query 1 and Query 2 will give the same result if all the customers have distinct balance. So, for some databases, the result of query 1 and query 2 will be same.

 
Now, let us consider a fact that all the entries in the database have the same value for balance. Ideally, all the customers should have rank 1, but both the queries will give all the customers a rank equal to the number of customers in the database.
So, both the queries do not give us the required output.

 
Thus, C is the correct choice.

 

Please comment below if you find anything wrong in the above post.

Quiz of this Question



My Personal Notes arrow_drop_up


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.