retagged by
6,009 views
16 votes
16 votes

The relation scheme given below is used to store information about the employees of a company, where $\textsf{empId}$ is the key and $\textsf{deptId}$ indicates the department to which the employee is assigned. Each employee is assigned to exactly one department.

$$\textsf{emp($\underline{\textsf{empId}}$, name, gender, salary, deptId)}$$

Consider the following $\text{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

  1. employees in the department
  2. employees in the company
  3. female employees in the department
  4. female employees in the company
retagged by

2 Answers

Best answer
12 votes
12 votes

It’s a nested query but not Co-related query. 

Evaluate the innermost query first.

select avg(salary)
from emp

It is given that emp represent employees of a company.

So, Option B is the correct answer.

selected by
3 votes
3 votes
Option B, as the sub-query runs on emp table which contains both male and female employees from the company.
Answer:

Related questions