Answer / praveen

select * from emp e where sal<
(select avg(sal) from emp where deptno=e.deptno)
order by deptno

Answer / subbu

select e.eno,e.dno,e.sal
from emp_t e,
(select dno,avg(sal) avgsal from emp_t group by dno) b
where e.sal<b.avgsal and e.dno=b.dno
/

Answer / biswa

select *
from (select eno,dno,sal,avg(sal) over(partition by dno) as
avg_sal
from emp)
where sal<avg_sal;

OR

select e1.empno,e1.deptno,e1.sal
from emp e1
where sal>(select avg(sal) from emp e2
where e1.deptno=e2.deptno)

Answer / madhu

select * from emp where sal<(select avg(sal) from emp where
eno in(2,3,4))

Answer / ajit

select eno, dno, sal
from test
group by eno, dno, sal
having sal <any ( select avg(sal) from test )

Answer / mahalakshmi s

select * from (select id,(case when sal < avg(sal) over(partition by id)  then sal else 0 end) avg_val from dept) a where avg_val !=0;

Answer / abhishekjaiswal

select department_id,salary from employees e 
where salary<(select trunc(avg(salary)) 
from employees where department_id=e.department_id;

Answer / rutujagabhane

select salary from emp
wher dno in (select dno
from dept
where salary < any
(select avg(salary)
from dept
where dept = 3)

Answer / prasant

select sal from demo where sal<(select avg(sal) from demo
where eno in (2,3,4));

NOTE:first create table demo and insert all these 5 rows
then run querry.
If any issue call me(PRASANT)

Answer / mohan

SELECT dno,sal
FROM EMP
WHERE sal>ANY(SELECT AVG(sal)
FROM emp
GROUP BY dno)
ORDER BY dno;

What is the difference between left join and right join?

0 Answers  

Can you join a table to itself?

0 Answers  

What is compilation error in pl sql?

0 Answers  

What is the difference between delete and truncate commands?

0 Answers  

Difference between truncate, delete and drop commands?

0 Answers  

how to convert numeric values to character strings? : Sql dba

0 Answers  

what is the Default Libraries for Oracle Report 6i

0 Answers   IBM,

What is ttitle and btitle?

0 Answers  

What is normalization? dec 2009

3 Answers   Cognizant,

What is Data Concarency and Consistency?

1 Answers  

What are the conditions an underlying table must satisfy before a cursor can be used by a positioned update or delete statement? : Transact sql

0 Answers  

What is Highwatermark?

3 Answers   Thermotech,