Consider the relation account (customer, balance) where customer is a primary key and there are no null…

Question

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 broken, 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?

Accepted Answer

Correct answer: C — A correct ‘rank with gaps’ (competition-ranking) query must satisfy two things at once: (a) every row’s rank must equal 1 + the count of rows with a strictly greater balance, and (b) every customer — including whoever holds the single largest balance — must still appear exactly once in the output. A plain SQL self-join written as ‘from account A, account B where <predicate> group by A.customer’ silently drops any A for which the predicate matches zero B partners: group by can only form a group out of rows that survive the where filter, so a customer with zero matching partners never gets a row at all, not even a rank-0 row. Query1 tests A.balance <= B.balance, so its count includes every customer tied with A as well as everyone strictly above A. For a customer at the very top, that count equals the size of the top tie-group, not 1. Take balances 100, 100, 90: Query1 returns (100, 2), (100, 2), (90, 3) — the two tied leaders both get 2 instead of the required 1, and rank 2 is not skipped as the specification demands. So Query1 gets requirement (a) wrong for every tied group; it only satisfies the spec when no two customers ever tie. Query2 tests the strict A.balance < B.balance and adds 1, so wherever it produces a row, the value is exactly 1 + (count of strictly-greater balances) — the correct rank arithmetic, ties included. But the customer(s) holding the overall largest balance have zero partners with a strictly greater balance, so their row is filtered out before group by ever sees them. On the same 100, 100, 90 example, Query2 returns only (90, 3) — both rank-1 customers are missing entirely, not merely mis-ranked. This happens in every non-empty database, tie or no tie, because the maximum balance always has nobody above it — requirement (b) always fails for Query2 whenever there is at least one customer. So Query1 and Query2 each break a different requirement: Query1 mis-values tied groups (fails (a)); Query2 always omits the top-balance customer(s) (fails (b)). Checking each statement against this: Statement Verdict Why 1 — same row set as Query2 for some but not all databases True Literally, the two row sets coincide only for the trivial empty database (both then return zero rows) — for any non-empty database, Query2 always omits the top-balance customer's row that Query1 always includes, so the sets are never identical once there is at least one customer. What is genuinely database-dependent is how far apart the two get: when every balance is distinct, every row Query2 does return carries exactly the same value as Query1's for that customer — e.g. balances 100, 90, 80 give Query1 = (100,1),(90,2),(80,3) and Query2 = (90,2),(80,3), agreeing on both shared rows, missing only the top one. If a tie occurs elsewhere too — not at the very top — the two can also disagree in value on rows both return: with balances 100, 90, 90, Query1 gives (90,3),(90,3) for the tied pair (it counts each against the other) while Query2 gives (90,2),(90,2) for the same two customers (it only counts the single 100 above them), on top of both again omitting the untied top customer. So agreement between the queries genuinely varies with the database — never universal, and total only in the empty-database corner case — unlike statements 2 and 3, which are simply false regardless of the database. 2 — both are correct implementations False Query1 already fails the 100, 100, 90 example above. 3 — Query1 correct, Query2 is not False Query1 itself is not a correct implementation — same counter-example rules it out too. 4 — neither is a correct implementation True Query1 mis-values tied groups; Query2 always omits the top-balance customer(s). Every non-empty database triggers at least one of these two failures. 5 — relational ranking always beats ODBC-style scanning False The self-join here compares on the order of n2 candidate pairs, so it is not guaranteed to beat an ordered scan that assigns ranks in a single pass — the claim cannot be asserted as an absolute rule. Exactly statements 1 and 4 hold, so the pairing “1 and 4” is the correct choice.

Answer

A. 2 and 5

Answer

B. 1 and 3

Answer

C. 1 and 4

Answer

D. 3 and 5

Statement	Verdict	Why
1 — same row set as Query2 for some but not all databases	True	Literally, the two row sets coincide only for the trivial empty database (both then return zero rows) — for any non-empty database, Query2 always omits the top-balance customer's row that Query1 always includes, so the sets are never identical once there is at least one customer. What is genuinely database-dependent is how far apart the two get: when every balance is distinct, every row Query2 does return carries exactly the same value as Query1's for that customer — e.g. balances 100, 90, 80 give Query1 = (100,1),(90,2),(80,3) and Query2 = (90,2),(80,3), agreeing on both shared rows, missing only the top one. If a tie occurs elsewhere too — not at the very top — the two can also disagree in value on rows both return: with balances 100, 90, 90, Query1 gives (90,3),(90,3) for the tied pair (it counts each against the other) while Query2 gives (90,2),(90,2) for the same two customers (it only counts the single 100 above them), on top of both again omitting the untied top customer. So agreement between the queries genuinely varies with the database — never universal, and total only in the empty-database corner case — unlike statements 2 and 3, which are simply false regardless of the database.
2 — both are correct implementations	False	Query1 already fails the 100, 100, 90 example above.
3 — Query1 correct, Query2 is not	False	Query1 itself is not a correct implementation — same counter-example rules it out too.
4 — neither is a correct implementation	True	Query1 mis-values tied groups; Query2 always omits the top-balance customer(s). Every non-empty database triggers at least one of these two failures.
5 — relational ranking always beats ODBC-style scanning	False	The self-join here compares on the order of n2 candidate pairs, so it is not guaranteed to beat an ordered scan that assigns ranks in a single pass — the claim cannot be asserted as an absolute rule.

Consider the relation account (customer, balance) where customer is a primary…