GATE2006
 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.
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.
 Query1 will produce the same row set as Query2 for some but not all databases.
 Both Query1 and Query2 are correct implementation of the specification
 Query1 is a correct implementation of the specification but Query2 is not
 Neither Query1 nor Query2 is a correct implementation of the specification
 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
Ans: option (c)
Explanation:
Both queries will produce the same result set only when there are no duplicate balances in the table. So statement 1 is correct.
Query1 & Query2 is not the correct implementation 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.
GATE2011
 Database table by name Loan_Records is given below.
—————————————
Borrower Bank_Manager Loan_Amount
—————————————
Ramesh Sunderajan 10000.00
Suresh Ramgopal 5000.00
Mahesh Sunderajan 7000.00
—————————————
What is the output of the following SQL query?
SELECT Count(*)
FROM ( (SELECT Borrower, Bank_Manager
FROM Loan_Records) AS S
NATURAL JOIN (SELECT Bank_Manager,
Loan_Amount
FROM Loan_Records) AS T );
(a) 3 (b) 9 (c) 5 (d) 6
Ans: option (c)
Explanation:


Table: S Natural Join T  
Borrower  Bank_Manager  Loan_Amount 
Ramesh  Sunderajan  10000.00 
Ramesh  Sunderajan  7000.00 
Suresh  Ramgopal  5000.00 
Mahesh  Sunderajan  10000.00 
Mahesh  Sunderajan  7000.00 
GATE2011
 Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table.
Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?
SELECT Y FROM T WHERE X=7;
(a) 127 (b) 255 (c) 129 (d) 257
Ans: option (a)
Explanation:
First record is X=1 and Y=1.
As per the given condition, the next record will be:
X = MX + 1 = 1 + 1 = 2
Y = 2*MY +1 = 2 + 1 = 3
third record will be
X = MX + 1 = 2 + 1 = 3
Y = 2*MY +1 = 6 + 1 = 7
fourth record will be
X = MX + 1 = 3 + 1 = 4
Y = 2*MY +1 = 14 + 1 = 15
fifth record will be
X = MX + 1 = 4 + 1 = 5
Y = 2*MY +1 = 30 + 1 = 31
sixth record will be
X = MX + 1 = 5 + 1 = 6
Y = 2*MY +1 = 62 + 1 = 63
seventh record will be
X = MX + 1 = 4 + 1 = 3
Y = 2*MY +1 = 126 + 1 = 127
GATE2007
 Consider the table employee(empId, name, department, salary) and the two queries Q1, Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?
Q1 : Select e.empId
From employee e
Where not exists
(Select * From employee s where s.department = “5” and
s.salary >=e.salary)
Q2 : Select e.empId
From employee e
Where e.salary > Any
(Select distinct salary From employee s Where s.department = “5”)
(a) Q1 is the correct query
(b) Q2 is the correct query
(c) Both Q1 and Q2 produce the same answer.
(d) Neither Q1 nor Q2 is the correct query.
Ans: option (d)
Explanation:
Assume that we have the following records in the employee table:
———————————
empId name department salary
———————————
1 A 2 800
2 B 5 100
3 C 5 300
4 D 5 700
4 E 6 500
———————————
On executing Q1 we will get empId 1 (But note that the department of empId 1 is 2)
On executing Q2 we will get empid 1, 3, 4, 5
GATE2000
 In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the
following pairs is not equivalent?
(a) x = 5, not (not (x = 5)
(b) x = 5, x > 4 and x < 6, where x is an integer
(c) x < 5, not(x = 5)
(d) None of the above Ans: option (c)
GATE2000
 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
(a) r has no duplicates and s is nonempty
(b) r and s have no duplicates
(c) s has no duplicates and r is nonempty
(d) r and s have the same number of tuples
Ans: option (a)
Explanation:
Its given in the question that the resultset of the query should be same as that of the relational table “r”. Since it should be same as that of “r”, it should not have any duplicates and we know that the Cartesian product of two sets will be empty if any of the two sets is empty, hence s should be nonempty.