Question
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?
a.
2 and 5
b.
1 and 3
c.
1 and 4
d.
3 and 5
Posted under GATE cse question paper DBMS
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
View solution
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?
SELECT pid
FROM Reservation ,
WHERE class ‘AC’ AND
EXISTS (SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger. pid = Reservation.pid)
View solution
Q. Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional dependencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is
View solution
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.
CREATE TABLE S (
a INTEGER,
d INTEGER,
e INTEGER,
PRIMARY KEY (d),
FOREIGN KEY (a) references R)
Which one of the following statements is CORRECT?
View solution
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?
View solution
Q. Which option is true about the SQL query given below?
SELECT firstName, lastName
FROM Employee
WHERE lastName BETWEEN 'A%' AND 'D%';
View solution
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?
View solution
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 :
View solution
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?
View solution
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
View solution
Q. Which level of locking provides the highest degree of concurrency in a relational data base?
View solution
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?
View solution
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
View solution
Q. Given relations r(w, x) and s(y, z), the result of
SELECT DISTINCT w, x
FROM r, s
is guaranteed to be same as r, provided
View solution
Q. An index is clustered, if
View solution
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?
View solution
Q. Which of the following concurrency control protocols ensure both conflict serialzability and freedom from deadlock? I. 2-phase locking II. Time-stamp ordering
View solution
Q. From the following instance of a relation scheme R (A, B, C), we can conclude that :
A B C
1 1 1
1 1 0
2 3 2
2 3 2
View solution
Q. A clustering index is defined on the fields which are of type
View solution
Q. Given the basic ER and relational models, which of the following is INCORRECT?
View solution
Suggested Topics
Are you eager to expand your knowledge beyond DBMS? We've curated a selection of related categories that you might find intriguing.
Click on the categories below to discover a wealth of MCQs and enrich your understanding of Computer Science. Happy exploring!