备注：以下例题均基于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);
