i have a table
eno dno sal
1 10 200
2 10 150
3 10 100
4 20 75
5 20 100
i want to get sal which is less than the avg sal of thri dept.
eno dno sal
2 10 150
3 10 100
4 20 75
Answers were Sorted based on User's Feedback
Answer / praveen
select * from emp e where sal<
(select avg(sal) from emp where deptno=e.deptno)
order by deptno
Is This Answer Correct ? | 20 Yes | 2 No |
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
/
Is This Answer Correct ? | 5 Yes | 4 No |
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)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / madhu
select * from emp where sal<(select avg(sal) from emp where
eno in(2,3,4))
Is This Answer Correct ? | 0 Yes | 0 No |
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;
Is This Answer Correct ? | 0 Yes | 0 No |
select department_id,salary from employees e
where salary<(select trunc(avg(salary))
from employees where department_id=e.department_id;
Is This Answer Correct ? | 0 Yes | 0 No |
select salary from emp
wher dno in (select dno
from dept
where salary < any
(select avg(salary)
from dept
where dept = 3)
Is This Answer Correct ? | 0 Yes | 0 No |
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)
Is This Answer Correct ? | 4 Yes | 5 No |
Answer / mohan
SELECT dno,sal
FROM EMP
WHERE sal>ANY(SELECT AVG(sal)
FROM emp
GROUP BY dno)
ORDER BY dno;
Is This Answer Correct ? | 0 Yes | 2 No |
what is meant by DML?
mail-id table contains two columns(email_id,userid) it contains different types of mail-ids,and no of users. here username length is differ na,(ex- tamil@yahoo.com,joshua@hotmail.com like) now i want to fetch the email-ids only starting from '@' (ex-@gmail.com,@yahoo.com,@hotmail.com
How do I run a script in sql developer?
display records from 5 to 9 using rowid or rownum
What is the usage of sql functions?
Create a procedure to delete certain records from a table and display the total number of records deleted in this process. (Condition for deletion can be of ur choice, for instance delete all records where eid='')
What is exit statement?
What can I use instead of union in sql?
Is it possible to access the current value in a session before accessing next value?
if we give update table_name set column_name= default. what will happen?
Why primary key is required?
Why do we use function in pl sql?