Oracle例题（一）

备注：以下例题均基于Oracle的scott账户中的表

1、插入一条信息至SCOTT的EMP表

insert into emp values(7777,'AABB','PRESIDENT',7999, to_date('2017-11-1','YYYY-MM-DD'),10000,null,30);

2、查询首字母为"A"或者第二个字母为"A"的所有员工信息

select * from emp where ename like 'A%' or ename like '_A%';

select * from emp where substr(ename,1,1)='A' or substr(ename,2,1)='A';

select * from emp where instr(ename,'A')=1 or instr(ename,'A')=2;

3、查询部门20和30中的，岗位不是"CLERK"或"SALESMAN"的员工信息

select * from emp where job!='CLERK' and job!='SALESMAN' and (deptno = 20 or deptno = 30);//不好

select * from emp where job!='CLERK' and job!='SALESMAN' and deptno in(20,30);

4、查询出工资在2500-3500之间，1981年入职的没有奖金的员工的所有信息

select *
  from emp
 where sal between 2500 and 3500
   and to_char(hiredate, 'yyyy-mm-dd') like '1981%'
   and comm is null;

select *
  from emp
 where sal between 2500 and 3500
   and comm is null
   and extract(year from hiredate) = '1981';

5、查找比平均工资高的员工信息

select * from emp where sal > (select avg(sal) from emp);

6、查询平均工资高于2000的部门信息

select *
  from dept
 where deptno in
       (select deptno from emp group by deptno having avg(sal) > 2000);

7、查询出WARD的工作所在地

select loc
  from dept
 where deptno = (select deptno from emp where ename = 'WARD');

select dept.loc
  from dept, emp
 where dept.deptno = emp.deptno
   and emp.ename = 'WARD';

8、查询出工资比ADAMS高的所有人姓名、部门、所在地

select emp.ename, dept.dname, dept.loc
  from emp, dept
 where emp.deptno = dept.deptno
   and sal > (select sal from emp where ename = 'ADAMS');

9、查询工资排名第7的员工信息

select *
  from emp
 where sal = (select min(sal)
                from (select sal from emp order by sal DESC)
               where rownum <= 7);