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

This problem involves understanding the SQL query and determining what it computes based on the given schema:

The schema is:

\(\text{emp}(\text{empId}, \text{name}, \text{gender}, \text{salary}, \text{deptId})\)

Let’s break down the SQL query:

     

    SELECT deptId, count(*)
    FROM emp
    WHERE gender = "female" 
      AND salary > (SELECT avg(salary) FROM emp)
    GROUP BY deptId;
    

   

1. Understanding the Aggregation: The query uses GROUP BY deptId, which means the results will be grouped by department.
2. Counting Female Employees: Within each group (department), the query counts the number of employees. It particularly focuses only on those records where the gender is "female".
3. Subquery Impact:
   • The subquery \(SELECT \, avg(salary) \, FROM \, emp\) calculates the average salary of all employees in the company.
4. Salary Condition: The main query further filters to include only those female employees whose salary is greater than the average salary computed over all employees in the company.

Conclusion: For each department, this SQL query determines the number of female employees whose salary exceeds the company's average salary, not just the department's average. Therefore, the correct option is "employees in the company."