备注：以下例题均基于Oracle的scott账户中的表
1、使用in查询部门编号为10,20的所有员工
select * from emp where deptno in(10,20);
2、使用like查询所有名字中包括w的员工信息
select * from emp where ename like '%W%';
3、使用like查询所有员工名字中第二个子字母为w的信息
select * from emp where ename like '_M%';
4、查询所有员工信息并按部门编号和工资进行排序
select * from emp order by deptno,sal ASC;
select * from emp order by 1,2 ASC;
5、显示员工工资上浮20%的结果
select ename, sal 原工资 , sal*1.2 上浮后的工资 from emp;
6、显示emp表的员工姓名以及工资和奖金的和
select ename, sal+nvl(comm,0) from emp;
7、显示dept表的内容，使用别名叫表头转换成中文显示
select deptno 部门编号, dname 部门名字, loc 部门地点 from dept;
8、查询员工姓名和工资，并按工资从小到大排序
select ename, sal from emp order by sal ASC;
9、查询员工姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示
select ename, hiredate from emp order by hiredate DESC;
10、查询员工信息，先按部门标号从小到大排序，再按雇佣时间的先后排序
select * from emp order by deptno,hiredate asc;
11、列出在部门SALES工作的员工姓名
select ename
from emp
where deptno in (select deptno from dept where dname = 'SALES');
select emp.ename
from emp, dept
where emp.deptno = dept.deptno
and dept.dname = 'SALES';
12、列出所有员工的姓名，部门和工资
select emp.ename, dept.dname, emp.sal
from emp, dept
where emp.deptno = dept.deptno;
13、列出所有部门的详细信息和部门人数
select dept.*, count(ename)
from emp, dept
where emp.deptno(+) = dept.deptno
group by dept.deptno, dept.dname, dept.loc;
14、列出各个部门职位为Manager的最低薪金
select deptno, min(sal) from emp where job = 'MANAGER' group by deptno;
15、查询部门人数至少是1的部门名字
select dept.dname
from emp, dept
where emp.deptno = dept.deptno
group by dept.dname
having count(ename) >= 1;
16、列出工资比SMITH多的员工
select ename
from emp
where sal > (select sal from emp where ename = 'SMITH');
17、列出所有员工的对应领导的姓名
select e1.ename, e2.ename 领导 from emp e1 left join emp e2 on e1.mgr = e2.empno;
18、求出某个员工的领导，并要求这些领导的薪水高于或等于3000
select distinct e.e2mgr
from (select e1.ename, e2.ename e2mgr, e2.sal e2sal
from emp e1
left join emp e2 on e1.mgr = e2.empno) e
where e.e2sal >= 3000
19、列出部门名称，和这些部门的员工信息
select dept.dname,
dept.deptno,
emp.empno,
emp.ename,
emp.job,
emp.mgr,
emp.hiredate,
emp.sal,
emp.comm
from dept
left join emp on emp.deptno = dept.deptno;
20、列出所有职位为CLERK的员工姓名及部门名称，部门人数
select ename, dname, enum, job
from emp
left join (select dept.deptno, dname, count(ename) as enum
from emp, dept
where emp.deptno = dept.deptno
group by dept.deptno, dname) a on emp.deptno = a.deptno
where job = 'CLERK';
