Oracle数据库练习：基于SCOTT/TIGER用户的EMP与DEPT表操作

http://apps.hi.baidu.com/share/detail/18710844

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

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

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

工资 ＝ 薪金 ＋ 佣金 

1．列出至少有一个员工的所有部门。
select * from dept d where d.deptno in (select deptno from emp group by deptno having count(deptno)>1) 
2．列出薪金比“SMITH”多的所有员工。 
select * from emp where sal > (select sal from emp where ename ='SMITH')
3．列出所有员工的姓名及其直接上级的姓名。
select e.empno,e.ename,(select ename from emp where empno=e.mgr) from emp e
4．列出受雇日期早于其直接上级的所有员工。
select e.ename,e.hiredate,(select ename from emp where empno=e.mgr) 领导,(select hiredate from emp where empno=e.mgr) 领导入职时间 from emp e where e.hiredate<(select hiredate from emp where empno=e.mgr)
5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.deptno,d.dname,e.empno,e.ename,e.job from emp e,dept d where d.deptno= e.deptno(+)
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
select e.ename,e.job,d.dname from emp e, dept d where e.job='CLERK' and e.deptno=d.deptno 
7．列出最低薪金大于1500的各种工作。 
select job from emp where sal>1500 group by job
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 
select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno and d.dname ='SALES'
9．列出薪金高于公司平均薪金的所有员工。
select e.ename  from emp e where sal >(select avg(sal) from emp) 
10．列出与“SCOTT”从事相同工作的所有员工。 
select * from emp where job = (select job from emp where ename = 'SCOTT')

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
 select ename,sal from emp where sal in (select sal from emp where deptno =30)
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
select ename,sal from emp where sal >(select max(sal) from emp where deptno =30)
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
 select deptno, count(*) 员工数量,avg(sal) 平均工资,avg(sysdate -hiredate ) from emp group by deptno
14．列出所有员工的姓名、部门名称和工资。 
select e.ename,e.sal,(select d.dname from dept d where d.deptno =e.deptno) 部门 from emp e
15．列出所有部门的详细信息和部门人数。 
select d.dname,d.deptno,d.loc,(select count(e.deptno) from emp e where e.deptno=d.deptno) 人数 from dept d 
16．列出各种工作的最低工资。
 select  job, min(sal) from emp group by job
17．列出各个部门的MANAGER（经理）的最低薪金。
 select deptno,min(sal) from emp where job='MANAGER' GROUP by deptno
18．列出所有员工的年工资,按年薪从低到高排序。
select ename,sal*12+nvl(comm,0) 年工资 from emp order by 年工资 asc
19.列出经理人的名字.
SQL> select ename from emp where empno in(select distinct mgr from emp);
20.不用组函数,求出薪水的最大值(此题猥琐)





解答：

1．列出至少有一个员工的所有部门。(两个表联合查询，及group by...having的用法)
select dname from dept where deptno in(select deptno from emp group by deptno having count(*)>1);

2．列出薪金比“SMITH”多的所有员工。(经典的自连接查询)