oracle练习题目

使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下

　　emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

　　dept部门表(deptno部门编号/dname部门名称/loc地点)

　　工资 ＝ 薪金 ＋ 佣金

1．列出至少有一个员工的所有部门

方法1：select t.* from scott.dept t where exists( select 1 from scott.emp a where a.deptno = t.deptno);

方法2：select t.* from scott.dept t where t.deptno in ( select a.deptno from scott.emp a );

2．列出薪金比“SMITH”多的所有员工。

select t.*

  from scott.emp t

where t.sal > (select a.sal from scott.emp a where upper(a.ename) = 'SMITH')



3．列出所有员工的姓名及其直接上级的姓名。

select t.ename, a.ename from scott.emp t, scott.emp a where a.empno = t.mgr



4．列出受雇日期晚于其直接上级的所有员工。

select t.* from scott.emp t, scott.emp a where a.empno = t.mgr and t.hiredate > a.hiredate



5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

select t.dname,a.* from scott.dept t, scott.emp a where a.deptno(+) = t.deptno



6．列出所有“CLERK”（办事员）的姓名及其部门名称。

select a.ename,t.dname from scott.dept t, scott.emp a where a.deptno = t.deptno  and a.job='CLERK';



7．列出最低薪金大于1500的各种工作。

select t.job

  from scott.emp t

having(min(t.sal)) > 1500

group by t.job;

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

select t.* from scott.emp t where t.deptno in (select a.deptno from scott.dept a where a.dname='SALES') ;



9．列出薪金高于公司平均薪金的所有员工。

select t.* from scott.emp t where t.sal > (select avg(a.sal) from scott.emp a);

10．列出与“SCOTT”从事相同工作的所有员工。

select t.* from scott.emp t where exists (select 1 from scott.emp a where a.ename='SCOTT' and t.job = a.job );

select * from scott.emp where job=(select job from scott.emp where ename='SCOTT');

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

　　select * from emp where sal in

　　(select sal from emp where deptno=30);

　　或

　　select * from emp where sal = any

　　(select sal from emp where deptno=30);



12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

select * from emp where sal>all

　　(select sal from emp where deptno=30);

　　--最小值<all

　　select * from emp where sal < all

　　(select sal from emp where deptno=30);



13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

select deptno,count(*),

　　trunc(avg(sal+nvl(comm,0))) avgsal,

　　to_char(to_date('0001-01-01','yyyy-mm-dd') + avg(sysdate-hiredate)

　　-366-31,'yy"年"mm"月"dd') avgday

　　from emp group by deptno;



14．列出所有员工的姓名、部门名称和工资。

select ename,dname,sal+nvl(comm,0) from emp,dept where emp.deptno=dept.deptno;

16．列出所有部门的详细信息和部门人数。

select a.*,(select count(*) from emp where deptno=a.deptno) tot

　　from dept a ;



17．列出各种工作的最低工资。

　select job,min(sal+nvl(comm,0)) from emp group by job;

18．列出MANAGER（经理）的最低薪金。

　select min(sal) from emp where job='MANAGER' ;

19．列出所有员工的年工资,按年薪从低到高排序。

select ename,(sal+nvl(comm,0))*12 tot from emp order by tot;

　　-----orcle的等连接

　　SELECT d.* FROM EMP E ,DEPT D WHERE E.DEPTNO=D.DEPTNO;

　　-----orcla的外连接

　　SELECT d.* FROM EMP E ,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;

　　+放在没有匹配行的表一侧，所以dept表的记录完全显示

　　--标准等联结

　　select dept.* from emp inner join dept

　　on emp.deptno = dept.deptno;

　　--标准的右外联结

　　select dept.* from emp right outer join dept

　　on emp.deptno = dept.deptno;

　　select dept.* from dept left outer join emp

　　on emp.deptno = dept.deptno;