第6章 分组函数 练习二、三、课后作业（部分）

-- 查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资
SELECT e.DEPTNO,d.DNAME,COUNT(e.DEPTNO),MAX(e.SAL),MIN(e.SAL),SUM(e.SAL),AVG(e.SAL)
FROM emp e,dept d
WHERE e.DEPTNO=d.DEPTNO
GROUP BY e.DEPTNO;

-- 查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资
SELECT e.DEPTNO,d.DNAME,e.JOB,COUNT(e.DEPTNO),MAX(e.SAL),MIN(e.SAL),SUM(e.SAL),AVG(e.SAL)
FROM emp e,dept d
WHERE e.DEPTNO=d.DEPTNO
GROUP BY e.DEPTNO,e.JOB;

-- 查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息
SELECT COUNT(e1.ENAME),e1.MGR,e2.ENAME
FROM emp e1 LEFT JOIN emp e2
ON e1.MGR = e2.EMPNO
GROUP BY e2.ENAME;

-- 查询部门人数大于3的部门编号,部门名称,部门人数
SELECT e.DEPTNO,d.DNAME,COUNT(e.ENAME)
FROM emp e ,dept d 
WHERE e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO
HAVING COUNT(e.ENAME)>3;

-- 查询部门平均工资大于2000,并且人数大于2的部门编号,部门名称,部门人数,部门平均工资,并按照部门人数升序排序
SELECT e.DEPTNO,d.DNAME,COUNT(e.ENAME),AVG(e.SAL)
FROM emp e ,dept d 
WHERE e.DEPTNO = d.DEPTNO
GROUP BY e.DEPTNO
HAVING COUNT(e.ENAME)>2 AND AVG(e.SAL)>2000
ORDER BY COUNT(e.ENAME) ASC;

-- 查询部门平均工资在2500元以上的部门名称以及平均工资
SELECT d.DNAME,AVG(e.SAL)
FROM emp e,dept d
WHERE e.DEPTNO=d.DEPTNO
GROUP BY d.DNAME;

-- 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序
SELECT JOB,AVG(SAL)
FROM emp
WHERE JOB NOT LIKE ('SA%')
GROUP BY JOB
HAVING AVG(SAL) > 2500
ORDER BY AVG(SAL) ASC ;

-- 查询部门人数在2人以上的部门名称、最低工资、最高工资，并且对求得的工资进行四舍五入到整数位
SELECT d.DNAME,ROUND(MIN(e.SAL)),ROUND(MAX(e.SAL))
FROM emp e ,dept d
WHERE e.DEPTNO = d.DEPTNO 
GROUP BY d.DNAME
HAVING COUNT(d.DEPTNO) > 2;

-- 查询岗位不为SALESMAN，工资和大于等于5000的岗位以及每种岗位的工资和
SELECT JOB,SUM(SAL)
FROM emp
WHERE JOB NOT LIKE ('SALESMAN') 
GROUP BY JOB
HAVING SUM(SAL) >= 5000;

-- 显示经理号码和经理姓名，这个经理所管理的员工的最低工资，没有员工的KING也要显示
-- ，不包括最低工资小宇3000的，按最低工资由高到低排序

-- 写一个查询，显示每个部门最高工资和最低工资的差额
SELECT d.DNAME,MAX(e.SAL)-MIN(e.SAL)
FROM emp e ,dept d
WHERE e.DEPTNO = d.DEPTNO 
GROUP BY d.DNAME;