GATE CSE 2006 | Question: 67

Question

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?

• A. $2$ and $5$
• B. $1$ and $3$
• C. $1$ and $4$
• D. $3$ and $5$

Comments

https://www.geeksforgeeks.org/gate-gate-cs-2006-question-67/

---

geeksforgeeks has incorrect review about statement 1.When all tuples are distinct in Account relation, then

Query 1, gives correct rank, query 2 also gives correct ranks, but rank1 is not given.

---

Testing results here. (if it helps anyone)

https://docdro.id/emRYk8F

Answer 1

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

Comments

@Nilanjandaw The modified query is a correlated query where the inner query is dependent on outer query. 

Eg:

Customer	Balance
1	100
2	100
3	200
4	300

Now the outer query will select first tuple and send the value of Balance (i.e 100 in this case) to the inner query. A.balance will be replaced by 100. Now 100 < 200 and 300 when you compare it with the same table in inner query. So, count(*) will be 2, but it will incremented to 3 coz of +1. 

Then, the outer query will select 2nd tuple as the balance is again 100, count will be returned as 3.

Similarly, 3rd tuple will be selected and as 200<300, count(*)=1 which will be incremented to 2.

Similarly, 4th tuple will be selected and as it is the tuple with max balance, count(*)=0 which will be incremented to 1.

So, the final result:

Customer	Rank
1	3
2	3
3	2
4	1

---

Even though we have 1 + COUNT(B.customer), since there are no groups, the expression is never evaluated and no row appears in the result.

---

Hi @Gopal6854 , 
In the Query 2, the condition is not satisifed right. But, my question is. Like, yes there are no tuples satisfying the condition. But, We will group according to the A.customer not with B.customer right. So, if we group according to A.customer then we will groups with no values in the B.customer column? 

Correct me, if i'm wrong. 
So, the  1+count(B.customer) ==> count(B.customer) gets 0 and one will be added to that result. So, for every tuple the rank is assigned as 1.. 

This is my doubt. clarify this. Is the above thought right or not? 

 

Answer 2

Only statement 4 is correct

Page 1

Page 2

Comments

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.

---

I didn’t get why Query 1’s customer ‘a’ gets rank 2, in the second image.

Please help @adad20 @Sushil Shinde

---

@ankit3009
According to query 1 first cross product of both the relation will be done and that tuple will be selected in which A.balance <= B.balance. Since balance of ‘a’ in relation 1 is 1000 and after cross product of both the relations #tuples with A.balance <= b.balance will be 2, so ‘a’ get rank 2 not 1.

Answer 3

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.

Comments

nice explanation. thanks

Answer 4

(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)

Comments

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

---

if query 2 is not displaying rank 1  then how  1 is correct?

how same rowset?

---

@Pranavpurkar they are saying for some data bases it can give same rowset when they are saying that it must print 1 as rank. Rank can be anything bt row set can be same.

Answer 5

Let us assume table has two customers with highest balance. According to given specification, the both should get rank 1.

Q1 results assigning both rank 2, and none get rank 1

Q2 results assign no rank(NULL) for both guys

Query 1 & 2 gives the same output for all not all data based its true because the balances may be distinct variables. Statement 1 is true.

Statement 5 is false as a single scan should be faster than a join query.

Thus we end up with option C

Comments

Can you explain option 5 more? What is "ODBC"?

Answer 6

In statement  5 Relational query is used for performance Analysis.... based on balance assigns Rank and for scanning also takes same time so 5th statement also false 

 

hence option C is true 

Comments

Can u give us real sql command for it .......