4.2k views

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.

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?

1. 2 and 5
2. 1 and 3
3. 1 and 4
4. 3 and 5
+1

Can some one give a dummy data result when there are duplicates in the table.I am trying to verify my analysis as the rank will also vary in both the tables when there will be duplicate balance & in query Q2 there will not be any 1st rank.

+4

Consider:

 customer balance A 100 B 200 C 200

Executing Query 1, you can see that A would appear 3 times and B and C would appear 2 times after the join. Hence the final count would not contain rank 1.

Executing Query 2, A would appear twice and the count would be 3. B and C wouldn't appear in the list and hence won't be grouped. Both of these queries would return wrong answers.

Similarly, if there where only the first two rows present in the table, on executing Query 2 we would get rank 2 for A and no rank 1 in the output.

They would produce the same results only when empty relations can be considered.

Both Query1 and Query2 are not correct implementations because: Assume that we have a table with n customers having same balance. In that case Query1 will give rank n to each customer. But according to the question the rank assigned should be 1. And Query2 will return an empty result set (as it will never return rank 1). So statement 4 is correct. For same reason Query 1 is wrong though it is true if we assume 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, 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

selected by
+1
why query 2 will never return rank 1 ?? plz explain..
+2
how did you deduce that the two queries will only give same result if the tuples are distinct ? I am not able to come to that conclusion :/
+1
@ Arjun Suresh : How can you say Query 2 will give same result when no duplicates balances will be there. The equality sign will be redundant in Query 1 due to no duplicate balances but what about 1+ count(B.customer) in Query 2 which will give different result of rows.
+8

Query 2 will result in empty result set in the condition mentioned.

You can get this by following SQL execution order.

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT

Now assume the condition mentioned, we have a table with n customers having same balance and Query 2 is having  WHERE condition as  "A.balance < B.balance"  to select each row. WHERE condition will be failed for each row since there is no customer in B having greater balance than customers in A. Thus no row will be selected after executing the WHERE condition. Since there is no row  selected then nothing will be grouped by GROUP BY.

Hence in the assumed case, Query 2 will not display and rank 1.Moreover it will not even display any row.

+2
As mentioned Query 2 will never give rank 1. Hence in any case Query 1 and Query 2 will never give same result. even for unique balances
0

If query 2 does not display rank 1 then how can we say 1 ie.' Query1 will produce the same row set as Query2 for some but not all databases. ' is  true??bcz for in result set there always will  be a entry of rank1..

0
How two queries will produce same result set if there are no duplicates.

I mean if there are no duplicates then query one will give a rank 1 to customer with highest balance but query 2 will not give any rank to that customer so arent they giving different result even there are no duplicates?
+1
Exaclty thats what i am thinking. These qieries will never give same result for any database state. So this is question has no correct option right??
+1
yes I think only stmt 4 is correct.
+1
Yes option (4) is only correct, because in any case  query 2 won't display rank 1
+1
Yes option (4) is only correct, because in any case  query 2 won't display rank 1.
Because join condition will never be evaluated to true for highest balance
+6
yes, 1 is not correct. But given this question and options C can be chosen as we can assume an empty relation though that is technically not correct.
+1

i think count returns 0 when applied on an empty set while all other aggregations return null.

http://firebirdsql.org/manual/nullguide-aggrfunc.html

so

2nd query should return 1st rank and all 4 options are wrong

–1

Ashis, when group by is there, count returns empty and not 0!!
Check this :

http://sqlfiddle.com/#!9/ffde34/3

–4

if i expressed my opinion on this very "public" website without somebody pointing finger at me. and btw FYI different versions of SQL have different implementations for this

0
okkk got it query2 will never display rank1 for the highest balance here therefore even if the balance are unique result can not be same

Only statement 4 is correct

Page 1

Page 2

0
option 1 is also correct becoz  if bal are distinct both will output same row set or result but if  duplicates are allowed then they will output diff result and 4 th option is also correct here
0
good job pc :)
0
but count(b.customer)  is here . So for query 1 the rank of 'a' should be n(the no. of tuples ).

isn't it??correct me if i m wrong.
(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
+1
How query 2 will not produce the correct result?

I am getting the desired result from query 2.
+2
ok got it, 2nd query will not display 1st rank.
0
Can you please explain, how it will not display 1st rank?
+2

@Saurav Sharma:

Query 2 will result in empty result set in the condition mentioned below.

You can get this by following SQL execution order.

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT

Now assume the condition mentioned, we have a table with n customers having same balance and Query 2 is having  WHERE condition as  "A.balance < B.balance"  to select each row. WHERE condition will be failed for each row since there is no customer in B having greater balance than customers in A. Thus no row will be selected after executing the WHERE condition. Since there is no row  selected then nothing will be grouped by GROUP BY.

Hence in the assumed case, Query 2 will not display and rank 1.Moreover it will not even display any row.