2,068 views

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

### Subscribe to GO Classes for GATE CSE 2022

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.

by
35 104 555

How it is correct, can you explain breif

@Shaik Masthan Sir, any resource for understanding the difference between nested queries and co related queries. Please share your recommendations.

Option B, as the sub-query runs on emp table which contains both male and female employees from the company.
by
1

but gender is specified as female right ? what is the significance of WHERE here ?
@Shrusti
but gender is specified as female right ? what is the significance of WHERE here ?

the gender is specified for the outer query but in the inner query(subquery) it is clearly asking for the avg(salary) from the emp table (i.e avg salary of all the employees in the company).

1
3,822 views