分组查询

第5章 分组查询

1. 列出至少有一个员工的所有部门。

SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(ename) > 0;

2. 查询 emp 表中每个部门的部门代码、薪水之和、平均薪水。

SELECT deptno, SUM(sal), AVG(sal) FROM emp GROUP BY deptno;

3. 查询 emp 表中部门人数大于等于 2 的部门的部门代码、部门人数。

SELECT deptno, COUNT(ename) FROM emp GROUP BY deptno HAVING COUNT(ename) >= 2;

4. 查询 emp 表中部门平均薪水小于等于 2000 的部门的部门代码、平均薪水，并按平均薪水从大到小排序。

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) <= 2000 ORDER BY AVG(sal) DESC;

5. 查询 emp 表中部门合计薪水大于等于 1000 的部门的部门代码、平均薪水，并按平均薪水从大到小排序。

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING SUM(sal) >= 1000 ORDER BY AVG(sal) DESC;

6. 查询 emp 表中薪水最少和薪水最大员工的姓名和薪水，并按薪水从大到小排序。

SELECT ename, sal FROM emp 
WHERE sal = (SELECT MIN(sal) FROM emp) OR sal = (SELECT MAX(sal) FROM emp) 
ORDER BY sal DESC;

7. 列出薪金比 SMITH 高的所有员工。

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

8. 列出所有员工的姓名及其直接上级领导的姓名。

SELECT e.ename AS 员工姓名, m.ename AS 上级姓名 
FROM emp e, emp m 
WHERE e.mgr = m.empno;

9. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称。

SELECT e.empno, e.ename, d.dname 
FROM emp e, emp m, dept d 
WHERE e.mgr = m.empno AND e.hiredate < m.hiredate AND e.deptno = d.deptno;

10. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT d.dname, e.* 
FROM dept d 
LEFT JOIN emp e ON d.deptno = e.deptno;

11. 列出所有 CLERK（办事员）的部门名称、部门人数。

SELECT d.dname, COUNT(e.ename) 
FROM emp e, dept d 
WHERE e.deptno = d.deptno AND e.job = 'CLERK' 
GROUP BY d.dname;

12. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数。

SELECT job, COUNT(ename) 
FROM emp 
GROUP BY job 
HAVING MIN(sal) > 1500;

13. 列出在部门 SALES（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

SELECT ename 
FROM emp 
WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');

14. 列出薪金高于公司平均薪金的所有员工、所在部门、上级领导、公司的等级工资。

SELECT e.*, d.dname, m.ename AS 上级姓名, s.grade 
FROM emp e, dept d, emp m, salgrade s 
WHERE e.deptno = d.deptno AND e.mgr = m.empno AND e.sal BETWEEN s.losal AND s.hisal 
AND e.sal > (SELECT AVG(sal) FROM emp);

15. 列出与 SCOTT 从事相同工作的所有员工的编号、姓名、职位及其部门名称。

SELECT e.empno, e.ename, e.job, d.dname 
FROM emp e, dept d 
WHERE e.deptno = d.deptno AND e.job = (SELECT job FROM emp WHERE ename = 'SCOTT') 
AND e.ename != 'SCOTT';

16. 列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金。

SELECT ename, sal 
FROM emp 
WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30);

17. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名、薪金和部门名称。

SELECT e.ename, e.sal, d.dname 
FROM emp e, dept d 
WHERE e.deptno = d.deptno AND e.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

18. 列出在每个部门工作的员工数量、平均工资和平均服务期限。

SELECT deptno, COUNT(ename), AVG(sal), ROUND(AVG(SYSDATE - hiredate)) AS 平均服务天数 
FROM emp 
GROUP BY deptno;

19. 列出所有员工的姓名、部门名称和工资。

SELECT e.ename, d.dname, e.sal 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

20. 列出所有部门的详细信息和部门人数。

SELECT d.*, COUNT(e.ename) 
FROM dept d 
LEFT JOIN emp e ON d.deptno = e.deptno 
GROUP BY d.deptno, d.dname, d.loc;

21. 列出各种工作的最低工资及从事此工作的雇员姓名。

SELECT e.ename, e.sal 
FROM emp e 
WHERE e.sal IN (SELECT MIN(sal) FROM emp GROUP BY job);

22. 列出各个部门的 MANAGER（经理）的最低薪金。

SELECT deptno, MIN(sal) 
FROM emp 
WHERE job = 'MANAGER' 
GROUP BY deptno;

23. 列出员工的年工资，按年薪从低到高排序。

SELECT ename, sal * 12 AS 年薪 
FROM emp 
ORDER BY 年薪 ASC;

24. 查出员工的信息，并要求这些员工的主管薪水超过 3000。

SELECT e.* 
FROM emp e, emp m 
WHERE e.mgr = m.empno AND m.sal > 3000;

25. 求出部门名称中带 S 字符的部门员工的工资合计、部门人数。

SELECT d.dname, SUM(e.sal), COUNT(e.ename) 
FROM emp e, dept d 
WHERE e.deptno = d.deptno AND d.dname LIKE '%S%' 
GROUP BY d.dname;

26. 查询公司员工工资的最大值、最小值、平均值、总和。

SELECT MAX(sal), MIN(sal), ROUND(AVG(sal)), SUM(sal) FROM emp;

27. 查询各 job 的员工工资的最大值、最小值、平均值、总和。

SELECT job, MAX(sal), MIN(sal), ROUND(AVG(sal)), SUM(sal) 
FROM emp 
GROUP BY job;

28. 选择具有各个 job 的员工人数（提示：对 job 进行分组）。

SELECT job, COUNT(ename) 
FROM emp 
GROUP BY job;

29. 查询员工最高工资和最低工资的差距。

SELECT MAX(sal) - MIN(sal) FROM emp;

30. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 800，没有管理者的员工不计算在内。

SELECT m.ename AS 管理者, MIN(e.sal) AS 最低工资 
FROM emp e, emp m 
WHERE e.mgr = m.empno AND e.sal >= 800 
GROUP BY m.ename;

31. 查询所有部门的名字 dname、所在位置 loc、员工数量和工资平均值。

SELECT d.dname, d.loc, COUNT(e.ename), AVG(e.sal) 
FROM dept d 
LEFT JOIN emp e ON d.deptno = e.deptno 
GROUP BY d.dname, d.loc;

32. 查询 emp 表中员工的奖金，没有奖金的默认为 0，使用 CASE WHEN。

SELECT 
    CASE 
        WHEN comm IS NULL THEN 0 
        ELSE comm 
    END AS 奖金 
FROM emp;

33. 从 scott 中，查询人员的成绩等级：<60 返回 “不及格”，60-70 返回 “及格”。

SELECT 
    CASE 
        WHEN sal < 600 THEN '工资低' 
        WHEN sal BETWEEN 600 AND 1700 THEN '还不错' 
        ELSE '其他' 
    END AS 工资等级 
FROM emp;

34. 查询工资等级：70-80 返回 “中等”，80-90 返回 “良好”，90 以上返回 “优秀”。

SELECT 
    CASE 
        WHEN sal BETWEEN 70 AND 80 THEN '中等' 
        WHEN sal BETWEEN 80 AND 90 THEN '良好' 
        WHEN sal > 90 THEN '优秀' 
    END AS 工资等级 
FROM emp;

35. 从 student 表中，根据性别列，如果是“男”，则返回 1，如果是“女”，则返回 2。

SELECT 
    CASE ssex 
        WHEN '男' THEN 1 
        WHEN '女' THEN 2 
    END AS 性别编码 
FROM student;

36. 行转列：统计各部门人数（10、20、30 部门）。

SELECT 
    SUM(DECODE(deptno, 10, 1, 0)) AS 部门10人数,
    SUM(DECODE(deptno, 20, 1, 0)) AS 部门20人数,
    SUM(DECODE(deptno, 30, 1, 0)) AS 部门30人数 
FROM emp;