edited by
20,903 views
85 votes
85 votes

Consider the relation account (customer, balance) where the 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.

$\text{Query1:}$ 

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

$\text{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 Query$1$ and Query$2.$

  1.  Query$1$ will produce the same row set as Query$2$ for some but not all databases. 
  2.  Both Query$1$ and Query $2$ are a correct implementation of the specification 
  3.  Query$1$ is a correct implementation of the specification but Query$2$ is not 
  4.  Neither Query$1$ nor Query$2$ 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?

  1. $2$ and $5$
  2. $1$ and $3$
  3. $1$ and $4$
  4. $3$ and $5$
edited by

6 Answers

Best answer
88 votes
88 votes

Both Query$1$ and Query$2$ are not correct implementations because: Assume that we have a table with $n$ customers having the same balance. In that case Query$1$ will give rank $n$ to each customer. But according to the question the rank assigned should be $1.$ And Query$2$ will return an empty result set $($as it will never return rank $1).$ So statement $4$ is correct. For the same reason Query$1$ is wrong though it is true if we assume the relation set is empty. Statements $2$ and $3$ are false as $4$ is TRUE. Statement $5$ is false as a single scan should be faster than a join query. So, the best option should be C, though $1$ is not technically correct.

A correct query to achieve the task would be: 

select A.customer, ( 
    select 1+count(*) 
    from account B
    where A.balance < B.balance
) from account A
edited by
38 votes
38 votes

Only statement 4 is correct

Page 1

Page 2

26 votes
26 votes

Assume that each customers has Rs.$100$ in their bank account account and there are only $5$ customers.

Customer balance
C1 100
C2 100
C3 100
C4 100
C5 100

Consider query 1.

 

from account A, account B  \\ This line will do cross-product of the table with itself i.e. we will get 5*5 = 25 tuples

where A.balance <= B.balance  \\ All of the 25 tuples will satisfy this equation as every balance = 100 i.e. same

group by A.customer \\ this will make group of tuples where each tuple's A.customer has same name i.e there will be 5 groups {C1} ,{C2},{C3},{C4],{C5} and each will have 5 tuples

Group A.Customer A.Balance B.Customer B.balance
group 1 C1 100 C1 100
C1 100 C2 100
C1 100 C3 100
C1 100 C4 100
C1 100 C5 100
group 2 C2 100 C1 100
C2 100 C2 100
C2 100 C3 100
C2 100 C4 100
C2 100 C5 100
group 3 C3 100 C1 100
C3 100 C2 100
C3 100 C3 100
C3 100 C4 100
C3 100 C5 100
group 4 C4 100 C1 100
C4 100 C2 100
C4 100 C3 100
C4 100 C4 100
C4 100 C5 100
group 5 C5 100 C1 100
C5 100 C2 100
C5 100 C3 100
C5 100 C4 100
C5 100 C5 100

select A.customer, count(B.customer)  \\ now this will run and we will get 5 tuples as output.

A.Customer count (B.customer)
C1 5
C2 5
C3 5
C4 5
C5 5

But according to question instead of $5$ we should have got $1$ as output for each tuple.

$\therefore$ query1 is giving wrong output.


Consider query 2.

 

from account A, account B  \\ This line will do cross-product of the table with itself i.e. we will get 5*5 = 25 tuples

where A.balance < B.balance  \\ none of the 25 tuples will satisfy this equation as every balance = 100

so we will get empty set as output and so nothing will be printed as output.


So both query 1 and query 2 are giving incorrect result.

$\implies$ statement $4$ is correct

Since statement $4$ is present only in option $C$

$\therefore$ Option $C$ is the correct choice.

5 votes
5 votes
(c) 1 and 4

both are not correct quries

both quries will produce same set of rows if the balance are distinct(but still wont meet the spec given)
edited by
Answer:

Related questions