【安博培训笔记】Oracle3 操作符及SQL函数 作业20130910

Oracle3 操作符及SQL函数 作业
一、使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下：
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
工资 ＝ 薪金 ＋ 佣金
1. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
select count(empno) 员工数量,avg(sal + nvl(comm,0)) 平均工资,avg(sysdate-hiredate) 平均服务期限 
from emp group by deptno;
2. 列出所有员工的姓名、部门名称和工资。 
select emp.ename,dept.dname,emp.sal+nvl(emp.comm,0) 工资 
from emp,dept where emp.deptno = dept.deptno;
select ename,nvl(dname,'无部门') 部门名称,sal+nvl(comm,0) 工资 
       from emp left join dept on emp.deptno = dept.deptno;
@3. 列出从事同一种工作但属于不同部门的员工的一种组合。
select t1.ename,t2.ename from emp t1,emp t2 
       where t1.job = t2.job and t1.deptno != t2.deptno;
4. 列出所有部门的详细信息和部门人数。 [*]
select dept.deptno,dname,loc,count(ename) 人数
       from emp,dept where emp.deptno = dept.deptno
       group by dept.deptno,dname,loc;
select d1.deptno,d1.dname,d1.loc,d2.c 部门人数 
       from dept d1 left join 
       (select count(*) c,deptno dn from emp group by deptno)
       d2 on d1.deptno = d2.dn;
5. 列出各种工作的最低工资。
select job,min(sal+nvl(comm,0)) 最低工资 from emp group by job;
6. 列出各个部门的MANAGER（经理）的最低薪金。
select dept.deptno,dname,job,ename,min(sal+nvl(comm,0))
        from emp,dept where emp.deptno = dept.deptno and job = 'MANAGER'
        group by dept.deptno,dname,job,ename;
select dname,min(sal) from emp join dept on emp.deptno = dept.deptno 
       where job = 'MANAGER' group by dname;
7. 列出所有员工的年工资,按年薪从低到高排序。
select ename,(sal + nvl(comm,0))*12 年薪 from emp order by 年薪; 
@8. 找出月薪相同的员工
select t1.ename,t2.ename,t1.sal,t2.sal from emp t1,emp t2 
       where t1.sal = t2.sal;
select * from emp where sal+nvl(comm,0) in 
       (select sal+nvl(comm,0) from emp group by sal+nvl(comm,0) 
       having count(sal+nvl(comm,0))>1) order by sal+nvl(comm,0);   
@9. 列出至少有一个员工的所有部门。
select count(*),deptno from emp 
group by deptno having count(*) > 1;
select * from dept where exists 
       (select * from emp where deptno = dept.deptno);
10. 列出薪金比“SMITH”多的所有员工。
select * from emp where (sal+nvl(comm,0))>
       (select sal+nvl(comm,0) from emp where ename = 'SMITH');
select * from emp where sal > 
(select sal from emp where ename = 'SMITH');
11. 列出所有员工的姓名及其直接上级的姓名。
select t1.ename 员工的姓名,t2.ename 直接上级的姓名 
       from emp t1,emp t2 where t1.mgr = t2.empno;
12. 列出受雇日期早于其直接上级的所有员工。
select t1.ename 员工的姓名,t1.hiredate,t2.ename 直接上级的姓名,t2.hiredate 
       from emp t1,emp t2 where t1.mgr = t2.empno and t1.hiredate < t2.hiredate;
13. 列出所有“CLERK”（办事员）的姓名及其部门名称。
select ename,dname from emp,dept
       where emp.deptno = dept.deptno and job = 'CLERK';  
14. 列出最低薪金大于1500的各种工作。
select job,min(sal) 最低薪金 from emp 
       group by job having min(sal) > 1500;
15. 列出在部门“SALES”（销售部）工作的员工的姓名， 假定不知道销售部的部门编号。 
select ename from emp where deptno = 
       (select deptno from dept where dname = 'SALES');
16. 列出薪金高于公司平均薪金的所有员工。
select * from emp where sal >
       (select avg(sal) from emp);
17. 列出与“SCOTT”从事相同工作的所有员工。
select * from emp where job = 
       (select job from emp where ename = 'SCOTT');    
18. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename,sal from emp where sal in 
       (select sal from emp where deptno = 30);   
19. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal > 
       (select max(sal) from emp where deptno = 30);
@20. 列出比“SCOTT”大两个月以上的员工姓名 
select t1.ename from emp t1 join emp t2 on t2.ename = 'SCOTT' 
       and months_between(t2.hiredate,t1.hiredate) >= 2;
select t1.ename,t1.hiredate,t2.ename,t2.hiredate from emp t1 join emp t2 on t2.ename = 'SCOTT' 
       and months_between(t2.hiredate,t1.hiredate) >= 2;