in Databases recategorized by
2,068 views
7 votes
7 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
in Databases recategorized by
by
2441 3624 5537
2.1k views

Subscribe to GO Classes for GATE CSE 2022

2 Answers

5 votes
5 votes
 
Best answer

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
by
35 104 555

2 Comments

How it is correct, can you explain breif
0
0

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

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

2 Comments

but gender is specified as female right ? what is the significance of WHERE here ?
0
0
@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).
0
0
Answer:

Related questions