oracle sql語句小結(jié)

wind998899

一、數(shù)據(jù)表設(shè)計圖


二、創(chuàng)建語句

/*==============================================================*/
/* 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 
 )
 
/**
*查詢每一個員工的經(jīng)理人及自己的名字
*/ 
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
  
/**
* 求那些人是經(jīng)理人
*/  
  select ename from emp e
   where empno in (select distinct mgr from emp) 

/**
*不準(zhǔn)用組函數(shù) 求薪水的最高值
*/  
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
   )
 )
 
 /**
 *求部門經(jīng)理人中平均薪水最低的部門名稱
 */
 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
   ) 
 )
 /**
 *求必普通員工的最高薪水還要高的經(jīng)理人名稱
 */
 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

http://www.zhuoda.org/lunzi/98685.html