The employee information in a company is stored in the relation Employee…
2004
The employee information in a company is stored in the relation
Employee (name, sex, salary, deptName)Consider the following SQL query
select deptName
from Employee
where sex = 'M'
group by deptName
having avg (salary) > (select avg (salary) from Employee)
It returns the names of the department in which
- A.
the average salary is more than the average salary in the company
- B.
the average salary of male employees is more than the average salary of all male employees in the company
- C.
the average salary of male employees is more than the average salary of employees in the same department
- D.
the average salary of male employees is more than the average salary in the company
Attempted by 136 students.
Show answer & explanation
Correct answer: D
Answer: The query returns the names of departments where the average salary of male employees in that department is greater than the overall average salary across the company.
WHERE sex = 'M' filters the rows to male employees only.
GROUP BY deptName groups those male employees by department so aggregate functions apply per department.
avg(salary) in the HAVING clause is the average salary of male employees in that department.
The subquery (select avg(salary) from Employee) computes the overall average salary across all employees in the company (both sexes).
HAVING avg(salary) > (subquery) keeps only those departments whose male-average salary exceeds the company's overall average.
Therefore, the correct interpretation is: the query returns department names where the average salary of male employees in that department is greater than the overall average salary in the company.
A video solution is available for this question — log in and enroll to watch it.