SQL Interview Questions:3

3. FETCH THE EMPLOYEES WHOSE SALARY IS GREATER THAN AVERAGE SALARY OF THEIR DEPARTMENT.

CREATE  table employee

(

id int,

name varchar (100),

dept_Id int,

salary int

)

insert into employee values(1,'vineela',1,20000),(2,'lakshmi',1,30000),(3,'gayithri',1,30000),

(4,'mukundh',2,45000),(5,'kavya',2,300000),(6,'sithara,2,45000)

select * from employee

method1:-

        select a.name,a.dept_Id,a.salary from employee a where a.salary >(select avg(salary) from         employee b where b.dept_Id=a.dept_Id)

                                            

method2:-

select t1.name,t1.dept_Id,,t1.salary from employee as t1 join (select dept_Id,avg(salary) as avgsalary from employee group by dept_Id) as t2 on t1.dept_Id=t2.dept_Id where salary>avgsalary

                                            

method3:-

select a.name, a.dept_Id, a.salary from (select name, salary ,dept_Id, avg(salary) over(partition by dept_Id) as avg_salary from employee )a where a.salary>avg_salary