Friday, January 18, 2013

GATE Question - DBMS - SQL

Previous GATE questions with solutions on DBMS (SQL) - CS/IT

GATE-2010
1. 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)

(a) 1, 0 (b) 1, 2 (c) 1, 3 (d) 1, 5

Ans: option (c)
Explanation:

The above query is an example of synchronized subquery or correlated subquery. A correlated sub-query is a sub-query that uses values from the outer query. The sub-query is evaluated once for each row processed by the outer query.

In the above query the outer query is

SELECT pid FROM Reservation WHERE class ‘AC’ AND EXISTS
And the subquery is,
SELECT * FROM Passenger WHERE age > 65 AND Passenger. pid = Reservation.pid
The correlated subquery is evaluated once for each row processed by the outer query. The outer query selects rows with pids: 0, 1, 5, 3, from Reservation table. Out of these, the subquery conditions are met only for 1 and 3.

GATE-2009

Common Data for Questions 2 and 3

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

Consider the following relational query on the above database:

SELECT S.sname
    FROM Suppliers S
        WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C
                            WHERE C.pid NOT IN (SELECT P.pid FROM Parts P                                                                       WHERE P.color<> 'blue'))

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
(a) Find the names of all suppliers who have supplied a non-blue part.

(b) Find the names of all suppliers who have not supplied a non-blue part.

(d) Find the names of all suppliers who have not supplied only blue parts.

Ans: option (a)

Explanation:
“SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’” gives pid of parts, which are not blue. Note: "<>" indicates "not equal to".
“SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’)” gives sid of all suppliers who have supplied blue parts.
The whole query finally retrieves the name (sname) of suppliers, who have supplied a non-blue part.

3. 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?
(a) The schema is in BCNF
(b) The schema is in 3NF but not in BCNF
(c) The schema is in 2NF but not in 3NF
(d) The schema is not in 2NF

Ans: option (a)

GATE-2004
4. The employee information in a company is stored in the relation
Employee (name, sex, salary, deptName)
Consider the following SQL query

   Select deptName 
         From Employee 
         Where sex = ‘M’ 
    Group by deptName 
    Having avg(salary) > 
         (select avg (salary) from Employee)

It returns the names of the department in which
(a) the average salary is more than the average salary in the company
(b) the average salary of male employees is more than the average salary of all male employees in the company
(c) the average salary of male employees is more than the average salary of
employees in the same department.
(d) the average salary of male employees is more than the average salary in the company

Ans: option (d)

GATE-2005
5. The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list?

  Select title
  From book as B
  Where (Select count(*)
     from book as T
     Where T.price > B.price) < 5

(a) Titles of the four most expensive books (b) Title of the fifth most inexpensive book
(c) Title of the fifth most expensive book
(d) Titles of the five most expensive books

Ans: option (d)
Explanation:
The outer query selects all titles from "book" table. For each title the inner query will be evaluated.

Table : T
Title	Price
ABC	65
DEF	75
GHI	85
JKL	95
MNO	105
PQR	115
STU	125

Table : B
Title	Price
ABC	65
DEF	75
GHI	85
JKL	95
MNO	105
PQR	115
STU	125

For every selected book, the subquery will return the count of books which are more expensive than the selected book. T.price > B.price is evaluated for every B.price.

For the 1st book (B.price = 65) the inner query gives count 6 (75,85,95,105,115,125).
For the 2nd book (B.price = 75) the inner query gives count 5 (85,95,105,115,125).
For the 3rd book (B.price = 85) the inner query gives count 4 (95,105,115,125).
For the 4th book (B.price = 95) the inner query gives count 3 (105,115,125).
For the 5th book (B.price = 105) the inner query gives count 2 (115,125).
For the 6th book (B.price = 115) the inner query gives count 1 (125).
For the 7th book (B.price = 125) the inner query gives count 0.

Hence the entire query will list out the title of a book when the count is less than 5. Hence as shown above from 3rd book onwards the titles will be listed. GHI, JKL, MNO, PQR, STU will be listed.

GATE - 2006

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

(a) All queries return identical row sets for any database

(b) Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.

(d) There exist databases for which Query4 will encounter an integrity violation at runtime.

Ans: option (b)

Explanation: Query1 and Query3 will produce duplicate rows if same student has applied for another course also.

Table : Enrolled
Student	Course
Minu	Mtech
Tinu	Bsc
Vinu	Btech
Vinu	Mtech

Table : Paid
Student	Amount
Minu	2000
Vinu	4000

The output of each query for the above tables are shown below.

Query1 & Query3:

			student
			Minu
			Vinu

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:

Table: S
Borrower	Bank_Manager
Ramesh	Sunderajan
Suresh	Ramgopal
Mahesh	Suderajan

Table: T
Bank_Manager	Loan_Amount
Sunderajan	10000.00
Ramgopal	5000.00
Suderajan	7000.00

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

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

(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

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

GATE – 2015

13. Consider the following relations:

Student
Roll_No	Student_Name
1	Raj
2	Rohit
3	Raj

Performance
Roll_No	Course	Marks
1	Math	80
1	English	70
2	Math	75
3	English	80
2	Physics	65
3	Math	80

Consider the following SQL query.

SELECT S. Student_Name, sum (P.Marks)
FROM Student S, Performance P
WHERE S. Roll_No =P.Roll_No
GROUP BY S.Student_Name

The number of rows that will be returned by the SQL query is _________.

Ans: 2

Explanation:

There are 2 student names. The query returns sum (P.Marks) for each student. Hence the output is :

Raj	310
Rohit	140

Thus 2 rows are returned.

GATE - 2015

14. Consider the following relation

Cinema (theater, address, capacity)

Which of the following options will be needed at the end of the SQL query

SELECT P1.address FROM Cinema P1

such that it always finds the addresses of theaters of theaters with maximum capacity?

(a) WHERE P1.capacity > = All (select P2. capacity from Cinema P2)

(b) WHERE P1.capacity > = Any (select P2. capacity from Cinema P2)

(d) WHERE P1.capacity >Any (select max (P2. capacity) from Cinema P2)

Ans: option (a)

Explanation:

When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with AND operators.

When the ANY condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with OR operators.

Reference: http://oracle-base.com/articles/misc/all-any-some-comparison-conditions-in-sql.php

Thus in the question above,

Option (b)returns address of all theatres except the one with minimum capacity

Option (c) returns 0 rows, since a theatre with a capacity greater than maximum capacity will not exist.

Option (d) also returns null for the same reason as above.

Option (a) is the right answer, query will return addresses of theaters with maximum capacity.

GATE - 2015

15. Select operation in SQL is equivalent to
(a) the selection operation in relational algebra
(b) the selection operation in relational algebra, except that select in SQL retains duplicates
(c) the projection operation in relational algebra
(d) the projection operation in relational algebra, except that select in SQL retains duplicates

Ans: option (d)
Explanation:
Select operation in SQL returns a set of records containing duplicates. To eliminate duplicates, DISTINCT keyword is specified.
Project operation in relational algebra,returns result set without repetition.
To know more on relational algebra, refer http://www.tutorialspoint.com/dbms/relational_algebra.htm

Questions 13, 14, & 15 contributed by Radhika V.M., M.Tech, College of Engineering Trivandrum