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

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


Answer: (C)

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.

Quiz of this Question



My Personal Notes arrow_drop_up