Oracle 课堂3

--4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
SELECT emp.deptno,dname,count(ename)
FROM emp,dept
WHERE emp.deptno=dept.deptno
GROUP BY emp.deptno,dname  
HAVING count(ename)>(SELECT avg(count(ename)) FROM emp GROUP BY deptno);

--26.    显示出平均工资最高的的部门平均工资及部门名称
SELECT dname,round(avg(sal),2)
FROM emp JOIN dept ON emp.deptno=dept.deptno
GROUP BY dname
HAVING avg(sal)=(SELECT MAX(avg(sal))
                  FROM emp
                  GROUP BY deptno);
                  
13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
SELECT deptno,avg(sal) a
FROM emp
GROUP BY deptno;

SELECT ename,sal,round(t.a,2),sal-round(t.a,2)
FROM emp,(SELECT deptno,avg(sal) A
          FROM emp
          GROUP BY deptno) t
WHERE emp.deptno=t.deptno and sal>t.a;


--查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
   --部门平均工资
   SELECT deptno,count(empno),avg(sal) ps
   FROM emp
   GROUP BY deptno
   
   --部门总平均工资
   SELECT avg(t.ps)
   FROM (   SELECT deptno,count(empno),avg(sal) ps
            FROM emp
            GROUP BY deptno) t
            
  select ename,sal,(e.sal-(   SELECT avg(t.ps)
                              FROM (   SELECT deptno,count(empno),avg(sal) ps
                                       FROM emp
                                       GROUP BY deptno) t
                           )) as "额度"
  from emp e,(   SELECT deptno,count(empno),avg(sal) ps
               FROM emp
               GROUP BY deptno) t
  WHERE e.deptno=t.deptno
       and sal>t.ps
       
       
查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
 --部门平均工资
   SELECT deptno,count(empno),avg(sal) ps
   FROM emp
   GROUP BY deptno
      
  select ename,sal,(e.sal-t.ps) as "额度"
  from emp e,(   SELECT deptno,count(empno),avg(sal) ps
               FROM emp
               GROUP BY deptno) t
  where e.deptno=t.deptno
       AND sal>t.ps
       AND e.sal-t.ps>0
       
24.    显示每位经理管理员工的最低工资，及最低工资者的姓名
select e.sal,e.ename from emp e,
(SELECT MIN(e.sal) salary,m.empno FROM emp e,emp m WHERE e.mgr = m.empno GROUP BY m.empno) l
WHERE e.sal = l.salary AND e.mgr = l.empno

--24.    显示每位经理管理员工的最低工资，及最低工资者的姓名
SELECT ename,sal FROM emp WHERE (sal,mgr) IN (SELECT MIN(sal),mgr FROM emp GROUP BY mgr);

SELECT mgr,MIN(sal) M
FROM emp
GROUP BY mgr;

SELECT ename,t.m
FROM emp,(SELECT mgr,MIN(sal) M
          FROM emp
          GROUP BY mgr) t
WHERE emp.mgr(+)=t.mgr AND sal&