SQL - Dispaly employee records who gets more salary than the average salary in their department?

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 140 of 173

Dispaly employee records who gets more salary than the average salary in their department?

Total Answers and Comments: 8

Last Update: October 15, 2008 Asked by: kowmudiswarna

Sponsored Links

Best Rated Answer

—

Submitted by: patilpravin_1981

select * from emp
where salary >(select avg(salary) from dept having emp.dept_id=dept.dept_id);

Above answer was rated as good by the following members:
sureshkumar.mtech

September 21, 2007 07:02:50

Ashutosh Srivastava

RE: Dispaly employee records who gets more salary than...

select * from emp a
(select avg(sal) aa deptno from emp group by deptno)x
where a.DEPTNO x.deptno and a.sal>x.aa

Is this answer useful? Yes | No

September 21, 2007 10:58:23

appidixyz

Member Since: March 2007 Contribution: 2

RE: Dispaly employee records who gets more salary than...

select a.* from emp a

(select deptno avg ( sal) avg_sal from emp group by deptno) b

where a.deptno b.deptno

and a.sal > b.avg_sal

Is this answer useful? Yes | No

September 22, 2007 04:39:36

Surender

RE: Dispaly employee records who gets more salary than...

select * from emp e where sal>(select avg(sal) from emp where deptno e.deptno)

Is this answer useful? Yes | No

September 24, 2007 06:56:38

kowmudiswarna

Member Since: September 2007 Contribution: 21

RE: Dispaly employee records who gets more salary than...

Hi Surender

Thank u very much for reply to my query.

Is this answer useful? Yes | No

January 29, 2008 05:17:52

patilpravin_1981

Member Since: December 2007 Contribution: 29

RE: Dispaly employee records who gets more salary than the average salary in their department?

select * from emp
where salary >(select avg(salary) group by department_id);

Is this answer useful? Yes | No

January 29, 2008 05:21:07

patilpravin_1981

Member Since: December 2007 Contribution: 29

RE: Dispaly employee records who gets more salary than the average salary in their department?

select * from emp
where salary >(select avg(salary) from dept having emp.dept_id dept.dept_id);

Is this answer useful? Yes | No

1 0

Overall Rating: +1

July 07, 2008 13:38:42

chaitanya61

Member Since: July 2008 Contribution: 3

RE: Dispaly employee records who gets more salary than the average salary in their department?

select b.ename b.deptno b.sal avg_salfrom (select ename deptno avg(sal) over (partition by deptno) avg_sal empno from emp) a emp bwhere a.empno b.empno and b.sal>a.avg_salorder by b.deptno;

Is this answer useful? Yes | No

October 15, 2008 07:28:28

sureshkumar.mtech

Member Since: May 2008 Contribution: 37

RE: Dispaly employee records who gets more salary than the average salary in their department?

select * from employe
where
salary > (
select avg(salary) from dept employe where dept.deptno employe.deptno);

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank