Consider the following relation : Works (emp_name, company_name, salary)
Consider the following relation :
Works (emp_name, company_name, salary)
Here, emp_name is primary key.
Consider the following SQL query
Select emp_name
From works T
where salary > (select avg (salary)
from works S
where T.company_name = S.company_name)
The above query if for following :
Show answerHide answer
Correct answer: (D) Find all employees who earn more than the average salary of all employees of their company
Explanation
The correlated subquery computes the average salary of the employee's own company (T.company_name = S.company_name). The query does not use max, so it does not select only the highest paid employee; it selects all employees who earn more than the average salary of their company.