Consider the following relation: Works(emp_name, company_name,salary) Here,emp…
2017
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 is for following:
- A.
Find the highest paid employee who earns more than the average salary of all employees of his company.
- B.
Find the highest paid employee who earns more than the average salary of all the employees of all the companies.
- C.
Find all employees who earn more than the average salary of all employees all the companies.
- D.
Find all employees who earn more than the average salary of all employees of their company.
Attempted by 112 students.
Show answer & explanation
Correct answer: D
Correct interpretation: The query returns all employees who earn more than the average salary of their own company.
The inner subquery select avg(salary) from works S where T.company_name = S.company_name is a correlated subquery that computes the average salary for the company of the current outer row.
The outer WHERE salary > (that average) filters and returns every employee whose salary is greater than their company's average.
This does not return a single highest-paid employee. To get the highest-paid employee per company you would use MAX(salary) with GROUP BY company_name or ORDER BY salary DESC with a LIMIT per company.
Because emp_name is the primary key, each returned row uniquely identifies one employee.
A video solution is available for this question — log in and enroll to watch it.