The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+15 votes
1k 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 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

asked in Databases by Veteran (69k points) | 1k views

2 Answers

+18 votes
Best answer
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.
answered by Veteran (346k points)
selected by
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
"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".   :)

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?

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

right?

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.

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.

 

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

inner query:  the average salary in the company

ans d
answered by Junior (669 points)


Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

33,687 questions
40,230 answers
114,268 comments
38,793 users