SQL基础入门36题-题目及解题思路

这里涉及聚合函数，分析函数，条件函数，以及自身映射。
一共有以下几张表（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;

SQL基础入门36题-题目及解题思路

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）