Skip to main content

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.

Comments

Popular posts from this blog

ORACLE 9i practice solutions

Created by BCL easyConverter SDK 3 (HTML Version)

C Questions

C Questions
C Questions

Note : All the programs are tested under Turbo C/C++ compilers.
It is assumed that,
Programs run under DOS environment, The underlying machine is an x86 system, Program is compiled using Turbo C/C++ compiler.
The program output may depend on the information based on this assumptions (for example sizeof(int) == 2 may be assumed).
Predict the output or error(s) for the following:

void main()
{
int const * p=5; printf("%d",++(*p));
}
Answer:
Compiler error: Cannot modify a constant value.
Explanation:
p is a pointer to a "constant integer". But we tried to change the value of the "constant integer".
main()
{
char s[ ]="man"; int i;
for(i=0;s[ i ];i++)
printf("\n%c%c%c%c",s[ i ],*(s+i),*(i+s),i[s]);
}
Answer: mmmm
aaaa nnnn
Explanation

Zoho Interview | Set 1 (Advanced Programming Round)

Third Round: (Advanced Programming Round) Here they asked us to create a “Railway reservation system” and gave us 4 modules. The modules were:
    1. Booking
    2. Availability checking
    3. Cancellation
    4. Prepare chart
We were asked to create the modules for representing each data first and to continue with the implementation phase.

 My Solution :

#include<stdio.h>#include<conio.h>#include<stdlib.h>#include<string.h>#include<iostream.h>#include<time.h>#include<iomanip.h>#include<fstream.h>char f[10]="f";char s[10]="s";int addr,ad,flag,f1,d,m,i,amt;float tamt; class login {public:char id[100];char pass[100];char*password;void getid(){ cout<<"Enter your id:";gets(id); password=getpass("Enter the password:");strcpy(pass,password);}void displayid(){ cout<<"Id:";puts(id); cout<<"Password:";puts(pass);}}; class detail {public:in…