Question

Let Account be a relation as shown.

Table: Account

AccNo	Balance
A1	5000
A2	5000
A3	10000
A4	15000
A5	18000

Consider the given SQL query.

SELECT AccNo FROM Account AS A
WHERE (SELECT COUNT(*) FROM Account AS B
WHERE A.Balance < B.Balance) >= (SELECT COUNT(*)
FROM Account AS C WHERE A.Balance > C.Balance)

The number of rows returned by the SQL query is _______. (Answer in integer)

Hint:

For each account, compare how many accounts have a strictly higher balance against how many have a strictly lower balance; only rows at or below the median pass.

Answer 1

Correct Answer: 3

Step 1: Understand what the two subqueries compute for each row.
The query goes through every row A of Account, one at a time, and checks a condition built from two correlated subqueries.
The first subquery, (SELECT COUNT(*) FROM Account AS B WHERE A.Balance < B.Balance), counts how many accounts in the whole table have a balance strictly greater than A's balance. Call this "countGreater".
The second subquery, (SELECT COUNT(*) FROM Account AS C WHERE A.Balance > C.Balance), counts how many accounts have a balance strictly less than A's balance. Call this "countLess".
A row A is kept in the output only when countGreater >= countLess, meaning at least as many accounts beat A as A beats.

Step 2: List the balances and compute countGreater and countLess for each account.
The balances are A1=5000, A2=5000, A3=10000, A4=15000, A5=18000.
For A1 (5000): accounts with a strictly greater balance are A3, A4, A5, so countGreater = 3. Accounts with a strictly smaller balance: none, since the only other 5000 (A2) is equal, not smaller, so countLess = 0.
For A2 (5000): by the same reasoning, countGreater = 3 and countLess = 0.
For A3 (10000): accounts with a strictly greater balance are A4 and A5, so countGreater = 2. Accounts with a strictly smaller balance are A1 and A2, so countLess = 2.
For A4 (15000): only A5 (18000) is greater, so countGreater = 1. A1, A2, A3 are all smaller, so countLess = 3.
For A5 (18000): no account is greater, so countGreater = 0. A1, A2, A3, A4 are all smaller, so countLess = 4.

Step 3: Apply the condition countGreater >= countLess to each account.
A1: 3 >= 0, true. Kept.
A2: 3 >= 0, true. Kept.
A3: 2 >= 2, true. Kept.
A4: 1 >= 3, false. Dropped.
A5: 0 >= 4, false. Dropped.

Final Answer:
The query returns the AccNo of A1, A2 and A3, which is 3 rows in total. \[ \boxed{3} \]