1.9k views

The employee information in a company is stored in the relation

• Employee (name, sex, salary, deptName)

Consider the following SQL query

Select deptName
From Employee
Where sex = ‘M’
Group by deptName
Having avg(salary) >
(select avg (salary) from Employee)


It returns the names of the department in which

1. the average salary is more than the average salary in the company

2. the average salary of male employees is more than the average salary of all male employees in the company

3. the average salary of male employees is more than the average salary of employees in same the department

4. the average salary of male employees is more than the average salary in the company

edited | 1.9k views
0
$(A)$the average salary is more than the average salary in the company.

What is the meaning of this option?
0

@Lakshman Patel RJIT a company can have both male and female employ so this option means the average salary of departmeant (uncluding both female and male )    rest are same as d option

0

Can we have HAVING salary instead of HAVING avg(salary) here???

The inner query is over all department and over both male and female employees while the outer query is only for male employees.
by Veteran (416k points)
selected
+6
In case if "where"," group by" and "having" clauses are altogether in the same query then order of execution is:
1)where
2)group by
3)having
0
"where" is applied before filtering anything from relation i.e it is applied to the whole relation always but "having" is applied to filtered values from the relation which came after "group by".   :)
0

then what is significance of group by in this query?

I mean

Select deptName
From Employee
Where sex = ‘M’
Group by deptName
Having avg(salary) >
(select avg (salary) from Employee)

what is group by actually doing here?

0
group by is grouping department name, which has no need here

right?
+2

I think this query is doing "Give Dept-Name such that where department's average salary of their (considering only) male employees is greater than the average salary of all the company employees(male and female both)". Which seem to match none.

+4

Hello srestha

You leave Group by DeptName from our original query and hence you will get NULL as result.

'Having' clause works on groups defined by 'group by' clause. If you forget to use group by clause then having will work like Where clause and hence it will consider whole relation as one group and calculate salary for whole relation which is nothing but avg salary and hence overall query become avg salary > avg salary , hope you can guess when we say x>x , the result will be false.

0

why will it return NULL?

If we write like this

Select deptName
From Employee
Where sex = ‘M’
Having avg(salary) >
(select avg (salary) from Employee)

which will return same tuples as above query

0

'having' is used only if the query has 'group by'.

0

@srestha ma'am answer of above comment : It will produce NULL (return with no record)

0
I am not getting how group by clause has no significance here?
0

@Cristina6884 here group by

grouping all male according to department so the avg we calculated is the avg of male salary in that department

In outer query :the average salary of male employees in each department

inner query:  the average salary in the company

ans d
by (479 points)
0
Answer should be Department names in which Avg Salary of male employees in that department is greater than Avg salary of all employees in the table
0

In outer query :the average salary of male employees in each department

inner query:  the average salary in the company

options d does not specify in each department so i think answer d also wrong

1
2