Open In App

GATE | GATE-CS-2015 (Mock Test) | Question 13

Like Article
Like
Save
Share
Report

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)

6
 

(B)

5
 

(C)

4
 

(D)

0
 


Answer: (B)

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: 




Hence option (C) 5 rows.
 


Quiz of this Question
Please comment below if you find anything wrong in the above post


Last Updated : 28 Jun, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads