经典sql练习题答案

1. select dname,deptno
   from dept
   where deptno in (select deptno from emp);
2. select w.ename,w.hiredate,m.ename,m.hiredate
   from emp w,emp m
   where w.mgr=m.empno and w.hiredate<m.hiredate;
3. select e.ename,d.dname
   from emp e,dept d
   where e.deptno=d.deptno and e.job='clerk';
4. select job
   from emp
   group by job
   having  min(sal+nvl(coom,0)>1500;
5. select e.ename
   from emp e
   minus
   select e.ename
   from emp e,dept d
   where e.deptno=d.deptno;
6. select ename
   from emp
   having  (sal+nvl(coom,0))>avg(sal+nvl(coom,0));
7. select max(sal),ename
   from emp;
8. select d.*,const(e.deptno) 
   from emp e,dept d
   where e.deptno(+)=d.deptno;
9 .select ename,sal
   from emp
   group by deptno