GATE-2006

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

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

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.

GATE-2011

8. 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:

GATE-2011

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

GATE-2007

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

GATE-2000

11. 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)

GATE-2000

12. 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 non-empty

(b) r and s have no duplicates

(c) s has no duplicates and r is non-empty

(d) r and s have the same number of tuples

Ans: option (a)

Explanation:

Its given in the question that the result-set 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 non-empty.