Oracle数据库——子查询思考题-08

最新推荐文章于 2024-04-22 11:28:38 发布

a584898

最新推荐文章于 2024-04-22 11:28:38 发布

阅读量288

点赞数

分类专栏： Oracle数据库

本文链接：https://blog.csdn.net/a584898/article/details/81266101

版权

Oracle数据库专栏收录该内容

14 篇文章 1 订阅

订阅专栏

思考题：
1.列出薪金高于在部门30工作的所有的薪金的员工姓名和薪金，部门名称，平均工资，平均服务年薪，工资等级
确定所需要的数据表
emp表:员工姓名和薪金
dept表：部门名称
emp表:统计信息
salgrade表：工资等级
确定已知的关联字段
雇员和部门：emp.deptno=dept.deptno
雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal;
第一步：找出部门30工作的雇员工资，返回多行单列数据（IN,ANY,ALL）
SELECT sal FROM emp WHERE deptno=30;
第二步：查找大于30的员工姓名和工资，使用子查询完成
SELECT e.ename,e.sal
FROM emp e
WHERE e.sal>ALL(SELECT sal FROM emp WHERE deptno=30);
第三步：找到部门名称，增加dept表
SELECT e.ename,e.sal,d.dname
FROM emp e, dept d
WHERE e.sal>ALL(SELECT sal FROM emp WHERE deptno=30) AND e.deptno=d.deptno;
第四步：找到每位雇员的工资等级
SELECT e.ename,e.sal,d.dname,s.grade
FROM emp e, dept d，salgrade s
WHERE e.sal>ALL(SELECT sal FROM emp WHERE deptno=30) AND e.deptno=d.deptno
           AND e.sal BETWEEN s.losal AND s.hisal;
第五步：统计平均工资与平均服务年限
在之前给出的外部查询的SELECT子句里面，已经无法继续统计函数了（统计函数要么单独使用，要么结合GROUP BY
使用，而且SELECT子句里面只能够出现分组字段和统计函数，但是这个时候发现外部SELECT有太多的非分组
字段），现在需要使用统计查询，又无法直接使用，可以考虑FROM子句完成
SELECT e.ename,e.sal,d.dname,s.grade,temp.avg,temp.avg_year
FROM emp e, dept d，salgrade s,(
       SELECT deptno dno,AVG(sal) avg,AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12)avg_year
       FROM emp
       GROUP BY deptno) temp
WHERE e.sal>ALL(SELECT sal FROM emp WHERE deptno=30) AND e.deptno=d.deptno
           AND e.sal BETWEEN s.losal AND s.hisal
           AND d.deptno=temp.dno;

2.列出与”SCOTT“从事相同工作的所有员工及部门名称，部门人数以及领导所在部门的平均工资
确定所需要的数据表
emp表员工信息
dept表：部门名称
emp表：统计部门的人数
emp表：统计领导所在部门的平均工资
确定已知的关联字段
雇员和部门:emp.deptno=dept.deptno
雇员和领导：emp.mgr=memp.empno
第一步：找到”SCOTT“的工作，返回单行单列（WHERE使用）
SELECT job FROM emp WHERE ename='SCOTT';
第二步：找到从事以上工作的雇员信息
SELECT e.empno,e.ename,e.sal,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.sal,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.sal,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 d.deptno=temp.dno;
第五步：找到雇员领导，使用自身关联
SELECT e.empno,e.ename,e.sal,e.job,d.dname,temp.count,m.ename
FROM emp e,dept d,(
   SELECT deptno dno,COUNT(empno) count
   FROM emp
   GROUP BY deptno)temp，emp m
WHERE e.job=(
   SELECT job FROM emp WHERE ename='SCOTT')
   AND e.ename<>'SCOTT'
   AND e.deptno=d.deptno
   AND d.deptno=temp.dno
   AND e.mgr=m.empno(+);
第六步：领导所在部门的平均工资（同时列出领导所在的部门）
SELECT e.empno,e.ename,e.sal,e.job,d.dname,temp.count,m.ename,tempb.dna,tempb.mcount
FROM emp e,dept d,(
   SELECT deptno dno,COUNT(empno) count
   FROM emp
   GROUP BY deptno)temp，emp m,(
   SELECT dd.deptno ddno,dd.dname dna,COUNT(mm.empno) mcount
   FROM dept dd,emp ee,emp mm
   WHERE dd.deptno=ee.deptno
   AND ee.mgr=mm.empno(+)
   GROUP BY dd.deptno,dd.dname) tempb
WHERE e.job=(
   SELECT job FROM emp WHERE ename='SCOTT')
   AND e.ename<>'SCOTT'
   AND e.deptno=d.deptno
   AND d.deptno=temp.dno
   AND e.mgr=m.empno(+)
   AND tempb.ddno=m.deptno;
3.列出薪金比”SMIITH“或”ALLEN“多的所有员工的编号，姓名，部门名称，其领导姓名，部门人数
确定所需要的数据表
emp表：员工的编号，姓名
dept表：部门名称
emp表：领导姓名
emp表：统计部门人数
确定已知的关联字段
雇员和部门：emp.deptno=dept.deptno
雇员和领导：emp.mgr=memp.empno;
第一步SMIH或ALLEN的薪金（多行单列）
SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN');
第二步：使用>ANY，同时还需要取消SMITH和ALLEN
SELECT e.empno,e.ename
FROM emp e
WHERE sal>ANY(
SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN'))
   AND ename NOT IN('SMITH','ALLEN');
第三步：找到部门名称
SELECT e.empno,e.ename,d.dname
FROM emp e ，dept d
WHERE sal>ANY(
SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN'))
   AND ename NOT IN('SMITH','ALLEN')
   AND e.deptno=d.deptno;
第四步:加入emp表的自身关联，找到领导姓名
SELECT e.empno,e.ename,d.dname,m.ename
FROM emp e ，dept d,emp m
WHERE e.sal>ANY(
SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN'))
   AND e.ename NOT IN('SMITH','ALLEN')
   AND e.deptno=d.deptno
   AND e.mgr=m.empno(+);
第五步：统计每个部门的人数，此时外部的SELECT无法再继续使用统计函数，所以定义子查询完成
SELECT e.empno,e.ename,d.dname,m.ename,temp.count
FROM emp e ，dept d,emp m,(
   SELECT deptno dno,COUNT(empno) count
   FROM emp
   GROUP BY deptno) temp
WHERE e.sal>ANY(
SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN'))
   AND e.ename NOT IN('SMITH','ALLEN')
   AND e.deptno=d.deptno
   AND e.mgr=m.empno(+)
   AND e.deptno=temp.dno;

4.列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称，部门位置，部门人数
确定所需要的数据表
emp表：员工的编号，姓名
dept表：部门名称，部门位置
emp表：统计部门人数
确定已知的关联字段
雇员和领导：emp.mge=memp.empno
雇员和部门：emp.empno=dept.deptno
第一步：emp表进行自身关联找到受雇日期早于其直接上级的所有员工的编号，姓名
SELECT e.empno,e.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate;
第二步：找到部门名称及位置
SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno(+)
   AND e.hiredate<m.hiredate
   AND e.deptno=d.deptno;
第三步;统计部门人数
SELECT e.empno,e.ename,d.dname,d.loc,temp.conut
FROM emp e,emp m,dept d,(
   SELECT deptno dno,COUNT(empno) conut
   FROM emp
   GROUP BY deptno) temp
WHERE e.mgr=m.empno(+)
   AND e.hiredate<m.hiredate
   AND e.deptno=d.deptno
   AND d.deptno=temp.dno;

5.列出所有”CLERK“（办事员）的姓名及其部门名称，部门人数，工资等级，以及具备此等级工资的雇员人数
确定所需要的数据表
emp表：姓名
dept表：部门名称
emp表：统计两次操作一个是部门人数，一个是工资等级人数
salgrade表：工资等级
确定已知的关联字段
雇员和部门：emp.deptno=dept.deptno
雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal
第一步：找到所有的办事员的姓名
SELECT e.ename
FROM emp e
WHERE e.job='CLERK';
第二步：找到雇员所在的部门
SELECT e.ename,d.dname
FROM emp e ,dept d
WHERE e.job='CLERK' AND e.deptno=d.deptno;
第三步：查询出部门人数，使用FROM子查询
SELECT e.ename,d.dname,tempd.count
FROM emp e ,dept d,(
   SELECT deptno dno,COUNT(empno) count
   FROM emp
   GROUP BY deptno)tempd
WHERE e.job='CLERK'
   AND e.deptno=d.deptno
   AND e.deptno=tempd.dno;
第四步：暂时不理会以上的内容，只查询一个内容：”工资等级，以及具备此等级工资的雇员人数“
SELECT s.grade,s.losal,s.hisal,COUNT(ee.empno)
FROM emp ee,salgrade s
WHERE ee.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade,s.losal,s.hisal;
第五步：将第四步的查询可以与第三步的查询混合在一起使用了，因为第四步给出了每个等级的工资范围
SELECT e.ename,d.dname,tempd.count,temps.grade,temps.count
FROM emp e ,dept d,(
   SELECT deptno dno,COUNT(empno) count
   FROM emp
   GROUP BY deptno)tempd,(
   SELECT s.grade
   grade,s.losal losal,s.hisal hisal,COUNT(ee.empno) count
   FROM emp ee,salgrade s
   WHERE ee.sal BETWEEN s.losal AND s.hisal
   GROUP BY s.grade,s.losal,s.hisal)temps
WHERE e.job='CLERK'
   AND e.deptno=d.deptno
   AND e.deptno=tempd.dno
   AND e.sal BETWEEN temps.losal AND
   temps.hisal;
以上的程序要反复联系