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
select A.customer, count(B.customer)
from account A, account B
where A.balance <=B.balance
group by A.customer

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?


2 and 5


1 and 3


1 and 4


3 and 5

Answer: (c).1 and 4

Engage with the Community - Add Your Comment

Confused About the Answer? Ask for Details Here.

Know the Explanation? Add it Here.

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

Similar Questions

Discover Related MCQs

Q. Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O1,...,Ok}. This is done in the following manner: Step 1. T acquires exclusive locks to O1, . . . , Ok in increasing order of their addresses. Step 2. The required operations are performed. Step 3. All locks are released. This protocol will

Q. A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, tid)

Table: Passenger
pid pname age
0 Sachin 65
1 Rahul 66
2 Sourav 67
3 Anil 69

Table : Reservation
pid class tid
0 AC 8200
1 AC 8201
2 SC 8201
5 AC 8203
1 SC 8204
3 AC 8202
What pids are returned by the following SQL query for the above instance of the tables?
FROM Reservation ,
WHERE class ‘AC’ AND
FROM Passenger
WHERE age > 65 AND
Passenger. pid =

Q. Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is

Q. Given the following statements:

S1: A foreign key declaration can always
be replaced by an equivalent check
assertion in SQL.
S2: Given the table R(a,b,c) where a and
b together form the primary key, the
following is a valid table definition.
FOREIGN KEY (a) references R)
Which one of the following statements is CORRECT?

Q. What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial participation constraints of both entities?

Q. Which option is true about the SQL query given below?

SELECT firstName, lastName
FROM Employee
WHERE lastName BETWEEN 'A%' AND 'D%';

Q. Consider the relation "enrolled(student, course)" in which (student, course) is the primary key, and the relation "paid(student, amount)" where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries:

Query1: select student from enrolled where
student in (select student from paid)
Query2: select student from paid where
student in (select student from enrolled)
Query3: select E.student from enrolled E, paid P
where E.student = P.student
Query4: select student from paid where exists
(select * from enrolled where enrolled.student
= paid.student)

Which one of the following statements is correct?

Q. Consider the relations r1(P, Q, R) and r2(R, S, T) with primary keys P and R respectively. The relation r1 contains 2000 tuples and r2 contains 2500 tuples. The maximum size of the join r1⋈ r2 is :

Q. Suppose three are 3 transactions T22, T23, T24 with timestamps 10, 20, 30. Now T23 occupies a data item that is required by T22 and T24. Among the four possibilities what will be true in wait–die schema?

Q. Consider the following transaction involving two bank accounts x and y.

read(x); x := x – 50; write(x); read(y); y := y + 50; write(y)

The constraint that the sum of the accounts x and y should remain constant is that of

Q. Which level of locking provides the highest degree of concurrency in a relational data base?

Q. Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
(sid,pid are primary keys)
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?

Q. Given the following input (4322, 1334, 1471, 9679, 1989, 6171, 6173, 4199) and the hash function x mod 10, which of the following statements are true?

1. 9679, 1989, 4199 hash to the same value
2. 1471, 6171 hash to the same value
3. All elements hash to the same value
4. Each element hashes to a different value

Q. Given relations r(w, x) and s(y, z), the result of

FROM r, s

is guaranteed to be same as r, provided

Q. An index is clustered, if

Q. Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.

create table geq
ib integer not null
ub integer not null
primary key 1b
foreign key (ub) references geq on delete cascade

Which of the following is possible if a tuple (x,y) is deleted?

Q. Which of the following concurrency control protocols ensure both conflict serialzability and freedom from deadlock? I. 2-phase locking II. Time-stamp ordering

Q. From the following instance of a relation scheme R (A, B, C), we can conclude that :
1 1 1
1 1 0
2 3 2
2 3 2

Q. A clustering index is defined on the fields which are of type

Q. Given the basic ER and relational models, which of the following is INCORRECT?