GATE CSE 2004 | Question: 53

Question

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

• A. the average salary is more than the average salary in the company

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

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

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

Comments

Gurdeep Saini

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.

---

Detailed Video Solution: https://youtu.be/zb6vXpZVo1E?t=3553&feature=shared

Answer 1

D is the answer.

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

Comments

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...

Answer 2

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

inner query:  the average salary in the company

ans d

Comments

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..

Answer 3

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.

Answer 4

2