SQL复杂查询（二）

6.列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数，工资等级
确定所需要的数据表：
dept表:部门名称
emp表：姓名，工资等级
emp表与dept表关联统计：部门人数
salgrade表：查询工资等级

确定已知的关联字段：
emp.deptno=dept.deptno

6.1 找出办事员的姓名

SELECT e.ename FROM emp e WHERE job='CLERK';

6.2 找到部门信息，引入dept表，同时增加消除笛卡尔积的条件

SELECT e.ename ,d.dname FROM emp e ,dept d   WHERE job='CLERK' AND e.deptno=d.deptno;

6.3 部门人数需要额外统计，但是这是查询里面已经不能继续使用COUNT()函数，所以写子查询

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

6.4 雇员的工资等级，继续引入salgrade表

SELECT e.ename,d.dname,temp.count,s.grade  FROM emp e ,dept d ,(SELECT deptno,COUNT(empno) count FROM emp GROUP BY deptno) temp ,salgrade s
         WHERE job='CLERK' AND e.deptno=d.deptno AND e.deptno=temp.deptno AND e.sal BETWEEN s.losal AND s.hisal;

7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数和所在部门名称、位置、平均工资
确定所需要的数据表：
emp:最低薪金大于1500的工作，以及求出雇员人数
dept表：求出此雇员所在的部门信息
emp表：统计求出部门的平均工资

确定已知的关联字段：
emp.deptno=dpet.deptno

7.1 使用emp表按照job分组，统计最低工资（HAVING ）和人数

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

要查询出雇员所在的部门信息，但是以上的查询能跟dept表有联系吗？
以上的查询和dept表之间并没有关联字段，那么如果没有关联字段，一定会有笛卡尔积产生，但是多表查询必须要消除笛卡尔积，所以必须找联系
以上的查询可以和emp表的job字段关联；
要引入dept表也可以和emp表的deptno字段关联；

 SELECT d.dname,d.loc,temp.job,temp.count ,res.sal
 FROM emp e ,dept d,(SELECT e.job job,COUNT(empno) count 
      FROM emp e 
      GROUP BY e.job HAVING MIN(e.sal)>1500) temp,(SELECT AVG(sal) sal,deptno dno  FROM emp GROUP BY deptno ) res
 WHERE e.deptno=d.deptno AND temp.job=e.job AND e.deptno=res.dno;

8.列出在部门“SALES”（销售部）工作的员工姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。
确定所需要的数据表：
emp表：员工姓名，基本工资，雇佣日期
dept表：销售部的部门编号，部门名称
确定已知的关联字段：
emp.deptno=dpet.deptno

SELECT e.ename,e.sal ,e.hiredate ,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname='SALES';

9.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
确定所需要的数据表：
emp表：员工的工资等级，薪金，上级领导
dept表：员工部门
salgrade表：工资等级

确定已知的关联字段：
emp1.mgr=emp2.empno
emp.deptno=dept.deptno

9.1 求出薪金高于公司平均薪金的员工信息

SELECT e.ename,e.sal 
FROM emp e ,(SELECT AVG(sal) avgsal FROM emp) temp
WHERE e.sal>avgsal;

9.2 加入dept表和salgrade表，求出所在部门，上级领导，工资等级

SELECT e.ename,e.sal ,temp.avgsal,s.grade,d.dname,m.ename
FROM emp e ,emp m,dept d ,salgrade s,(SELECT AVG(sal) avgsal FROM emp) temp
WHERE e.sal>avgsal AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal and s.hisal;

10.列出“SCOTT”从事相同工作的所有员工及部门名称，部门人数
确定所需要的数据表：
emp表：员工姓名
dept表：部门名称
emp表：部门人数

确定已知的关联字段：
emp.deptno=dept.deptno

10.1 与“SCOTT”从事相同工作的员工信息

SELECT e.ename,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;

10.2 统计部门人数 使用子查询

SELECT e.ename,d.dname ,e.job ,temp.count FROM emp e,dept d , (SELECT COUNT(empno) count,deptno dno  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 e.deptno=temp.dno;