Question

Suppose the relation Employee(SSN,Name,Salary,Department). Which of the followings returns all those employees with salary exceeds salary of all employees from department 1?

Select one:
a. SELECT * FROM Employee WHERE Salary >= ALL (SELECT Salary FROM Employee WHERE Department=1)
b. SELECT * FROM Employee WHERE Salary >= (SELECT MAX(Salary) FROM Employee WHERE Department=1)
c. SELECT * FROM Employee WHERE Salary >=ANY (SELECT MAX(Salary) FROM Employee WHERE Department=1)
d. All of the others

Answer 1

The best SQL statement to find employees earning more than the highest salary in department 1 is option (b), using a subquery to ascertain the maximum salary from department 1 for the comparison.

Step-by-step explanation:

The correct answer to the question is option (b): SELECT * FROM Employee WHERE Salary >= (SELECT MAX(Salary) FROM Employee WHERE Department=1). This SQL query is retrieving a list of all employees whose salary exceeds the highest salary within department 1. It utilizes a subquery to first determine the highest (maximum) salary in department 1 and then compares the salary of each employee against this figure.

Option (a) is not the best answer as it compares salaries against every individual salary in department 1, not just the highest one. Option (c) is incorrect because the ANY operator would return true if the employee's salary is greater than or equal to any of the salaries in department 1, which is not the requirement. Furthermore, 'MAX(Salary)' would only return one value; therefore, using ANY in this context would not make sense.