GATE CSE 2021 Set 2 | Question: 31

Question

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

• A. employees in the department
• B. employees in the company
• C. female employees in the department
• D. female employees in the company

Comments

here  “”select  avg(salary) from emp “” is not correleted query so just evaluate it first

how to identify correleted query or independent query:-

1)try to see inside nested quesry and try to solve if indpendently if you can evaluate inner quesry without dependepnt on outer query then inner query will be simple quesry

or you can identify like that also

2) if inner query used some attribute of outer query then these inner query are correleted nested query and from 1) you

can see here if you will try to evaluate inner quesry then you have to go to see outer query also becuse inner quesry is

using attribute of outer quesry.
______________________________________________________________________________________-------------

--------------------------------------------------------------------------------------------------------------------------------------------------------
evaluation order of these quesry  :: where-->group by--->selection
----------------------------------------------------------------------------------------------------------------------------------------------------

now, come to these question
1 step ) evluate inner query which gives avg salary of compony

here where-----> will select all female employe whose salary is greter then avg salary

now, after that group by evaluted and grouping will happen by departemnt id

then if you will select then dept id will come and each deprtment contain how much female emp that will come
------------------------------------------------------------------------------------------------------------------------------------------

Answer 1

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.

Comments

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.

Answer 2

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

Comments

The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of

The question is asking if the outer statement is true for the condition in the inner query is? 

 Correct me if i am wrong.

---

the question is asking about the inner query , please read the question first , its aksing “the number of female employees whose salary is greater than the average salary of” all the employees in the company 
so answer is B

---

@Shaik Masthan sir, @Abhrajyoti00 da when Group by clause execute first...

if group by class execute first then the answer must be

1. employees in the department

Please clarify my concepts.