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>

"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