如何优化如下sql
/*1.1*/
select e.ename,e.hiredate
from emp e
where e.hiredate=(select min(hiredate) from emp) or e.hiredate=(select max(hiredate) from emp);
/*1.2*/
select grade,count(*)
from emp e,salgrade s
where sal<=s.hisal and sal>=s.losal
group by grade;
/*1.3*/
select d.dname,sum(e.sal)
from emp e,dept d
where d.deptno=e.deptno
group by d.dname;
/*1.4*/
select e.deptno,e.ename,e.sal
from emp e
order by e.deptno,e.sal desc;
/*1.5*/
select e1.ename,e2.ename manger
from emp e1,(select empno,ename from emp) e2
where e1.mgr=e2.empno
/*1.6*/
select e.deptno,sum(e.sal+nvl(e.comm,0)),avg(e.sal+nvl(e.comm,0)),max(e.sal+nvl(e.comm,0)),min(e.sal+nvl(e.comm,0))
from emp e
group by e.deptno;
/*1.1*/
select e.ename,e.hiredate
from emp e
where e.hiredate=(select min(hiredate) from emp) or e.hiredate=(select max(hiredate) from emp);
/*1.2*/
select grade,count(*)
from emp e,salgrade s
where sal<=s.hisal and sal>=s.losal
group by grade;
/*1.3*/
select d.dname,sum(e.sal)
from emp e,dept d
where d.deptno=e.deptno
group by d.dname;
/*1.4*/
select e.deptno,e.ename,e.sal
from emp e
order by e.deptno,e.sal desc;
/*1.5*/
select e1.ename,e2.ename manger
from emp e1,(select empno,ename from emp) e2
where e1.mgr=e2.empno
/*1.6*/
select e.deptno,sum(e.sal+nvl(e.comm,0)),avg(e.sal+nvl(e.comm,0)),max(e.sal+nvl(e.comm,0)),min(e.sal+nvl(e.comm,0))
from emp e
group by e.deptno;
select e.rn,e.empno,e.ename,e.sal
from(select rownum rn,empno,ename,sal from (select empno,ename,sal from emp order by sal desc)) e
where rn=2;select rownum,empno,ename,sal from (select empno,ename,sal from emp order by sal desc)where rownum<=2
minus
select rownum,empno,ename,sal from (select empno,ename,sal from emp order by sal desc) where rownum<=1;select e.empno,e.ename,e.sal from
(select empno,ename,sal,dense_rank() over (order by sal desc) rank from emp) e
where rank = 2;