Ashutosh Srivastava

• Sep 21st, 2007

 

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

appidixyz

 Profile Answers by appidixyz 

• Sep 21st, 2007

 

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

Surender

• Sep 22nd, 2007

 

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

kowmudiswarna

 Profile Answers by kowmudiswarna Questions by kowmudiswarna

• Sep 24th, 2007

 

Hi Surender,
 

Thank u very much for reply to my query.

patilpravin_1981

 Profile Answers by patilpravin_1981 

• Jan 29th, 2008

 

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

patilpravin_1981

 Profile Answers by patilpravin_1981 

• Jan 29th, 2008

 

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

chaitanya61

 Profile Answers by chaitanya61 

• Jul 7th, 2008

 

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;

sureshkumar.mtech

 Profile Answers by sureshkumar.mtech Questions by sureshkumar.mtech

• Oct 15th, 2008

 

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

yagneswarasankar

 Profile Answers by yagneswarasankar 

• Nov 23rd, 2010

 

  1  select employee_id,last_name,a.department_Id,salary,b.avg_sal   from employees a ,
  2  (select avg(salary) avg_sal,department_id from employees group by department_id) b
  3  where salary > (select avg(salary) from employees where department_id = a.department_id group by department_Id )
  4  and a.department_id = b.department_id
  5* order by a.department_Id
SQL> /

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID     SALARY    AVG_SAL
----------- ------------------------- ------------- ---------- ----------
        201 Hartstein                            20      13000       9500
        124 Mourgos                              50       5800       3500
        103 Hunold                               60       9000       7400
        104 Ernst                                60       9000       7400
        149 Zlotkey                              80      10500 10033.3333
        174 Abel                                 80      11000 10033.3333
        100 King                                 90      24000 19333.3333
        205 Higgins                             110      12000      10150

8 rows selected.

nirmal1in

 Profile Answers by nirmal1in Questions by nirmal1in

• Nov 24th, 2010

 

Below is example query to get the employee details who gets the more than avg sal of their department.

select * from emp where salary > (select avg(salary) from emp a where a.deptno = emp.deptno group by deptno)

krishsidd

 Profile Answers by krishsidd 

• Nov 26th, 2010

 

1) select orderamount,salespersonid, row_number() over(order by orderamount
desc) rank from
(SELECT OrderAmount,SalesPersonID FROM orders order by orderamount desc) y where
rownum <= 5;

2) select orderamount,salespersonid, x rank from (SELECT
OrderAmount,SalesPersonID, rank() over(order by orderamount desc) x FROM orders)
y where x <=5;

3) select orderamount,salespersonid, x rank from (SELECT
OrderAmount,SalesPersonID, dense_rank() over(order by orderamount desc) x FROM
orders) y where x <=5;
For a,b - refer to 1
For c,d - can be done in 2 different ways as in 2 & 3

Nikhil

• Oct 12th, 2011

 

Code
SELECT sal FROM emp e,dept d


WHERE e.depid=d.depid AND e.sal>(SELECT avg(e.sal) FROM emp e);

saikumar

• Nov 2nd, 2011

 

select * from emp where sal>(select avg(sal) from emp);

may be this not correct

kranthi swaroop

• Dec 3rd, 2011

 

Try this

Code
SELECT ENAME,SAL


WHERE SAL > (SELECT AVG(SAL)


                         FROM EMP


                         WHERE DEPTNAME ='COOK') -- Gets emp whose sal 


                                                                                greater THAN Average sal 


                                                                                FROM his department


ORDER BY 2 ASC

uma

• Mar 5th, 2012

 

select * from emp a
where salary >(select avg(salary) from emp b where a.dept_no = b.dept_no);

farhan

• Apr 20th, 2012

 

Code
SELECT * FROM emp  WHERE sal >(SELECT avg(sal) FROM emp)