ORACLE经典作业：三表联查：EMP,DEPT,SALGRADE

1.列出至少有1个员工的所有部门编号、名称，并统计出这些部门的平均、最低、最高工资。

解法1：

select d.deptno 部门编号,d.dname 部门名称,

round(avg(e.sal),2) 部门平均工资,min(e.sal) 部门最低工资,max(e.sal) 部门最高工资
from dept d,emp e
where e.deptno=d.deptno
group by d.deptno,d.dname;

解法2：

select e.deptno,w.dname,round(avg(e.sal),2),min(e.sal),max(e.sal)
from emp e ,(select e.deptno,d.dname from dept d ,emp e where e.deptno=d.deptno ) w 
where w.deptno=e.deptno group by e.deptno,w.dname;

2.列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。

select e.empno,e.ename,m.ename,d.dname
from emp e left join emp m on e.mgr=m.empno left join dept d on e.deptno=d.deptno
where e.sal>some(select sal from emp where ename in ('SMITH','ALLEN'))；

3.列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。

select e.empno,e.ename,m.empno,m.ename,m.sal*12+nvl(m.comm,0) yearsal
from emp e,emp m
where e.mgr=m.empno(+)
order by yearsal desc;

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

a.员工的编号、姓名、部门名称、部门位置、部门人数
select e.empno,e.ename,d.dname,d.loc,temp.empcount
from emp e,dept d,(select deptno dno,count(empno) empcount
                          from emp
                          where deptno is not null
                          group by deptno) temp
where e.deptno=d.deptno and d.deptno=temp.dno

b.这里需要单独先统计每个部门有多少人
select deptno dno,count(empno) empcount
from emp
where deptno is not null
group by deptno

c.受雇日期早于其直接上级的

d.最终结果如下：
select e.empno,e.ename,d.dname,d.loc,temp.empcount,e.hiredate,m.hiredate
from emp e,emp m,dept d,(select deptno dno,count(empno) empcount
                          from emp
                          where deptno is not null
                          group by deptno) temp
where e.deptno=d.deptno and d.deptno=temp.dno and e.mgr=m.empno
      and e.hiredate<m.hiredate;

5. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。

select d.dname,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.dname;

6.列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。

select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.job='CLERK'

部门人数
select deptno dno,count(empno) empcount
from emp
group by deptno

加入部门人数
select e.ename,d.dname,t1.empcount
from emp e,dept d,(select deptno dno,count(empno) empcount
                          from emp
                          group by deptno) t1
where e.deptno=d.deptno and t1.dno=d.deptno and e.job='CLERK'
加入工资等级

最终结果：
select e.ename,d.dname,t1.empcount,s.grade
from emp e,dept d,salgrade s,(select deptno dno,count(empno) empcount
                          from emp
                          group by deptno) t1
where e.deptno=d.deptno 
      and t1.dno=d.deptno 
      and e.sal between s.losal and s.hisal 
      and e.job='CLERK'

7.列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。
写法1：

SELECT E.ENAME,E.SAL,E.HIREDATE,d.DNAME 

from emp e,dept d WHERE  d.deptno=e.deptno and D.DEPTNO NOT IN

(SELECT D2.DEPTNO FROM EMP E2 INNER JOIN DEPT D2 ON E2.DEPTNO=D2.DEPTNO
                      WHERE DNAME!='SALES'
                      GROUP BY D2.DEPTNO);

写法2：

SELECT E.ENAME,E.SAL,E.HIREDATE,d.DNAME
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
WHERE D.DEPTNO NOT IN(SELECT D2.DEPTNO 
                      FROM EMP E2 INNER JOIN DEPT D2 ON E2.DEPTNO=D2.DEPTNO
                      WHERE DNAME!='SALES'
                      GROUP BY D2.DEPTNO);

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

方法1：

select DISTINCT e.ename ,D.DNAME ,m.ename,e.sal ,s.grade
from emp e ,emp m,dept d ,salgrade S
WHERE E.DEPTNO=D.DEPTNO AND E.MGR=M.EMPNO AND E.SAL>(select avg(sal) FROM emp) AND E.SAL BETWEEN S.LOSAL AND S.HISAL; 

方法2：

SELECT E.ENAME,D.DNAME,M.ENAME,E.SAL,S.GRADE
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO 
           INNER JOIN EMP M ON E.MGR=M.EMPNO 
           INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE E.SAL>=(SELECT ROUND(AVG(SAL),2) AVS FROM EMP);

9.列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
SELECT E.ENAME ,D.DNAME,W.C
FROM EMP E,DEPT D,(SELECT deptno,COUNT(*) C FROM EMP GROUP BY deptno) W
WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=W.DEPTNO AND JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT');

10.列出公司各个工资等级雇员的数量、平均工资。
SELECT S.GRADE 工资等级,COUNT(S.GRADE) 雇员的数量, ROUND(SUM(E.SAL)/COUNT(S.GRADE),2) 平均工资 
FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL GROUP BY S.GRADE ORDER BY S.GRADE DESC;