備注：以下例題均基于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"或者第二個(gè)字母為"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年入職的沒(méi)有獎(jiǎng)金的員工的所有信息
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);
