Friday, January 18, 2013
GATE Question - DBMS - SQL
Labels:
GATE,
GATE questions on DBMS
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)
What pids are returned by the following SQL query for the above instance of the tables?
Ans: option (c)
Explanation:
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
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
GATE-2010
1. A relational schema for a train reservation database is given below.
Passenger (pid, pname, age)
Reservation (pid, class, tid)
Table: Passenger
|
Table : Reservation
|
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, 5Ans: 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:
(a) Find the names of all suppliers who have supplied a non-blue part.
“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
(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?
(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.
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
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.
|
|
||||||||||||||||||||||||||||||||||||
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
GATE-2006
Borrower Bank_Manager Loan_Amount
---------------------------------------
Ramesh Sunderajan 10000.00
Suresh Ramgopal 5000.00
Mahesh Sunderajan 7000.00
---------------------------------------
GATE-2011
(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
GATE-2007
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.
|
|
||||||||||||||||||||
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)
(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:
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
|
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;
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
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
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
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
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 thefollowing 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
GATE – 2015
13. Consider the following relations:
|
|
||||||||||||||||||||||||||||||||||
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
Subscribe to:
Post Comments (Atom)
i think the answer should me A in question 6
ReplyDeleteYa , you are correct.Answer is A
DeleteNo, the answer is B, as number of duplicates in each table can be different and selecting each table shows their respective number of rows with student name duplicates of the selected table.
Deleteeg:
if enrolled have student name "naveen" 2 times and paid has student name "naveen" 3 times,
Q1 and Q3 will display "naveen" 2 times and Q2 and Q4 will display "naveen" 3 times.
grt explanations
ReplyDeleteThanks a lot, keep posting. Really helpful
ReplyDelete