2020.4.24

10、列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

1、确定所需要的数据表：

emp表：找到SCOTT的工作；
emp表：员工的信息；
dept表：部门名称；
emp表：部门人数；
2、确定已知的关联字段：emp.deptno=dept.deptno；

第一步：找到SCOTT的工作

SELECT job FROM emp WHERE ename=’SCOTT’;
第二步：以上的子查询返回单行单列的数据，所以可以在WHERE子句中出现，以这个条件查找满足要求的雇员信息

复制代码
SELECT e.empno,e.ename,e.job
FROM emp e
WHERE e.job=(
SELECT job
FROM emp
WHERE ename=’SCOTT’)
AND e.ename<>’SCOTT’;
复制代码
第三步：找到部门名称

复制代码
SELECT e.empno,e.ename,e.job,d.dname
FROM emp e,dept d
WHERE e.job=(
SELECT job
FROM emp
WHERE ename=’SCOTT’)
AND e.ename<>’SCOTT’
AND e.deptno=d.deptno;
复制代码
第四步：找到部门人数，需要统计，所以在FROM子句之中编写

复制代码
SELECT e.empno,e.ename,e.job,d.dname,temp.count
FROM emp e,dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp
GROUP BY deptno) temp
WHERE e.job=(
SELECT job
FROM emp
WHERE ename=’SCOTT’)
AND e.ename<>’SCOTT’
AND e.deptno=d.deptno
AND temp.dno=e.deptno;
复制代码
11、列出公司各个工资等级雇员的数量、平均工资。

1、确定所需要的数据表：

emp表：统计出数据；
salgrade表：得出工资等级；
2、确定已知的关联字段：emp.sal BETWEEN salgrade.losal AND s.hisal；

本程序实际上就是一个多字段分组而已，唯一不同的是，将分组条件设置为salgrade表中的字段；

SELECT s.grade,s.losal,s.hisal,COUNT(e.empno),AVG(e.sal)
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade,s.losal,s.hisal;
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

1、确定所需要的数据表：

emp表：找出所有在30部门工作的雇员的工资；
emp表：最终显示的雇员姓名；
dept表：找到部门名称；
2、确定已知的关联字段：emp.deptno=dept.deptno；

第一步：找到30部门工作的雇员的工资

SELECT sal FROM emp WHERE deptno=30;
第二步：高于30部门，使用ALL操作符，引入emp表，查询姓名和薪金

SELECT e.empno,e.ename
FROM emp e
WHERE sal>ALL(
SELECT sal FROM emp WHERE deptno=30)
AND e.deptno=d.deptno;
13、列出在每个部门工作的员工数量、平均工资和平均服务期限。

1、确定所需要的数据表：

dept表：找到部门信息；
emp表：统计出数量、平均工资、平均服务年限；
2、确定已知的关联字段：emp.deptno=dept.deptno；

直接将dept和emp表关联，使用多字段分组即可，但是对于服务年限需要一个计算过程。

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal),AVG(MONTHS_BWTWEEN(SYSDATE,e.hiredate)/12) year
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.deptno,d.dname,d.loc;
14、列出所有员工的姓名、部门名称和工资。

1、确定所需要的数据表：

emp表：找到员工姓名；
dept表：部门名称；
2、确定已知的关联字段：emp.deptno=dept.deptno；

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