GATE | GATE-CS-2007 | Question 61

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


Answer: (A)

Explanation: First note that they asked for Anyone (= All) not for Any.
Here, Everyone means all of the group.
Anyone means all or any part of the group.

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 Q1 is the correct query.

Note that if we use ALL in place of Any in second query then this will be correct.

Option (A) is correct.

Quiz of this Question



My Personal Notes arrow_drop_up

Recommended Posts:



3 Average Difficulty : 3/5.0
Based on 2 vote(s)






User Actions