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.
(c) Find the names of all suppliers who have supplied only blue parts.
(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.
(c) There exist databases for which Query3 returns strictly fewer rows than Query2.
(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



Vinu
Query2 & Query4:
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
(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.

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)
(c) WHERE P1.capacity > All (select max (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.
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


2 comments:

  1. i think the answer should me A in question 6

    ReplyDelete