The relation scheme given below is used to store information about the…
2021
The relation scheme given below is used to store information about the employees of a company, where empId is the key and deptId indicates the department to which the employee is assigned. Each employee is assigned to exactly one department.
emp(<u>empId</u>, name, gender, salary, deptId)Consider the following SQL query:
select deptId, count(*) from emp where gender = “female” and salary > (select avg(salary)from emp) group by deptId;The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of
- A.
employees in the department.
- B.
employees in the company.
- C.
female employees in the department.
- D.
female employees in the company.
Attempted by 161 students.
Show answer & explanation
Correct answer: B
Key insight: the subquery select avg(salary) from emp computes the average salary over the entire emp table (all employees).
Step 1: The subquery has no WHERE clause, so AVG(salary) is the company-wide average salary.
Step 2: The outer WHERE filters rows to female employees with salary greater than that company-wide average.
Step 3: GROUP BY deptId then counts how many such female employees exist in each department.
Therefore, for each department this query returns the number of female employees in that department whose salary is greater than the average salary of employees in the company.
A video solution is available for this question — log in and enroll to watch it.