Oracle ¶à±íÁ¬½Ó×Ó²éѯ

1.Çó²¿ÃÅÖÐÄÄЩÈËнˮ×î¸ß£º
select ename,sal
from emp join
(
    select max(sal) max_sal, deptno
    from emp
    group by deptno
) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
2.Çó²¿ÃÅƽ¾ùнˮµÄµÈ¼¶£º
select deptno, avg_sal, grade
from
(
    select deptno, avg(sal) avg_sal
    from emp
    group by deptno
) t join salgrade s
on (t.avg_sal between s.losal and s.hisal);
3.Çó²¿ÃÅƽ¾ùµÄнˮµÈ¼¶£º
select deptno, avg(grade)
from (
    select deptno, ename, grade
    from emp join salgrade s
    on emp.sal between s.losal and s.hisal
) t
group by deptno;
4.ÇóÄÄЩÈËÊǾ­ÀíÈË£º
select ename from emp where empno in (select distinct mgr from emp);
5.²»×¼Óþۼ¯º¯Êý£¬ÇóнˮµÄ×î¸ßÖµ£º
select distinct sal
from emp
where sal not in
(
    select distinct e1.sal
    from emp e1 join emp e2 on (e1.sal < e2.sal)
);
6.Çóƽ¾ùнˮ×î¸ß²¿ÃŵIJ¿ÃűàºÅ£º
select deptno, avg_sal
from (
    select deptno, avg(sal) avg_sal
    from emp
    group by deptno
) where avg_sal = (
    select max(avg_sal)
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    )
);
7.Çóƽ¾ùнˮ×î¸ß²¿ÃŵIJ¿ÃÅÃû³Æ£º
select deptno,dname
from dept
where deptno = (
    select deptno
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    ) where avg_sal = (
        select max(avg_sal)