Re: how??
From: Spencer <spencerp_at_swbell.net>
Date: Fri, 24 Aug 2001 21:30:18 -0500
Message-ID: <G2Eh7.147$Se3.7930_at_nnrp1.sbc.net>
where e.deptno = m.deptno
and e.sal = m.min_sal
where e.deptno = a.deptno
and e.sal < a.avg_sal
Date: Fri, 24 Aug 2001 21:30:18 -0500
Message-ID: <G2Eh7.147$Se3.7930_at_nnrp1.sbc.net>
"Eun-jeong Jeon" <zeni_at_hananet.net> wrote in message
news:pCKfpkFLBHA.357_at_news2.sys.netsgo.com...
>
> I know ename, deptno and sal of employees who has minimum salary in their
> department.
> I use pair-wise subquery.
>
> SQL> select ename, deptno, sal
> 2 from emp
> 3 where (deptno, sal) in (select deptno, min(sal)
> 4 from emp
> 5 group by deptno);
>
>
> I want to know ename, deptno and sal
> of employees who are paid less than average salary in their deptartment.
>
> What should I do? please help me~~
>
>
compare your query to this one:
select e.ename , e.deptno , e.sal from emp e , (select s.deptno , min(s.sal) as min_sal from emp s group by s.deptno ) m
where e.deptno = m.deptno
and e.sal = m.min_sal
and then try something like this:
select e.ename , e.deptno , e.sal from emp e , (select s.deptno , avg(s.sal) as avg_sal from emp s group by s.deptno ) a
where e.deptno = a.deptno
and e.sal < a.avg_sal
hth Received on Sat Aug 25 2001 - 04:30:18 CEST