oracle习题三.docVIP

oracle习题带答案（二） 2008-09-03 19:42 使用scott/tiger用户下的emp表和dept表完成下列练习 Emp员工表 empno ename job Mgr Hiredate Sal Comm Deptno 员工号 员工姓名 工作 上级编号 受雇日期 薪金 佣金 部门编号 Dept表 Deptno Dname Loc 部门编号 部门名称 地点 1. select distinct dname from dept where deptno in (select distinct deptno from emp); 要查部门编号如果存在于emp 表中，说明这个部门有员工。 2. select ename,job,mgr,hiredate,sal from emp where sal (select sal from emp where ename=SMITH); 3. select e.ename, e.mgr , w.ename from emp e, emp w where e.mgr=w.empno; 4. select e.ename, e.mgr , e.hiredate,w.ename,w.hiredate from emp e, emp w where e.mgr=w.empno and e.hiredate w.hiredate; 5. select e.ename,e.hiredate, d.dname,d.deptno from emp e, dept d where e.deptno (+)=d.deptno; 本例子使用的是外连接， 也可以用右连接。 select e.ename,e.hiredate, d.dname,d.deptno from emp e right join dept d on e.deptno=d.deptno; 6. select e.ename,e.hiredate,e.job, d.dname,d.deptno from emp e, dept d where e.deptno =d.deptno and e.job=upper(clerk); 7. select job, min(sal) from emp group by job having min(sal)1500; 8. select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname=upper(sales); 或者： select ename from emp where deptno = (select deptno from dept where dname=upper(SALES)); 9. select ename, job, sal from emp where sal (select avg(sal) from emp ); 10. select ename, job from emp where job = (select job from emp where ename=upper(scott)); 11. select ename, sal,job from emp where sal in (select sal from emp where deptno=30); 12. select ename, sal,job from emp where sal all (select sal from emp where deptno=30); 或者 select ename ,sal from emp where sal (select max(sal) from emp where deptno=30); 13. select d.deptno,d.dname, count(e.ename), avg(e.sal), avg(months_between(sysdate,e.hiredate)) from emp e, dept d where e.deptno(+)= d.deptno group by d.deptno,dname; 或者下面的方法，这种方法更好理解，把需要汇总的生成一个视图e。 s