共计 5304 个字符，预计需要花费 14 分钟才能阅读完成。
这里涉及聚合函数，分析函数，条件函数，以及自身映射。
一共有以下几张表（EMP，Dept）以及表的结构和下面的 36 条基础题。
下面我会用 * 号特定展示，那些比较重要，优先处理。
1.1 列出至少有两个员工的所有部门。
SELECT DEPTNO,COUNT(1)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(1) >= 2;
1.2 查询工资大于或者等于 2000 的员工信息
SELECT * FROM EMP WHERE SAL >= 2000;
1.3 查询至少有两个员工的工资不低于 2000 的部门编号
SELECT DEPTNO,COUNT(1)
FROM EMP
WHERE SAL >= 2000
GROUP BY DEPTNO
HAVING COUNT(1) >= 2;
2.1 列出薪金比“SMITH”多的所有员工。
SELECT * FROM EMP WHERE SAL >(SELECT SAL FROM EMP WHERE ENAME = 'SMITH');
2.2 查询工资 与 SCOTT 相同的员工信息
SELECT * FROM EMP WHERE SAL = (SELECT ENAME FROM EMP WHERE ENAME = 'SCOTT');
2.3 查询工资 与 SCOTT 相同的员工信息, 并且不返回 SCOTT 自己的信息
SELECT * FROM EMP WHERE SAL = (SELECT ENAME FROM EMP WHERE ENAME = 'SCOTT') AND ENAME <> 'SCOTT';
3．* 列出所有员工的姓名及其直接上级的姓名。
SELECT A.ENAME,B.ENAME
FROM EMP A
LEFT JOIN EMP B
ON A.MGR = B.EMPNO;PS : 自表映射（关联）
关键在于由谁作为连接字段去映射自表，谁在前谁在后。
因为 EMPNO 和 MGR 都属于员工编号，而且多个员工对应一个 MGR(经理)
4．列出受雇日期早于其直接上级的所有员工。
SELECT A.*
FROM EMP A,EMP B
WHERE A.MGR = B.EMPNO
AND A.HIREDATE < B.HIREDATE;PS：这是第三题的拓展，增加了一条筛选条件。
日期比较早的，则表示受雇日期越大的，就离现在越近。
5．* 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT A.DNAME,E.*
FROM DEPT A LEFT JOIN EMP E
ON A.DEPTNO = E.DEPTNO;PS：谁为主表的问题，看清宾语 。
要的是部门名称和这个员工信息，同时要列出没有部门的员工。
第一种情况，假如以 EMP 为主表，则 EMP 表上所有的员工都会返回，只匹配到员工表上所有信息，而如果还有一些员工没在部门内的，则全不会返回。
第二种情况，假如以 Dept 为主表，则 Dept 表上所有的部门都会返回，只匹配到的员工则会有信息，没有的则不返回。
1）第一种情况：
第二种情况：
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
SELECT E.ENAME,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.JOB = 'CLERK';
7．列出最低薪金大于 1500 的各种工作。
SELECT JOB,MIN(SAL)
FROM EMP
GROUP BY JOB
HAVING MIN(SAL) > 1500;
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
SELECT ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');
9．列出薪金高于公司平均薪金的所有员工。
SELECT * FROM EMP WHERE SAL >(SELECT AVG(SAL) FROM EMP);
10．列出与“SCOTT”从事相同工作的所有员工。
SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SCOTT');
11．列出薪金等于部门 30 中员工的薪金的非 30 号部门的员工的姓名和薪金。
SELECT ENAME,SAL FROM EMP WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO=30) AND DEPTNO!=30;
12．列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。
SELECT A.ENAME,A.SAL FROM EMP A WHERE A.SAL>(SELECT MAX(B.SAL) FROM EMP B WHERE B.DEPTNO=30);
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT DEPTNO,COUNT(1),AVG(SAL),AVG(SYSDATE - HIREDATE) FROM EMP GROUP BY DEPTNO;
PS：服务期限就用自带函数 sysdate 与入职时间相减，最后再求平均。
14．列出所有员工的姓名、部门名称和工资。
SELECT E.ENAME, D.DNAME, E.SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;PS : 所有的员工，所有的员工只存在 EMP 表里。因此，EMP 表作为主表。
15．* 列出所有部门的详细信息和部门人数。
SELECT D.DEPTNO, D.DNAME, D.LOC, CT.CT1
FROM DEPT D,
(SELECT DEPTNO, COUNT(DEPTNO) CT1 FROM EMP GROUP BY DEPTNO) CT
WHERE D.DEPTNO = CT.DEPTNO(+);先看定语，所有部门的详细信息，这那张表最完整，只有在 Dept 表里有。
然后再 Left Join 作为两张表的链接，在 Where 后面会添加（+）则代表谁为主表。
按照传统的写法，则如下：
SELECT D.DEPTNO, D.DNAME, COUNT(E.DEPTNO)
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO(+)
GROUP BY D.DEPTNO, D.DNAME;
16．列出各种工作的最低工资。
SELECT E1.JOB,MIN(E1.SAL) FROM EMP E1 GROUP BY E1.JOB
17．列出各个部门的 MANAGER（经理）的最低薪金。
SELECT deptno, MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno;
18．列出所有员工的年工资, 按年薪从低到高排序。
SELECT (SAL+NVL(COMM,0))*12 年薪 FROM EMP ORDER BY 年薪;
19. 查出 emp 表中薪水在 3000 以上（包括 3000）的所有员工的员工号、姓名、薪水。
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=3000;
20. 查询出所有薪水在’ALLEN’之上的所有人员信息。
SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='ALLEN');
21. 查询出 emp 表中部门编号为 20，薪水在 2000 以上（不包括 2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
SELECT EMPNO AS 员工编号, ENAME AS 员工名字,SAL AS 薪水 FROM EMP E WHERE DEPTNO=20 AND SAL>2000;
22. 查询出 emp 表中所有的工作种类（无重复）
SELECT DISTINCT JOB FROM EMP;
SELECT JOB FROM EMP GROUP BY JOB;
23. 查询出所有奖金（comm）字段不为空的人员的所有信息。
SELECT * FROM EMP WHERE COMM IS NOT NULL;
24. 查询出薪水在 800 到 2500 之间（闭区间）所有员工的信息。（注：使用两种方式实现 and 以及 between and）
SELECT * FROM EMP WHERE SAL>=800 AND SAL<=2500;
SELECT * FROM EMP WHERE SAL BETWEEN 800 AND 2500;
25. 查询出员工号为 7521，7900，7782 的所有员工的信息。（注：使用两种方式实现，or 以及 in）
SELECT * FROM EMP WHERE EMPNO IN (7521,7900,7782);
SELECT * FROM EMP WHERE EMPNO=7521 OR EMPNO=7900 OR EMPNO=7782;
26. 查询出名字中有“A”字符，并且薪水在 1000 以上（不包括 1000）的所有员工信息。
PS：INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符, 返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置, 默认为 1
J 出现的位置, 默认为 1
SQL> select instr(“abcde”,’b’); 结果是 2，即在字符串“abcde”里面，字符串“b”出现在第 2 个位置。如果没有找到，则返回 0；不可能返回负数
SELECT * FROM EMP WHERE ENAME LIKE '%A%' AND SAL > 1000;
SELECT * FROM EMP WHERE INSTR(ENAME,'A',1,1) <> 0 AND SAL > 1000;
27. 查询出名字第三个字母是“M”的所有员工信息。
SELECT * FROM EMP WHERE ENAME LIKE '__M%';
SELECT * FROM EMP WHERE INSTR(ENAME,'M',3,1) = 3;
28. 将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
PS：两个先后排序，用逗号隔开。
SELECT * FROM EMP A ORDER BY A.SAL ASC,A.HIREDATE DESC;
29. 将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。
PS：Substr 函数
1、作用：用来截取数据库某个字段中的一部分。
2、语法：substr(string,start,length)
string 参数：必选。数据库中需要截取的字段。
start 参数：必选。
正数，从字符串指定位子开始截取；负数，从字符串结尾指定位子开始截取；0，在字符串中第一个位子开始截取。1，同理。(特殊)。length 参数：可选。需要截取的长度。缺省，即截取到结束位置。
SELECT * FROM EMP ORDER BY SUBSTR(ENAME,1,1),SAL DESC;
30. 查询出最早工作的那个人的名字、入职时间和薪水。
SELECT ENAME,HIREDATE,SAL FROM EMP WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP);
31*. 显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示 100.
PS : 一 NVL 函数是一个空值转换函数
NVL（表达式 1，表达式 2）如果表达式 1 为空值，NVL 返回值为表达式 2 的值，否则返回表达式 1 的值。该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式 1 和表达式 2 的数据类型必须为同一个类型。
对数字型 NVL（comm,0);
对字符型 NVL(TO_CHAR(comm), ‘No Commission’)
对日期型 NVL（hiredate,’ 31-DEC-99′)
SELECT
E.ENAME,
E.SAL,
NVL(E.COMM,100)
FROM EMP E;
32. 显示出薪水最高人的职位。
SELECT E.JOB FROM EMP E WHERE SAL=(SELECT MAX(SAL) FROM EMP);
33. 查出 emp 表中所有部门的最高薪水和最低薪水，部门编号为 10 的部门不显示。
SELECT DEPTNO,MAX(SAL),MIN(SAL)
FROM EMP
WHERE DEPTNO <> 10
GROUP BY DEPTNO;
34. 删除 10 号部门薪水最高的员工。
DELETE FROM EMP WHERE EMPNO IN (SELECT EMPNO FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 10) AND DEPTNO = 10) ;
DELETE FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 10) AND DEPTNO = 10;
DELETE FROM EMP WHERE DEPTNO = 10 AND SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 10);35. 将薪水最高的员工的薪水降 30%。
注意：所有的更改后面记得加上 Where，不然整张表都更新。
第二，任何的更新操作后面要加上 Commit;
UPDATE EMP SET e.SAL=e.sal*0.7 WHERE SAL=(SELECT MAX(E.SAL) FROM EMP E); 36. 查询员工姓名，工资和 工资级别(工资 >=3000 为 3 级，工资 >2000 为 2 级，工资 <=2000 为 1 级)
注意：每个条件判断条件后面不用写 ’,’ 号.
SELECT E.ENAME,E.SAL,CASE WHEN (E.SAL)>=3000 THEN '3 级'
WHEN (E.SAL)>2000 THEN '2 级'
ELSE '1 级'
END
FROM EMP E;
