8,256 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

$(A)$the average salary is more than the average salary in the company.

What is the meaning of this option?

@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

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

"Any attribute that is present in the having
clause without being aggregated must appear in the group by clause, otherwise
the query is treated as erroneous." Korth 6th edition chapter 3 , page 88.

The inner query is over all department and over both male and female employees while the outer query is only for male employees.
by

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

@Cristina6884 here group by

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

Here group by clause is used to support having clause...
In outer query :the average salary of male employees in each department

inner query:  the average salary in the company

ans d
by

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

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

"The names of the department in which" is already mentioned in the question..
A) the names of the department in which the average salary is more than the average salary in the company

select deptName

from Employee

Group by deptName

Having avg(salary) > ( select avg(salary) from Employee )

B)the names of the department in which the average salary of male employees is more than the average salary of all male employees in the company

select deptName

from Employee

where sex=’M’

Group by deptName

Having avg (salary) > ( select avg(salary) from Employee where SEX=”M” )

C)the names of the department in which  the average salary of male employees is more than the average salary of employees in same the department

select deptName

from Employee  e

where sex=”M”

Group by deptName

Having avg(salary) > ( select avg(salary) from Employee ee where ee.deptName = e.deptName )

D)the names of the department in which the average salary of male employees is more than the average salary in the company

same as querry provided in question.