Oracle笔记八.docx
- 文档编号:29396487
- 上传时间:2023-07-23
- 格式:DOCX
- 页数:20
- 大小:21.55KB
Oracle笔记八.docx
《Oracle笔记八.docx》由会员分享,可在线阅读,更多相关《Oracle笔记八.docx(20页珍藏版)》请在冰豆网上搜索。
Oracle笔记八
一、复杂查询
1、列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资。
1、确定所需要的数据表:
∙emp表:
可以查询出员工的数量;
∙dept表:
部门名称;
∙emp表:
统计信息;
2、确定已知的关联字段:
∙emp.deptno=dept.deptno;
第一步:
找出至少有一个员工的部门编号
SELECTdeptno,COUNT(empno)
FROMemp
GROUPBYdeptno
HAVINGCOUNT(empno)>1;
第二步:
找到部门名称,肯定使用部门表,因为现在的数据量较小,所以可以将之前的emp表和dept表两个进行连接,统一采用多字段分组的方式查询;
SELECTd.deptno,d.dname,COUNT(e.empno)
FROMempe,deptd
WHEREe.deptno=d.deptno(+)
GROUPBYd.deptno,d.dname
HAVINGCOUNT(e.empno)>1;
第三步:
依然需要继续统计
SELECTd.deptno,d.dname,COUNT(e.empno),AVG(sal),MIN(sal),MAX(sal)
FROMempe,deptd
WHEREe.deptno=d.deptno(+)
GROUPBYd.deptno,d.dname
HAVINGCOUNT(e.empno)>1;
2、列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
1、确定所需要的数据表:
∙emp表:
查询出“SMITH”或“ALLEN”工资;
∙emp表:
最终的显示需要编号、姓名;
∙emp表:
领导的姓名,自身关联;
∙dept表:
部门名称;
2、确定已知的关联字段:
∙雇员和领导:
emp.mgr=memp.empno;
∙雇员和部门:
emp.deptno=dept.deptno;
第一步:
找出“SMITH”或“ALLEN”的工资
SELECTsalFROMempWHEREenameIN('SMITH','ALLEN');
第二步:
以上的查询返回的多行单列的记录,按照子查询的要求在WHERE子句中写合适,所以这个时候将上面的查询作为一个子查询出现,继续查询符合此要求的员工的编号、姓名。
SELECTe.empno,e.ename
FROMempe
WHEREe.sal>ALL(
SELECTsal
FROMemp
WHEREenameIN('SMITH','ALLEN'));
第三步:
查询出部门的名称,引入部门表,同时增加消除笛卡尔积的条件
SELECTe.empno,e.ename,d.dname
FROMempe,deptd
WHEREe.sal>ALL(
SELECTsal
FROMemp
WHEREenameIN('SMITH','ALLEN'))
ANDe.deptno=d.deptno;
第四步:
领导的信息需要emp表自身关联
SELECTe.empno,e.ename,d.dname,m.ename
FROMempe,deptd,empm
WHEREe.sal>ALL(
SELECTsal
FROMemp
WHEREenameIN('SMITH','ALLEN'))
ANDe.deptno=d.deptno
ANDe.mgr=m.empno(+);
3、列出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列。
1、确定所需要的数据表:
∙emp表:
员工的编号、姓名;
∙emp表:
领导的编号、姓名、计算年薪;
2、确定已知的关联字段:
emp.mgr=memp.empno;
SELECTe.empno,e.ename,m.empno,m.ename,(m.sal+NVL(m,0))*12income
FROMempe,empm
WHEREe.mgr=m.empno(+)
ORDERBYincomeDESC;
4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
1、确定所需要的数据表:
∙emp表:
雇员的编号、姓名;
∙emp表:
求出领导的工作日期;
∙dept表:
部门名称、位置;
∙emp表:
统计部门人数;
2、确定已知的关联字段:
∙雇员和部门:
emp.deptno=dept.deptno;
∙雇员和领导:
emp.mgr=memp.empno;
第一步:
列出受雇日期早于其直接上级的所有员工的编号、姓名——自身关联emp表。
SELECTe.empno,e.ename
FROMempe,empm
WHEREe.mgr=m.empno(+)ANDe.hiredate 第二步: 加入部门信息,继续引入部门表 SELECTe.empno,e.ename,d.dname,d.loc FROMempe,empm,deptd WHEREe.mgr=m.empno(+)ANDe.hiredate ANDe.deptno=d.deptno; 额外提问,此时的笛卡尔积: emp表的14条*emp表的14条*dept表的4条; 第三步: 统计部门人数,此时由于要使用统计函数,而且以上的查询也无法再直接出现统计函数,所以使用子查询完成 SELECTe.empno,e.ename,d.dname,d.loc,temp.count FROMempe,empm,deptd,( SELECTdeptnodno,COUNT(empno)count FROMemp GROUPBYdeptno)temp WHEREe.mgr=m.empno(+)ANDe.hiredate ANDe.deptno=d.deptno ANDe.deptno=temp.dno; 当查询显示的时候需要统计信息,但是又不能直接使用统计函数查询的话,通过子查询在FROM子句之后进行统计。 5、列出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门。 1、确定所需要的数据表: ∙dept表: 部门的信息; ∙emp表: 求出所有的统计信息; 2、确定已知的关联字段: emp.deptno=dept.deptno; SELECTd.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal) FROMempe,deptd WHEREe.deptno(+)=d.deptno GROUPBYd.deptno,d.dname,d.loc; 6、列出所有“CLERK”(办事员)的姓名及其部门名称,部门的人数,工资等级。 1、确定所需要的数据表: ∙emp表: 找到办事员的姓名; ∙dept表: 部门名称; ∙emp表: 统计求出部门的人数; ∙salgrade表: 查询工资等级; 2、确定已知的关联字段: ∙emp表和dept表: emp.deptno=dept.deptno; ∙emp表和salgrade表: emp.salBETWEENsalgrade.losalANDsalgrade.hisal; 第一步: 找到所有办事员的姓名 SELECTe.ename FROMempe WHEREjob='CLERK'; 第二步: 找到部门信息,引入dept表,同时增加消除笛卡尔积的条件 SELECTe.ename,d.dname FROMempe,deptd WHEREjob='CLERK'ANDe.deptno=d.deptno; 第三步: 部门人数需要额外的统计,但是本程序的查询里面已经不可能继续使用COUNT()函数,所以写子查询统计 SELECTe.ename,d.dname,temp.count FROMempe,deptd,( SELECTdeptnodno,COUNT(empno)count FROMemp GROUPBYdeptno)temp WHEREe.job='CLERK'ANDe.deptno=d.deptno ANDd.deptno=temp.dno; 第四步: 雇员的工资等级,继续引入salgrade表 SELECTe.ename,d.dname,temp.count,s.grade FROMempe,deptd,( SELECTdeptnodno,COUNT(empno)count FROMemp GROUPBYdeptno)temp,salgrades WHEREe.job='CLERK'ANDe.deptno=d.deptno ANDd.deptno=temp.dno ANDe.salBETWEENs.losalANDs.hisal; 7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资。 1、确定所需要的数据表: ∙emp表: 最低薪金大于1500的工作肯定需要使用emp表统计求出,以及可以求出雇员人数; ∙dept表: 求出些雇员所在的部门信息; ∙emp表: 统计求出部门的平均工资; 2、确定已知的关联字段: emp.deptno=dept.deptno; 第一步: 使用emp表按照job分组,统计最低工资(HAVING)和人数 SELECTe.job,COUNT(e.empno) FROMempe GROUPBYe.job HAVINGMIN(e.sal)>1500; 第二步: 要查询出雇员所在的部门信息,但是以上的查询能跟dept表有关联吗? 以上的查询和dept表之间并没有关联字段,那么如果没有关联字段,一定会有笛卡尔积产生,但是多表查询必须要消除笛卡尔积,所以必须联系; ∙以上的查询可以和emp表的job字段关联; ∙要引入的dept表也可以和emp表的deptno字段关联; SELECTtemp.job,temp.count,d.dname,e.ename FROMdeptd,( SELECTe.jobjob,COUNT(e.empno)count FROMempe GROUPBYe.job HAVINGMIN(e.sal)>1500)temp, empe WHEREe.deptno=d.deptnoANDe.job=temp.job; 第三步: 求出一个部门的平均工资,使用emp表在子查询中统计 SELECTtemp.job,temp.count,d.dname,e.ename,res.avg FROMdeptd,( SELECTe.jobjob,COUNT(e.empno)count FROMempe GROUPBYe.job HAVINGMIN(e.sal)>1500)temp, empe,( SELECTdeptnodno,AVG(sal)avg FROMemp GROUPBYdeptno)res WHEREe.deptno=d.deptnoANDe.job=temp.job ANDe.deptno=res.dno; 本题目之所以出的如此之复杂,目的是训练大家寻找关联字段的能力,但是本题目没有任何的意义,知道就行了。 8、列出在部门“SALES”(销售部)工作的员工姓名、基本工资、雇佣日期、部门名称、假定不知道销售部的部门编号。 1、确定所需要的数据表: ∙emp表: 员工姓名、基本工资、雇佣日期; ∙dept表: 找到销售部的部门编号、部门名称; 2、确定已知的关联字段: emp.deptno=dept.deptno; SELECTe.ename,e.sal,e.hiredate,d.dname FROMempe,deptd WHEREe.deptno=d.deptno ANDd.dname='SALES'; 9、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级。 1、确定所需要的数据表: ∙emp表: 可以求出公司的平均薪金; ∙emp表: 员工的信息; ∙dept表: 部门的信息; ∙emp表: 领导的信息; ∙salgrade表: 工资等级; 2、确定已知的关联字段: ∙雇员和部门: emp.deptno=dept.deptno; ∙雇员和领导: emp.mgr=memp.empno; ∙雇员和工资等级: emp.salBETWEENsalgrade.losalANDsalgrade.hisal; 第一步: 求出公司的平均薪金 SELECTAVG(sal)FROMemp; 第二步: 将以上的子查询放在WHERE子句之中,作为一个查询条件,求出满足此条件的雇员信息。 SELECTe.empno,e.ename,e.job,e.sal FROMempe WHEREe.sal>( SELECTAVG(sal)FROMemp); 第三步: 找到部门的名称 SELECTe.empno,e.ename,e.job,e.sal,d.dname,d.loc FROMempe,deptd WHEREe.sal>( SELECTAVG(sal)FROMemp) ANDe.deptno=d.deptno; 第四步: 找到领导的信息 SELECTe.empno,e.ename,e.job,e.sal,d.dname,d.loc,m.ename FROMempe,deptd,empm WHEREe.sal>( SELECTAVG(sal)FROMemp) ANDe.deptno=d.deptno ANDe.mgr=m.empno(+); 第五步: 找到工资等级 SELECTe.empno,e.ename,e.job,e.sal,d.dname,d.loc,m.ename,s.grade FROMempe,deptd,empm,salgrades WHEREe.sal>( SELECTAVG(sal)FROMemp) ANDe.deptno=d.deptno ANDe.mgr=m.empno(+) ANDe.salBETWEENs.losalANDs.hisal; 10、列出与“SCOTT”从事相同工作的所有员工及部门名称,部门人数。 1、确定所需要的数据表: ∙emp表: 找到SCOTT的工作; ∙emp表: 员工的信息; ∙dept表: 部门名称; ∙emp表: 部门人数; 2、确定已知的关联字段: emp.deptno=dept.deptno; 第一步: 找到SCOTT的工作 SELECTjobFROMempWHEREename='SCOTT'; 第二步: 以上的子查询返回单行单列的数据,所以可以在WHERE子句中出现,以这个条件查找满足要求的雇员信息 SELECTe.empno,e.ename,e.job FROMempe WHEREe.job=( SELECTjob FROMemp WHEREename='SCOTT') ANDe.ename<>'SCOTT'; 第三步: 找到部门名称 SELECTe.empno,e.ename,e.job,d.dname FROMempe,deptd WHEREe.job=( SELECTjob FROMemp WHEREename='SCOTT') ANDe.ename<>'SCOTT' ANDe.deptno=d.deptno; 第四步: 找到部门人数,需要统计,所以在FROM子句之中编写 SELECTe.empno,e.ename,e.job,d.dname,temp.count FROMempe,deptd,( SELECTdeptnodno,COUNT(empno)count FROMemp GROUPBYdeptno)temp WHEREe.job=( SELECTjob FROMemp WHEREename='SCOTT') ANDe.ename<>'SCOTT' ANDe.deptno=d.deptno ANDtemp.dno=e.deptno; 11、列出公司各个工资等级雇员的数量、平均工资。 1、确定所需要的数据表: ∙emp表: 统计出数据; ∙salgrade表: 得出工资等级; 2、确定已知的关联字段: emp.salBETWEENsalgrade.losalANDs.hisal; 本程序实际上就是一个多字段分组而已,唯一不同的是,将分组条件设置为salgrade表中的字段; SELECTs.grade,s.losal,s.hisal,COUNT(e.empno),AVG(e.sal) FROMempe,salgrades WHEREe.salBETWEENs.losalANDs.hisal GROUPBYs.grade,s.losal,s.hisal; 12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。 1、确定所需要的数据表: ∙emp表: 找出所有在30部门工作的雇员的工资; ∙emp表: 最终显示的雇员姓名; ∙dept表: 找到部门名称; 2、确定已知的关联字段: emp.deptno=dept.deptno; 第一步: 找到30部门工作的雇员的工资 SELECTsalFROMempWHEREdeptno=30; 第二步: 高于30部门,使用ALL操作符,引入emp表,查询姓名和薪金 SELECTe.empno,e.ename FROMempe WHEREsal>ALL( SELECTsalFROMempWHEREdeptno=30) ANDe.deptno=d.deptno; 13、列出在每个部门工作的员工数量、平均工资和平均服务期限。 1、确定所需要的数据表: ∙dept表: 找到部门信息; ∙emp表: 统计出数量、平均工资、平均服务年限; 2、确定已知的关联字段: emp.deptno=dept.deptno; 直接将dept和emp表关联,使用多字段分组即可,但是对于服务年限需要一个计算过程。 SELECTd.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal),AVG(MONTHS_BWTWEEN(SYSDATE,e.hiredate)/12)year FROMempe,deptd WHEREe.deptno(+)=d.deptno GROUPBYd.deptno,d.dname,d.loc; 14、列出所有员工的姓名、部门名称和工资。 1、确定所需要的数据表: ∙emp表: 找到员工姓名; ∙dept表: 部门名称; 2、确定已知的关联字段: emp.deptno=dept.deptno; SELECTe.ename,d.dname,e.sal FROMempe,deptd WHEREe.deptno=d.deptno; 15、列出所有部门的详细信息和部门人数。 1、确定所需要的数据表: ∙emp表: 统计信息; ∙dept表: 查询部门信息; 2、确定已知的关联字段: emp.deptno=dept.deptno; SELECTd.deptno,d.dname,d.loc,COUNT(e.empno) FROMempe,deptd WHEREe.deptno(+)=d.deptno GROUPBYd.deptno,d.dname,d.loc; 16、列出各种工作的最低工资及从事此工作的雇员姓名。 1、确定所需要的数据表: ∙emp表: 统计出各个工作的最低工资; ∙emp表: 查找出雇员姓名; 第一步: 按照职位统计各个职位的最低工资 SELECTjob,MIN(sal)FROMemp GROUPBYjob; 第二步: 将以上的查询和emp表关联 SELECTe.ename,e.job,e.sal FROMempe,( SELECTjob,MIN(sal)FROMemp GROUPBYjob)temp WHEREe.job=temp.jobANDe.sal=temp.min; 17、列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。 1、确定所需要的数据表: ∙emp表: 找到经理的薪金、姓名; ∙dept表: 部门名称; ∙emp表: 统计部门人数; 2、确定已知的关联字段: emp.deptno=dept.deptno; 第一步: 找到所有部门的经理 SELECTdeptno,MIN(sal) FROMemp WHEREjob='MANAGER' GROUPBYdeptno; 第二步: 找到姓名,但是以上的子查询,不能再出现其他的字段 SELECTe.ename,e.sal FROMempe,( SELECTdeptnodno,MIN(sal)sal FROMemp WHEREjob=’MANAGER’ GROUPBYdeptno)temp WHEREe.deptno=temp.dnoANDe.sal=temp.salANDe.job='MANAGER'; 第三步: 加入部门的名称信息 SELECTe.ename,e.sal,d.dname FROMempe,( SELECTdeptnodno,MIN(sal)sal FROMemp WHEREjob='MANAGER' GROUPBYdeptno)temp,deptd WHEREe.deptno=temp.dnoANDe.sal=temp.salANDe.job='MANAGER' ANDe.deptno=d.deptno; 第四步: 统计部门人数 SELECTe.ename,e.sal,d.dname,res.count FROMempe,( SELECTdeptnodno,MIN(sal)sal FROMemp WHEREjob=’MANAGER’ GROUPBYdeptno)temp,deptd,( SELECTdeptnodno,COUNT(empno)count FROMemp GROUPBYdeptno)res WHEREe.deptno=temp.dnoANDe.sal=temp.salANDe.job='MANAGER' ANDe.deptno=d.deptnoANDre
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 笔记