Consider the relation account (customer, balance) where the customer is a… — GATE CS 2006 Q67

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.$

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

View worked solution

Answer: C

To determine which statements are correct, let us analyze both queries with a sample database of three customers:

$C_1$ with balance $100$
$C_2$ with balance $100$
$C_3$ with balance $50$

According to the specification, the correct ranks should be: $C_1 \to 1$ , $C_2 \to 1$ , and $C_3 \to 3$ (rank 2 is skipped).

1. Analysis of Query 1

Query 1 uses the join condition A.balance <= B.balance:

For $C_1$ ( $100$ ): $B.balance \ge 100 \implies \{C_1, C_2\}$ . Count is $2$ .
For $C_2$ ( $100$ ): $B.balance \ge 100 \implies \{C_1, C_2\}$ . Count is $2$ .
For $C_3$ ( $50$ ): $B.balance \ge 50 \implies \{C_1, C_2, C_3\}$ . Count is $3$ .

The output ranks are $C_1 \to 2$ , $C_2 \to 2$ , $C_3 \to 3$ . This is incorrect because the top rank is $2$ instead of $1$ . Thus, Query 1 is incorrect.

2. Analysis of Query 2

Query 2 uses the join condition A.balance < B.balance:

Because of the inner join, any customer $A$ who has the absolute maximum balance will find no customer $B$ with a strictly greater balance (i.e., A.balance < B.balance is false for all $B$ ). Consequently, the maximum balance customer(s) are completely filtered out and omitted from the final output. Thus, Query 2 is also incorrect.

Since neither query is correct, Statement 4 is correct.

3. Analysis of Statement 1

If the database is empty, both Query 1 and Query 2 produce an empty result set (they are identical).
For any non-empty database, Query 1 will output the maximum balance customer(s), whereas Query 2 will omit them. Thus, their outputs will differ.

Hence, Query 1 produces the same row set as Query 2 for some (empty) but not all databases. This makes Statement 1 correct.

Therefore, statements 1 and 4 are correct, matching Option C.

Free practice · Your answers on this page are not saved.

GATE CS 2006 Q67 — Tuple Calculus and SQL

1. Analysis of Query 1

2. Analysis of Query 2

3. Analysis of Statement 1

Related questions