edited by
11,198 views
38 votes
38 votes

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 by

3 Answers

Best answer
30 votes
30 votes
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.
selected by
4 votes
4 votes
In outer query :the average salary of male employees in each department

inner query:  the average salary in the company

ans d
2 votes
2 votes
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:

Related questions

26 votes
26 votes
2 answers
1
Kathleen asked Sep 18, 2014
11,271 views
The order of an internal node in a $B+$ tree index is the maximum number of children it can have. Suppose that a child pointer takes $6$ bytes, the search field value tak...