lunzi

/*==============================================================*/
/* DBMS name:      ORACLE Version 9i                            */
/* Created on:     2008-11-10 23:39:24                          */
/*==============================================================*/


alter table "emp"
   drop constraint FK_EMP_REFERENCE_DEPT;

drop table "dept" cascade constraints;

drop table "emp" cascade constraints;

drop table "salgrade" cascade constraints;

/*==============================================================*/
/* Table: "dept"                                                */
/*==============================================================*/
create table dept  (
   deptno             NUMBER(11)                         not null,
   dname              VARCHAR2(15)                    not null,
   loc                VARCHAR2(15)                    not null,
   constraint PK_DEPT primary key (deptno)
);

/*==============================================================*/
/* Table: "emp"                                                 */
/*==============================================================*/
create table emp  (
   empno              NUMBER(11)                         not null,
   deptno             NUMBER(11),
   ename              VARCHAR2(15)                    not null,
   sal                NUMBER(11)                         not null,
   job                VARCHAR2(15)                    not null,
   mgr                NUMBER(11)                         not null,
   hirdate            DATE                            not null,
   comm               NUMBER(11)                         not null,
   constraint PK_EMP primary key (empno)
);

/*==============================================================*/
/* Table: salgrade                                           */
/*==============================================================*/
create table salgrade  (
   grade              NUMBER(11)                         not null,
   losal              NUMBER(11)                         not null,
   hisal              NUMBER(11)                         not null,
   constraint PK_SALGRADE primary key (grade)
);

alter table emp
   add constraint FK_EMP_REFERENCE_DEPT foreign key (deptno)
      references dept (deptno);

三、测试要求及语句

/**
*公司工资最高的员工列表 子查询
*/
select t.ename,t.sal from emp t where t.sal =
 (
  select max(sal) from emp 
 )
 
/**
*查询每一个员工的经理人及自己的名字
*/ 
select e1.ename,e2.ename  from emp e1 join emp e2 on (e1.mgr = e2.empno)

/**
*查询公司平均薪水的等级
*/
select s.grade from  salgrade s where
(select avg(t.sal) from emp t) between s.losal and s.hisal

/**
*求部门中那些人的工资最高
*/
select d.dname,ename,sal from 
 (select t.deptno,ename,sal from 
   (select deptno,max(sal) as max_sal from emp group by deptno) e
   join emp t on (e.deptno = t.deptno and t.sal = max_sal)
 ) et
 join dept d on (d.deptno = et.deptno)  

/**
*查询部门平均薪水的等级
*/
select d.dname,avg_sal,grade from 
 (select  deptno,avg_sal,grade from 
  (select deptno,avg(sal) as avg_sal from emp group by deptno) e 
    join salgrade s on (e.avg_sal between s.losal and s.hisal )
 ) es
 join dept d on (es.deptno = d.deptno)

/**
*求部门的平均薪水等级
*/ 
 
  select deptno,avg(grade) from 
    (select deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) t
  group by t.deptno
  
/**
* 求那些人是经理人
*/  
  select ename from emp e
   where empno in (select distinct mgr from emp) 

/**
*不准用组函数 求薪水的最高值
*/  
select ename from emp
where empno not in
 (select distinct e1.empno from emp e1
  join emp e2 on (e1.sal<e2.sal) 
 ) 

 /**
 *平均薪水最高的部门编号与名称
 */
 select d.deptno,dname  from
   (select deptno,avg(sal) avg_sal  from emp group by deptno) t1 
   join dept d on (d.deptno = t1.deptno)
 where avg_sal = 
  (select max(avg_sal) from
   (select deptno,avg(sal) avg_sal  from emp group by deptno) t2 
  )
 
 /**
 *求平均薪水的等级最低的部门名称
 */
 select dname  from dept d
 where d.deptno in (  
   select deptno from 
   ( 
     select deptno,grade from
       (select deptno,avg(sal) avg_sal  from emp group by deptno) t1
        join salgrade g on (avg_sal between g.losal and g.hisal)
   ) t2
   where t2.grade =
   (
     select min(grade)  from
     ( 
       select deptno,grade from
         (select deptno,avg(sal) avg_sal  from emp group by deptno) t1
          join salgrade g on (avg_sal between g.losal and g.hisal)
     ) t3
   )
 )
 
 /**
 *求部门经理人中平均薪水最低的部门名称
 */
 select d.dname,t1.avg_sal from dept d
  join 
  (
   select deptno,avg(sal) avg_sal from 
     (
       select e2.deptno,e2.ename,e2.sal from emp e1
        join emp e2 on (e1.mgr = e2.empno)
     ) t
   group by deptno
  ) t1
   on (d.deptno = t1.deptno)
 where avg_sal =
 (
   select min(avg_sal) from 
   (
     select deptno,avg(sal) avg_sal from 
     (
       select e2.deptno,e2.ename,e2.sal from emp e1
        join emp e2 on (e1.mgr = e2.empno)
     ) t
     group by deptno
   ) 
 )
 /**
 *求必普通员工的最高薪水还要高的经理人名称
 */
 select ename from
 (
   select e2.ename,e2.empno,e2.sal from emp e1
    join emp e2 on (e1.mgr = e2.empno)
 ) t
 where t.sal >
 (
   select max(e.sal) from emp e
   where e.empno not in
   ( 
     select e1.mgr from emp e1
      join emp e2 on (e1.mgr = e2.empno)
   )
 )
 

 /**
 *求薪水最高的第6名到10名雇员
 */
SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM (select e1.ename,e1.sal from emp e1 order by e1.sal desc)) A 
WHERE ROWNUM <= 10
)
WHERE RN >= 6