sql 语句

oracle sql语句小结

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

 

一、数据表设计图


二、创建语句

alter table "emp"
   drop constraint FK_EMP_REFERENCE_DEPT;

drop table "dept" cascade constraints;

drop table "emp" cascade constraints;

drop table "salgrade" cascade constraints;




create table dept  (
   deptno             NUMBER(11)                         not null,
   dname              VARCHAR2(15)                    not null,
   loc                VARCHAR2(15)                    not null,
   constraint PK_DEPT primary key (deptno)
);




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)
);




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)
   )
 )
 

 
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