Question 1
Which of the following statements are TRUE about an SQL query?
P : An SQL query can contain a HAVING clause even 
    if it does not have a GROUP BY clause
Q : An SQL query can contain a HAVING clause only
    if it has a GROUP BY clause
R : All attributes used in the GROUP BY clause must
    appear in the SELECT clause
S : Not all attributes used in the GROUP BY clause
    need to appear in the SELECT clause 
A
P and R
B
P and S
C
Q and R
D
Q and S
GATE CS 2012    SQL    
Discuss it


Question 1 Explanation: 
According to standard SQL answer should be C. Refer If we talk about different SQL implementations like MySQL, then option B is also right. But in question they seem to be talking about standard SQL not about implementation. For example below is a P is correct in most of the implementations. HAVING clause can also be used with aggregate function. If we use a HAVING clause without a GROUP BY clause, the HAVING condition applies to all rows that satisfy the search condition. In other words, all rows that satisfy the search condition make up a single group. See this for more details. S is correct . To verify S, try following queries in SQL.
CREATE TABLE temp 
  ( 
     id   INT, 
     name VARCHAR(100) 
  ); 

INSERT INTO temp VALUES (1, "abc"); 
INSERT INTO temp VALUES (2, "abc"); 
INSERT INTO temp VALUES (3, "bcd"); 
INSERT INTO temp VALUES (4, "cde"); 

SELECT Count(*) 
FROM   temp 
GROUP  BY name; 
Output:
count(*)
--------
2
1
1
Question 2
Table A
Id   Name    Age
----------------
12   Arun    60
15   Shreya  24
99   Rohit   11


Table B
Id   Name   Age
----------------
15   Shreya  24
25   Hari    40
98   Rohit   20
99   Rohit   11


Table C
Id   Phone  Area
-----------------
10   2200   02  
99   2100   01
Consider the above tables A, B and C. How many tuples does the result of the following SQL query contains?
SELECT A.id 
FROM   A 
WHERE  A.age > ALL (SELECT B.age 
                    FROM   B 
                    WHERE  B. name = "arun") 
A
4
B
3
C
0
D
1
GATE CS 2012    SQL    
Discuss it


Question 2 Explanation: 
The meaning of “ALL” is the A.Age should be greater than all the values returned by the subquery. There is no entry with name “arun” in table B. So the subquery will return NULL. If a subquery returns NULL, then the condition becomes true for all rows of A (See this for details). So all rows of table A are selected. Source: http://www.geeksforgeeks.org/database-management-system-set-3/
Question 3
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
GATE CS 2011    SQL    
Discuss it


Question 4
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
GATE CS 2011    SQL    
Discuss it


Question 4 Explanation: 
Question 5
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?
SLECT 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
GATE CS 2010    SQL    
Discuss it


Question 5 Explanation: 
When a subquery uses values from outer query, the subquery is called correlated subquery. The correlated subquery is evaluated once for each row processed by the outer query. The outer query selects 4 entries (with pids as 0, 1, 5, 3) from Reservation table. Out of these selected entries, the subquery returns Non-Null values only for 1 and 3.
Question 6
Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database: gateqa
IV) SELECT R.a, R.b
       FROM R,S
            WHERE R.c=S.c
Which of the above queries are equivalent?
A
I and II
B
I and III
C
II and IV
D
III and IV
GATE-CS-2009    SQL    
Discuss it


Question 6 Explanation: 
I and II describe the division operator in Relational Algebra and Tuple Relational Calculus respectively. See Page 3 of this and slide numbers 9,10 of this for more details.
Question 7
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.
GATE-CS-2009    SQL    
Discuss it


Question 7 Explanation: 

The subquery “SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’” gives pids of parts which are not blue. The bigger subquery “SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’)” gives sids of all those suppliers who have supplied blue parts. The complete query gives the names of all suppliers who have supplied a non-blue part

Question 8
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
SQL    GATE-CS-2007    
Discuss it


Question 8 Explanation: 
Let the employee(empId, name, department, salary) have the following instance. empId name department salary ----------------------------------
e1 ------- A-------- 1---------10000
e2 -------B ------- 5 ---------5000
e3 -------C ------- 5----------7000
e4 -------D ------- 2----------2000
e5 -------E ------- 3----------6000
Now the actual result should contain empId : e1 , e3 and e5 ( because they have salary greater than anyone employee in the department '5') -------------------------------------------------------- Now Q1 : Note : EXISTS(empty set) gives FALSE, and NOT EXISTS(empty set) gives TRUE.
Select e.empId
From employee e
Where not exists
(Select * From employee s where s.department = “5” and
s.salary >=e.salary)
Q1 will result only empId e1. --------------------------------------------------------- whereas Q2 :
Select e.empId
From employee e
Where e.salary > Any
(Select distinct salary From employee s Where s.department = “5”)
Q2 will result empId e1, e3 and e5. -------------------------------------------------------- Hence Q2 is the correct query.
Question 9
Given the following statements:
    S1: A foreign key declaration can always 
        be replaced by an equivalent check
        assertion in SQL.
    S2: Given the table R(a,b,c) where a and
        b together form the primary key, the 
        following is a valid table definition.
        CREATE TABLE S (
            a INTEGER,
            d INTEGER,
            e INTEGER,
            PRIMARY KEY (d),
            FOREIGN KEY (a) references R) 
Which one of the following statements is CORRECT?
A
S1 is TRUE and S2 is FALSE.
B
Both S1 and S2 are TRUE.
C
S1 is FALSE and S2 is TRUE.
D
Both S1 and S2 are FALSE.
SQL    GATE-CS-2014-(Set-1)    
Discuss it


Question 9 Explanation: 
    S1: A foreign key declaration can always
        be replaced by an equivalent check
        assertion in SQL. 
False: Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.
    S2: Given the table R(a,b,c) where a and
        b together form the primary key, the
        following is a valid table definition.
        CREATE TABLE S (
            a INTEGER,
            d INTEGER,
            e INTEGER,
            PRIMARY KEY (d),
            FOREIGN KEY (a) references R) 
False: Foreign key in one table should uniquely identifies a row of other table. In above table definition, table S has a foreign key that refers to field 'a' of R. The field 'a' in table S doesn't uniquely identify a row in table R.
Question 10
Given the following schema:
     employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
     departments(dept-id, dept-name, manager-id, location-id) 
You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:
SQL> SELECT last-name, hire-date
     FROM employees
     WHERE (dept-id, hire-date) IN
     (SELECT dept-id, MAX(hire-date)
     FROM employees JOIN departments USING(dept-id)
     WHERE location-id = 1700
     GROUP BY dept-id); 
What is the outcome?
A
It executes but does not give the correct result.
B
It executes and gives the correct result.
C
It generates an error because of pairwise comparison.
D
It generates an error because the GROUP BY clause cannot be used with table joins in a subquery
SQL    GATE-CS-2014-(Set-1)    
Discuss it


Question 10 Explanation: 
The given query uses below inner query.
SELECT dept-id, MAX(hire-date)
     FROM employees JOIN departments USING(dept-id)
     WHERE location-id = 1700
     GROUP BY dept-id
The inner query produces last max hire-date in every department located at location id 1700. The outer query simply picks all pairs of inner query. Therefore, the query produces correct result.
SELECT last-name, hire-date
     FROM employees
     WHERE (dept-id, hire-date) IN
     (Inner-Query); 
Question 11
SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:
    select * from R where a in (select S.a from S) 
A
select R.* from R, S where R.a=S.a (D)
B
select distinct R.* from R,S where R.a=S.a
C
select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
D
select R.* from R,S where R.a=S.a and is unique R
SQL    GATE-CS-2014-(Set-2)    
Discuss it


Question 11 Explanation: 
The solution of this question lies in the data set(tuples) of Relations R and S we define. If we miss some case then we may get wrong answer. Let's say, Relation R(BCA) with attributes B, C and A contains the following tuples.
B C A
---------
7 2 1
7 2 1
8 9 5
8 9 5
And Relation S(AMN) with attributes A, M, and N contains the following tuples.
A M N
---------
1 6 7
2 8 4
5 9 6
5 5 3
----------------------------------------------------------------------------------------------------------- Now ,the original Query will give result as: "select * from R where a in (select S.a from S) " - The query asks to display every tuple of Relation R where R.a is present in the complete set S.a.
B C A
---------
7 2 1
7 2 1
8 9 5
8 9 5
----------------------------------------------------------------------------------------------------------- Option A query will result in : "select R.* from R, S where R.a=S.a"
B C A
---------
7 2 1
7 2 1
8 9 5
8 9 5
8 9 5
8 9 5
----------------------------------------------------------------------------------------------------------- Option B query will result in : " select distinct R.* from R,S where R.a=S.a"
B C A
---------
7 2 1
8 9 5
----------------------------------------------------------------------------------------------------------- Option C query will result in : "select R.* from R,(select distinct a from S) as S1 where R.a=S1.a" B C A --------- 7 2 1 7 2 1 8 9 5 8 9 5 ----------------------------------------------------------------------------------------------------------- Option D query will result in : NULL set "select R.* from R,S where R.a=S.a and is unique R" ---------------------------------------------------------------------------------------------------------- Hence option C query matches the original result set. Note : As mentioned earlier, we should take those data sets which can show us the difference in different queries. Suppose in R if you don't put identical tuples then you will get wrong answers. (Try this yourself, this is left as an exercise for you ).
Question 12
Consider the following relational schema:
  employee(empId, empName, empDept)
  customer(custId, custName, salesRepId, rating) 
salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?
SELECT empName
       FROM employee E
       WHERE NOT EXISTS (SELECT custId
       FROM customer C
       WHERE C.salesRepId = E.empId
       AND C.rating <> ’GOOD’); 
A
Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
B
Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
C
Names of all the employees with none of their customers having a ‘GOOD’ rating.
D
Names of all the employees with all their customers having a ‘GOOD’ rating.
SQL    GATE-CS-2014-(Set-3)    
Discuss it


Question 12 Explanation: 
If any employee has received rating other than 'good' from some customer,
then there will be some rows returned by the inner query.

And not exists will return false so that employee won't be printed 
only those employees which have got rating good from all their 
customers will be printed.
Question 13
The statement that is executed automatically by the system as a side effect of the modification of the database is
A
backup
B
assertion
C
recovery
D
trigger
SQL    
Discuss it


Question 14
Which of the following command is used to delete a table in SQL?
A
delete
B
truncate
C
remove
D
drop
SQL    
Discuss it


Question 14 Explanation: 
drop is used to delete a table completely
Question 15
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
SQL    GATE-CS-2006    
Discuss it


Question 15 Explanation: 
Query 1 and Query 2 will give the same result if all the customers have distinct balance. So, for some databases, the result of query 1 and query 2 will be same.
  Now, let us consider a fact that all the entries in the database have the same value for balance. Ideally, all the customers should have rank 1, but both the queries will give all the customers a rank equal to the number of customers in the database. So, both the queries do not give us the required output.
  Thus, C is the correct choice.
  Please comment below if you find anything wrong in the above post.
Question 16
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.
SQL    GATE-CS-2006    
Discuss it


Question 16 Explanation: 
Question 17
The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.
A   C
-----
2   4
3   4
4   3
5   2
7   2
9   5
6   4 
The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:
A
(3,4) and (6,4)
B
(5,2) and (7,2)
C
(5,2), (7,2) and (9,5)
D
(3,4), (4,3) and (6,4)
SQL    GATE-CS-2005    
Discuss it


Question 17 Explanation: 
Question 18
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 bookTitles of the five most expensive books
D
Titles of the five most expensive books
SQL    GATE-CS-2005    
Discuss it


Question 18 Explanation: 
Question 19
Consider the following relation schema pertaining to a students database:
Student (rollno, name, address)
Enroll (rollno, courseno, coursename)
where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?
A
8, 0
B
120, 8
C
960, 8
D
960, 120
SQL    GATE-CS-2004    
Discuss it


Question 19 Explanation: 
The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names. What is the maximum possible number of tuples? The result of natural join becomes equal to the Cartesian product when there are no common attributes. The given tables have a common attribute, so the result of natural join cannot have more than the number of tuples in larger table.
        Student
-------------------------------------
Rollno          name         Address
-------------------------------------
1                a             abc
2                b             bcd
3                c             cde



                   Enroll
----------------------------------------------
Rollno           Courseno          Coursename
----------------------------------------------
1                 12                 pqr
1                 14                 qrs
1                 15                 rst 
2                 13                 uvw 
2                 14                 qrs 

Result of natural join
----------------------------------------------
Rollno  Name   Address   Courseno  Coursename
----------------------------------------------
1        a      abc         12      pqr
1        a      abc         14      qrs
1        a      abc         15      rst 
2        b      bcd         13      uvw
2        b      bcd         14      qrs 
What is the maximum possible number of tuples? It might be possible that there is no rollnumber common. In that case, the number of tupples would be 0.
Question 20
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
SQL    GATE-CS-2004    
Discuss it


Question 20 Explanation: 
In this SQL query, we have
select deptName ---------------  Select the department name
from Employee  ----------------  From the database of employees
where sex = 'M' ---------------  Where sex is male (M)
group by deptName -------------  Group by the name of the department
having avg (salary) >   
(select avg (salary) from Employee)  -----  Having the average salary 
                                            greater than the average salary 
                                            of all employees in the organization.
So, this query would return the name of all departments in which the average salary of male employees is greater than the average salary of all employees in the company. Hence, D is the correct choice.   Please comment below if you find anything wrong in the above post.
Question 21
Consider the following SQL query
select distinct al, a2,........., an
from r1, r2,........, rm
where P 
For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions ? GATECS2004!30
A
A
B
B
C
C
D
D
SQL    GATE-CS-2003    
Discuss it


Question 21 Explanation: 

Cross product (x) combines the tuples of one relation with all the tuples of the other relation. Thus, tuples of relation r1, r2 …. rn are combined.
Select operator select is used to select resultant tuples.
Projection operator pi is used to select a subset of attributes from the resultant tuples by specifying the names of the attributes. So attributes a1, a2, an are projected from the resultant tuples.
 
Thus, option (A) is correct.
 
Please comment below if you find anything wrong in the above post.
Question 22
Consider the set of relations shown below and the SQL query that follows.
Students: (Roll_number, Name, Date_of_birth)
  Courses: (Course number, Course_name, Instructor)
  Grades: (Roll_number, Course_number, Grade)
 select distinct Name
         from Students, Courses, Grades
         where Students. Roll_number = Grades.Roll_number
              and Courses.Instructor = Korth
              and Courses.Course_number = Grades.Course_number
              and Grades.grade = A
Which of the following sets is computed by the above query?
A
Names of students who have got an A grade in all courses taught by Korth
B
Names of students who have got an A grade in all courses
C
Names of students who have got an A grade in at least one of the courses taught by Korth
D
None of the above
SQL    GATE-CS-2003    
Discuss it


Question 22 Explanation: 
The query gives the name of all the students who have scored "A" grade in any of the courses that are taught by Korth. So, C is the correct choice.
 
Please comment below if you find anything wrong in the above post.
Question 23
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
SQL    GATE-CS-2000    
Discuss it


Question 23 Explanation: 
Question 24
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 AND not(not(x = 5))
B
x = 5 AND x> 4 and x < 6, where x is an integer
C
x < 5 AND not (x = 5)
D
None of the above
SQL    GATE-CS-2000    
Discuss it


Question 24 Explanation: 
For all values smaller than 5, x < 5 will always be true but x = 5 will be false.
Question 25
Consider the following three table to store student enrollements in different courses.

Student(EnrollNo, Name)
Course(CourseID, Name)
EnrollMents(EnrollNo, CourseID) 
What does the following query do?
SELECT S.Name
FROM Student S, Course C, Enrollments E
WHERE S.EnrollNo = E.EnrollNo AND 
      C.Name = "DBMS" AND
      E.CourseID = C.CourseID AND
      S.EnrollNo IN 
        (SELECT S2.EnrollNo
         FROM Student S2, Course C2, Enrollments E2
         WHERE S2.EnrollNo = E2.EnrollNo AND
               E2.CourseID = C2.CourseID
               C2.Name = "OS")
A
Name of all students who are either enrolled in "DBMS" or "OS" courses
B
Name of all students who are enrolled in "DBMS" and "OS"
C
Name of all students who are either enrolled in "DBMS" or "OS" or both.
D
Non of the above
SQL    GATE-CS-2015 (Mock Test)    
Discuss it


Question 25 Explanation: 
  Background Reading: The above query is an example of nested query i.e. query within a query. Firstly the inner query is solved and then the outer one depending on the result of the inner query.
  • WHERE IN returns values that matches values in a list or subquery.
  • WHERE IN is a shorthand for multiple OR conditions.
Here, firstly the inner query is solved. It returns all the Enrollment 
Numbers (SELECT S2.EnrollNo) of students where the students’ enrollment 
number matches with the enrollment number of the courses 
(WHERE S2.EnrollNo = E2.EnrollNo) which have the course IDs whose Course 
Name is “OS” (E2.CourseID = C2.CourseID and C2.Name = “OS”).
Hence all the enrollment IDs are filtered out for the students who are enrolled for the “OS” course.
The outer query works similarly and filters out all the all tuples where 
the Students Enrollment Number matches with the Enrollment Number where the
course ID’s are for the course names “DBMS” 
(S.EnrollNo = E.EnrollNo AND C.Name =”DBMS” AND E.CourseID = C.CourseId) and 
additionally matches with the ones that are returned by the inner query i.e. 
Enrollment Number of students who are enrolled for the course “OS”.
Hence the above queries returns names of all students (SELECT S.Name) who have enrolled for both courses “DBMS” and “OS”. Hence option (B). This explanation has been contributed by Yashika Arora.
Question 26
Consider the following Employee table
ID   salary   DeptName
1    10000      EC
2    40000      EC
3    30000      CS
4    40000      ME
5    50000      ME
6    60000      ME 
7    70000      CS 
How many rows are there in the result of following query?
SELECT E.ID
FROM  Employee E
WHERE  EXISTS  (SELECT E2.salary
               FROM Employee E2
               WHERE E2.DeptName = 'CS'
               AND   E.salary > E2.salary)
A
0
B
4
C
5
D
6
SQL    GATE-CS-2015 (Mock Test)    
Discuss it


Question 26 Explanation: 
Background:
  1. WHERE EXISTS tests for the existence of any records in a subquery.
  2. EXISTS returns true if the subquery returns one or more records.
  3. EXISTS is commonly used with correlated subqueries.
Here in the above question, there is a correlated subquery because the subquery references the enclosing query (relation Employee renamed as E) The subquery (SELECT E2.salary FROM Employee E2 WHERE E2.DeptName = 'CS') Filters out E2 relation as (all tuples where DeptName is CS and the respective salaries) Now the correlated query works as follows: SELECT E.ID FROM Employee E WHERE EXISTS (SELECT E2.salary FROM Employee E2 WHERE E2.DeptName = 'CS' AND E.salary > E2.salary) It takes one tuple from the Employee Relation and displays its ID if the WHERE EXISTS returns true i.e. the subquery returns one or more records. This happens in the case when the tuple from the Employee Relation E has the value of the salary attribute greater than any one of the values of the salary attribute filtered out above. So tuples filtered out would be all the tuples that have their salary attribute value greater than the salary values of at least one from the E2 relation (3000 and 7000). ID salary DeptName 2 40000 EC 4 40000 ME 5 50000 ME 6 60000 ME 7 70000 CS Finally it displays their ID’s and the output would be: 2 4 5 6 7 Hence option (C) 5 rows.   This solution is contributed by Yashika Arora.
Question 27
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
SQL    GATE-CS-2015 (Set 1)    
Discuss it


Question 27 Explanation: 
Select operation is equivalent to the projection operation in relational algebra, except that select in SQL retains duplicates and on the contrary projection removes the duplicates.
Question 28
Consider the following relations: Q45
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 _________
A
0
B
1
C
2
D
3
SQL    GATE-CS-2015 (Set 1)    
Discuss it


Question 28 Explanation: 
Below is result of given query. Note that there are only two student names and query prints sum(P.Marks) for every student.
  Student_Name     Marks
      Raj           310
     Rohit          140 
Question 29
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 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)
SQL    GATE-CS-2015 (Set 3)    
Discuss it


Question 29 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, as shown below. Source: http://oracle-base.com/articles/misc/all-any-some-comparison-conditions-in-sql.php
Question 30
A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema:
salesinfo = (salespersonid, totalsales, commission)
In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula:
If commission < = 50000, enhance it by 2% If 50000 < commission < = 100000, enhance it by 4% If commission > 100000, enhance it by 6%
The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows:
 T1
Update salesinfo
Set commission = commission * 1.02
Where commission < = 50000;
 
 T2
Update salesinfo
Set commission = commission * 1.04
Where commission > 50000 and commission is < = 100000;
 
 T3
Update salesinfo
Set commission = commission * 1.06
Where commission > 100000;
 
Which of the following options of running these transactions will update the commission of all salespersons correctly
A
Execute T1 followed by T2 followed by T3
B
Execute T2, followed by T3; T1 running concurrently throughout
C
Execute T3 followed by T2; T1 running concurrently throughout
D
Execute T3 followed by T2 followed by T1
SQL    Gate IT 2005    
Discuss it


Question 30 Explanation: 
T3 followed by T2 followed by T1  If this sequence is not followed then it may happen that Officer of one slab get benefited twice. Say an officer is having commission as 99,999,He must be get updated commision according to T2.But then 99,999*1.04=1,03998 and he again becomes eligible for next slab of commision. T3 followed by T2 followed by T1 will be check all border cases like above.So Answer is D
Question 31
A table 'student' with schema (roll, name, hostel, marks), and another table 'hobby' with schema (roll, hobbyname) contains records as shown below:
Table: Student
Roll Name Hostel Marks
1798 Manoj Rathod 7 95
2154 Soumic Banerjee 5 68
2369 Gumma Reddy 7 86
2581 Pradeep Pendse 6 92
2643 Suhas Kulkarni 5 78
2711 Nitin Kadam 8 72
2872 Kiran Vora 5 92
2926 Manoj Kunkalikar 5 94
2959 Hemant Karkhanis 7 88
3125 Rajesh Doshi 5 82
 
Table: hobby
Roll Hobbyname
1798 chess
1798 music
2154 music
2369 swimming
2581 cricket
2643 chess
2643 hockey
2711 volleyball
2872 football
2926 cricket
2959 photography
3125 music
3125 chess
The following SQL query is executed on the above tables:
select hostel
from student natural join hobby
where marks > = 75 and roll between 2000 and 3000;
Relations S and H with the same schema as those of these two tables respectively contain the same information as tuples. A new relation S’ is obtained by the following relational algebra operation: S’ = ∏hostel ((σs.roll = H.rollmarks > 75 and roll > 2000 and roll < 3000 (S)) X (H)) The difference between the number of rows output by the SQL statement and the number of tuples in S’ is  
A
6
B
4
C
2
D
0
SQL    Gate IT 2005    
Discuss it


Question 31 Explanation: 
Output of above Query
Roll Hostel
2369 7
2581 6
2643 5
2643 5
2872 5
2926 5
2959 7
Total   rows selected by running SQL Query: 7 Total rows by Relation Algebra : 4  i.e 5,6,7 (Unique values only) 7-3=4   Answer is B
Question 32
In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items along with the stock-level of these items. Supply = (supplierid, itemcode) Inventory = (itemcode, warehouse, stocklevel) For a specific information required by the management, following SQL query has been written
Select distinct STMP.supplierid
From Supply as STMP
Where not unique (Select ITMP.supplierid
                  From Inventory, Supply as ITMP
                  Where STMP.supplierid = ITMP.supplierid
                  And ITMP.itemcode = Inventory.itemcode
                  And Inventory.warehouse = 'Nagpur');
For the warehouse at Nagpur, this query will find all suppliers who
A
do not supply any item
B
supply exactly one item
C
supply one or more items
D
supply two or more items
SQL    Gate IT 2005    
Discuss it


Question 33
Consider the following database table named water_schemes : x1 The number of tuples returned by the following SQL query is
with total(name, capacity) as
   select district_name, sum(capacity)
   from water_schemes
   group by district_name
with total_avg(capacity) as
   select avg(capacity)
   from total
select name
   from total, total_avg
   where total.capacity >= total_avg.capacity
A
1
B
2
C
3
D
4
SQL    GATE-CS-2016 (Set 2)    
Discuss it


Question 33 Explanation: 
First group by district name is performed and total capacities obtained as following
   Ajmer 20
   Bikaner 40
   Charu 30
   Dungargarh 10 
Then average capacity is computed,
Average Capacity = (20 + 40 + 30 + 10)/4 
                 = 100/4 
                 = 25.
Finally districts with more than average are selected.
Bikaner is 40 which is greater than average (25)
Charu is 30 which is also greater than average (25). 

Therefore answer is 2 tuples.
Question 34
Consider the relations r1(P, Q, R) and r2(R, S, T) with primary keys P and R respectively. The relation r1 contains 2000 tuples and r2 contains 2500 tuples. The maximum size of the join r1⋈ r2 is :  
A
2000
B
2500
C
4500
D
5000
SQL    GATE IT 2006    
Discuss it


Question 34 Explanation: 
  r1⋈ r2  is a join operation done on the common attribute R. Further R is the primary key of R2 When we take a , the value of common attribute( R2 in this case) should match.The value of  R in r2 is matched with corresponding R in r1 . So it will have 2000 tuples. So correct option is (A).  
Question 35
Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-phone)
Enrolment(school-id sch-roll-no, erollno, examname)
ExamResult(erollno, examname, marks)
What does the following SQL query output?
SELECT	sch-name, COUNT (*)
FROM	School C, Enrolment E, ExamResult R
WHERE	E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-id
 /* Add code here. Remove these lines if not writing code */ 
A
for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students
B
for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students
C
for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam
D
nothing; the query has a syntax error
SQL    Gate IT 2008    
Discuss it


Question 35 Explanation: 
In outer SQL query in SELECT sch-name is used where as in GROUP BY clause, school-id is used, that should be same as in SELECT clause.
There are 35 questions to complete.

GATE CS Corner


See Placement Course for placement preparation, GATE Corner for GATE CS Preparation and Quiz Corner for all Quizzes on GeeksQuiz.