oracle练习4

1． 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。(经典的两个表连接)
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname=’SALES’;

2． 列出薪金高于公司平均薪金的所有员工。(反复查自己)
select ename,sal from emp where sal>(select avg(sal) from emp);

3． 列出与“SCOTT”从事相同工作的所有员工。(排除自己)
select ename,job from emp where ename!=’SCOTT’ and job=(select job from emp where ename=’SCOTT’);

4． 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。(max的用法)
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);

5． 列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno as “部门编号”,count(empno) as “员工数量”,round(avg(sal),2) “平均工资”,trunc(avg(months_between(sysdate,hiredate))) as “服务期限/月” from emp group by deptno;

6． 列出所有员工的姓名、部门名称和工资.
select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno;

7． 列出所有部门的详细信息和部门人数。
select d.deptno,d.dname,d.loc,count(e.empno) as “部门人数” from dept d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname,d.loc;

8． 找出所有的普通员工
select * from emp where empno not in (Select mgr from emp where mgr is not null);

9． 求部门中哪些人的薪水最高
select ename,sal from emp where sal in(select max(sal) from emp group by deptno);

10． 求部门平均薪水的等级
select t.name as “部门名称”,round(t.avgsal,2) as “部门平均薪水”,s.grade as “等级”
from (select avg(e.sal) as avgsal,d.dname as name from emp e,dept d where e.deptno=d.deptno group by d.dname) t,salgrade s
where round(t.avgsal,2) between losal and hisal;

11． 雇员中哪些人是经理人
select ename from emp where empno in (select distinct mgr from emp);

12． 不准用组函数，求薪水的最高值
select ename,sal from (select * from emp order by sal desc) where rownum=1;

13． 求平均薪水最高的部门的部门编号
select t.deptno as “部门编号”,t.sal as “部门最高平均薪水” from
(select d.deptno as deptno ,round(avg(e.sal),2) as sal
from dept d,emp e
where d.deptno=e.deptno
group by d.deptno order by sal desc) t
where rownum=1;

14． 求平均薪水最高的部门的部门名字
select t.dname as “部门名称”,t.sal as “部门最高平均薪水” from
(select d.dname as dname,round(avg(e.sal),2) as sal
from dept d,emp e
where d.deptno=e.deptno
group by d.dname order by sal desc) t
where rownum=1;

15． 求比普通员工的最高薪水还要高的经理人名称
select ename,sal from emp where sal>(select max(sal) from emp where job not in (‘MANAGER’,’PRESIDENT’,’ANALYST’)) and
job in (‘MANAGER’,’PRESIDENT’,’ANALYST’);