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)
- WHERE EXISTS tests for the existence of any records in a subquery.
- EXISTS returns true if the subquery returns one or more records.
- 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:
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.
This solution is contributed by Yashika Arora.
Quiz of this Question