Question

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. 
\[ \text{emp}(\text{empId},\ \text{name},\ \text{gender},\ \text{salary},\ \text{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.

Hint:

If a subquery does not reference attributes from the outer query, it is evaluated once and applies globally to all groups.

Answer 1

The Correct Option is B

Step 1: Understand the subquery.
The subquery \[ (\text{select avg(salary) from emp}) \] computes the average salary over the entire \texttt{emp} table, that is, the average salary of all employees in the company, irrespective of department or gender.

Step 2: Analyze the WHERE clause.
The condition \[ \text{gender = "female" and salary > (select avg(salary) from emp)} \] filters only those employees who are female and whose salary is greater than the company-wide average salary.

Step 3: Role of GROUP BY.
The clause \[ \text{group by deptId} \] groups the filtered female employees by their department and counts them for each department.

Step 4: Final interpretation.
Hence, for each department, the query counts female employees whose salary exceeds the average salary of all employees in the company.

Final Answer: (B)