oracle²éѯÓï¾äÈëÃÅ

»·¾³£ºoracle 10g    
Óû§£ºscott/tiger   
±í£ºemp£¨¹ÍÔ±±í£©¡¢dept£¨²¿ÃÅ±í£©¡¢salgrade£¨¹¤×ʵȼ¶±í£©
1 Çó²¿ÃÅÖÐÄÄЩÈ˵Äнˮ×î¸ß
select t.deptno, 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 t.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 t.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 avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
  (select max(avg(sal)) from emp group by deptno)
7 Çóƽ¾ùнˮ×î¸ßµÄ²¿ÃŵIJ¿ÃÅÃû³Æ
select dname from dept where deptno =
(
  select deptno from 
    (select avg(sal) avg_sal,deptno from emp group by deptno)
  where avg_sal =
    (
      select max(avg_sal) from
        (select avg(sal) avg_sal,deptno from emp group by deptno)
    )
)
8 Çóƽ¾ùнˮµÄµÈ¼¶×îµÍµÄ²¿ÃŵIJ¿ÃÅÃû³Æ
select dname,t1.deptno,grade,avg_sal from
(
  select deptno,grade,avg_sal 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)
) t1
join dept on (t1.deptno = dept.deptno)
wher