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 correlated sub-query?
Can we create table in function?
what are string data types? : Sql dba
explain the advantages and disadvantages of stored procedure? : Sql dba
Where is sql database stored?
What is sql not null constraint?
I have a table .in the table 100 recored is there .we have get the single row with out using clause..
What is the usage of the distinct keyword?
Is coalesce faster than isnull?
Why do we use triggers?
how can we know the number of days between two given dates using mysql? : Sql dba
how many tables will create when we create table, what are they? : Sql dba