以下sql我都是亲测：大多数用法都会在面试当中被问到，切记一步一个脚印的去实现，结果不重要，重要的是你的实现过程的想法，第一步做什么然后第二步做什么等具体的详细过程！（学东西不能贪多，慢慢来）
先从单表查询开始——>多表查询->复杂查询->嵌套查询等等；
01.查询每个雇员的编号、姓名、职位。
select e.empno as "雇员编号",e.ename as "雇员姓名",e.job as "雇员职位" from emp e
02.查询每个雇员的职位，职位。
select e.job as "雇员职位" ,e.* from emp e;
03.查询每个雇员的职位，使用DISTINCT消除掉显示的重复行记录。
select DISTINCT e.job as "雇员职位",e.* from emp e;
04.计算出每个雇员的基本年薪，同时查询出雇员的编号、姓名。
select e.empno as "雇员编号",e.ename as "雇员姓名",(e.sal+IFNULL(comm,0)*12) as "基本年薪" from emp e
05.每个雇员每个月公司会补贴饭食200元，交通补助300元，计算年薪（年薪=(工资+奖金)*12）。
select e.empno as "雇员编号",e.ename as "雇员姓名",(e.sal+IFNULL(comm,0)+200+300)*12 as "基本年薪" from emp e;
06.查询基本工资高于2000的全部雇员信息。
select e.* from emp e where e.sal>2000;
07.查询出smith的信息。
select e.* from emp e where e.ename="SMITH";
08.查询出所有不是CLERK的详细信息。
select e.* from emp e where e.job != "CLERK";
select e.* from emp e where e.job <> "CLERK";
select e.* from emp e WHERE e.job not in("CLERK");
09.查询出所有销售人员(SALESMAN)的基本信息，并且要求销售人员的工资高于1300。
select e.* from emp e where e.job = "SALESMAN" && e.sal>1300;
select e.* from emp e where e.job in ("SALESMAN") and e.sal>1300;
10.查询出工资范围在1500~3000之间的全部雇员信息（包含1500和3000）。
select e.* from emp e where e.sal>=1500 and e.sal<= 3000;
select e.* from emp e where e.sal BETWEEN 1500 and 3000;
11.查询出所有经理或者是销售人员的信息，并且要求这些人的基本工资高于1500
select e.* from emp e WHERE (e.job='SALESMAN' or e.job='manager') and e.sal>1500;
12.要求查询出所有在1981年雇佣的雇员信息。
select * from emp e where e.HIREDATE between('1981-1-01') and('1981-12-31');
select * from emp e where e.hiredate >= '1981-1-01' and e.hiredate <= '1981-12-31';
13.查询所有领取奖金的雇员信息（comm不为空）
select * from emp e where e.comm is not null;
select * from emp e where e.comm="";
14.查询所有领取奖金高于100的雇员信息
select e.* from emp e WHERE e.comm > 100;
15.查询出雇员编号是7369、7566、9999的雇员信息。
select e.* from emp e where e.empno in (7369,7566,9999);
16.查询出所有雇员姓名是以A开头的全部雇员信息。
select * from emp e where e.ename like "A%";
17.查询出雇员姓名第二个字母是M的全部雇员信息。
select * from emp e where SUBSTR(e.ename,2,1)='m';
select * from emp e where e.ename like "%_m";
18.查询出雇员姓名任意位置上包含字母A的全部雇员信息。
select * from emp e WHERE e.ename like "%a%";
select * from emp e where regexp_like(first_name,'[,a]+');（正则表达式）
19.查询出所有雇员的信息，要求按照工资排序。
select * from emp e order by e.sal asc;（默认是asc,是升序）
select * from emp e order by e.sal desc;（desc,是降序）
20.要求查询所有雇员的信息，按照雇佣日期由先后排序。
select * from emp e order by e.HIREDATE asc;
select * from emp e order by e.HIREDATE
21.查询全部雇员信息，按照工资由高到低排序，如果工资相同，则按照雇佣日期由先后排序。
select * from emp e order by e.sal desc,e.hiredate asc;
22.查询部门30中的所有员工。
select * from emp e where e.deptno = 30;
23.查询出所有办事员（CLERK）的姓名，编号和部门编号。
select * from emp e where e.job = "CLERK";
24.查询出奖金高于薪金的员工。
select * from emp e where e.comm > e.sal;
25.查询出奖金高于薪金的60%的员工。
select * from emp e where e.comm > 0.6*e.sal
26.查询出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。
(select * from emp e where e.job='manager' and e.DEPTNO=10 )union all(SELECT * from emp b where b.job='clerk' and b.DEPTNO=20);
select * from emp e where e.deptno = 10 and e.job = 'Manager' union select * from emp e where e.deptno = 20 and e.job = 'clerk';
27.查询出部门10中所有经理，部门20中所有办事员，既不是经理又不是办事员但其薪金大于或等于2000的所有员工的信息。
(select * from emp e where e.job!='manager' and e.DEPTNO=10 and e.SAL>=2000 and e.job != 'clerk' )union (select * from emp b where b.job!='clerk' and b.job!= 'manager'and b.DEPTNO=20 and b.sal>=2000);
28.查询出收取奖金的员工的不同工作。
select e.job,e.* from emp e where e.comm !="";
select e.job,e.* from emp e where e.comm is not null;
29.查询出不收取奖金或收取的奖金低于100的员工。
30.查询出不带有“R”的员工的姓名。
select * from emp e where e.ename not like "%R%";
select * from emp e where not regexp_like(first_name,'[,r]+');
31.查询出每个雇员的姓名、职位、领导姓名。
select a.ename "雇员的姓名", a.job "雇员的职位", b.ename "雇员领导姓名" FROM emp a,emp b where a.mgr = b.empno;
32.查询出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
select a.empno as "员工编号",a.ename as "员工姓名" , b.empno"上司编号",b.ename as "上司姓名", (B.SAL+IFNULL(B.COMM,0))12 "领导年工资"
from emp a,emp b
where a.mgr= b.empno
orderby by (b.sal+IFNULL(b.comm,0))12 desc;
33.查询出在销售部（SALES）工作的员工姓名、基本工资、雇佣日期、部门名称。（不知道销售部编号）。
select e.ename as "员工姓名",e.empno as "员工编号",e.sal as "员工薪资",e.hiredate as "雇佣日期",d.dname as "部门名称" from emp e,dept d where e.job="salesman" and e.deptno = d.deptno;
34.查询出所有员工的姓名、部门名称和工资。
select emp.ename as "雇员姓名",dept.dname as "雇员职位",emp.sal as "雇员薪资" from emp,dept WHERE emp.deptno = dept.deptno;
35.查询出所有员工的年工资，所在部门名称，按年薪从低到高排序。
select (e.sal+IFNULL(e.comm,0))12 as allYearSales,d.dname as "部门名称",e. from emp e,dept d where e.deptno = d.deptno order by allYearSales desc;
36.查询出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000。
select distinct
e2.ename,d.dname
from emp e1,emp e2,dept d
where e1.mgr = e2.empno && e2.deptno = d.deptno && e2.sal > 300;
37.查询出公司的最高和最低工资。
select MAX(e.sal) as "最高工资",MIN(e.sal) as "最低工资" from emp e;
38.查询出每个部门的人数、平均工资，只显示部门编号。
select deptno as "部门编号",count(e.deptno) as "部门员工数量",avg(e.sal) as "平均薪资" from emp e group by e.deptno;
39.查询出每种职位的最高和最低工资。
SELECT job as "职位", MAX(sal) as "最高薪资",MIN(sal) as "最低薪资" FROM emp group by job;
40.查询平均工资高于2000的职位信息，以及从事此职位的雇员人数、平均工资。
select job as "职业",count(job) as "从事的职业数量",AVG(sal) as "平均薪资" from emp group by job having AVG(sal)>2000;
41查询出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
select
e.deptno as "部门编号", d.dname as "部门名称", AVG(sal) as "部门平均薪资",MIN(sal) as "最低薪资",MAX(sal) as "最高薪资"
from
emp e,dept d
where
e.deptno = d.deptno
group by
d.dname;
42.查询出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。
select p.deptno as "部门编号",p.dname as "部门名称",p.loc as "部门地址",count(job)as "部门人数",IFNULL(AVG(e.sal),0) as "部门平均工资" from emp e
right join dept p
on e.deptno = p.deptno
group by p.dname; （右连接）