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;

How many types of functions are there in sql?

0 Answers  

How to revise and re-run the last sql command?

0 Answers  

Are sql views compiled?

1 Answers  

What is the use of & in pl sql?

0 Answers  

1) Synonyms 2) Co-related Subquery 3) Different Jobs in Plsql 4) Explain Plan 5) Wrap 6) Query Optimization Technique 7) Bulk Collect 8) Types of index 9) IF primary key is created then the index created ? 10) Foreign Key 11) Exception Handling 12) Difference Between Delete and Trunc 13) Procedure Overloading 14) Grant Revoke 15) Procedure Argument types. 16) Functions. 17) Joins

0 Answers   CTS,

What is the life of an sql statement?

0 Answers  

what are the system privileges that are required by a schema owner (user) to create a trigger on a table?

0 Answers  

How do I tune a sql query?

0 Answers  

How many sql commands are there?

0 Answers  

What is sharding in sql?

0 Answers  

What are the different sql languages?

0 Answers  

How can check sql version from command line?

0 Answers