1、福建工程学院信息科学与工程学院实验报告2013 2014 学年第 一 学期 任课老师： 胡文瑜 课程名称 数据库开发技术 班级 信管1102 座号 20 姓名 郭明光实验题目实验二、Oracle 函数、组函数和数据分组统计 实验时间 2013.10.10实验目的、内容一、 实验目的1 掌握分组查询，连接查询和集合运算的使用方法。2 掌握子查询及内嵌视图的使用方法。3 掌握多列子查询和相关子查询的使用。4 掌握 TOP-N 分析方法。掌握 INSERT ALL 语句和 MERGE 语句的使用方法。实验设计过程二、 实验内容和要求第一部分：使用 SQL 语句完成以下查询要求。(实验用的数据表在本文
2、档的最后面)1. 查询使用红色零件的工程名称。(考核多表（三表）连接操作) (分别使用相关的子查询、带 in 谓词的子查询、带 exists 谓词的子查询和连接查询实现。注意体会不同实现方式的区别。)/* 连接查询 */select distinct jname from p,j,spjwhere spj.jno = j.jno and spj.pno=p.pno and p.color=红/*三层带 IN 谓词的嵌套查询 */select jnamefrom jwhere jno in (select jnofrom spjwhere pno in(select pno from pwher
3、e color=红)/* 带 exists 谓词的子查询 */select jname from jwhere exists(select *from pwhere exists(select *from spjwhere j.jno=jno and p.pno=pno and p.color=红);/* 相关子查询 */select jname from jwhere jno in (select jnofrom spj,pwhere p.pno=spj.pno and color=红)2. 查询每一种零件被供应的次数，要求：1）结果显示零件号、零件名称和被供应的次数。2）零件表中的所有零件
4、都要统计。显示效果形如下：PNO PNAME COUNT_P- - -P1 螺母 4P2 螺栓 2P3 螺丝刀 5P4 螺丝刀 0P5 凸轮 3P6 齿轮 3P7 把手 0已选择 7 行。select p.pno,pname,count(spj.pno) COUNT_Pfrom p,j,spjwhere j.jno = spj.jno and p.pno = spj.pno(+) group by p.pno,pnameorder by p.pno问题 1？用 GROUP BY P.PNO 还是 SPJ.PNO?Answer: 用 GROUP BY P.PNO若用 group by spj.p
5、no 则结果如下问题 2？GROUP BY P.PNO 和 GROUP BY P.PNO, PNAME 结果是否一样？Answer: GROUP BY P.PNO 无法运行问题 3？用 COUNT(SPJ.PNO)还是 COUNT(P.PNO)Answer: 用 COUNT(SPJ.PNO)若用 COUNT(P.PNO)则结果如下明显错误，P4 和 P7 没被使用问题 4？如果查询结果不要求显示零件号，只要求显示零件名称和数量，以下这个 SQL 语句对吗？SELECT PNAME, COUNT(SPJ.PNO) FROM P, SPJWHERE P.PNO = SPJ.PNO(+)GROUP
6、BY PNAME;Answer: 正确，但因为 pname 为螺丝刀的有分为蓝色和红色两种，所以 P4 没显现出来结果如下3. 查询 student 表中各系学生数占全校学生人数的百分比显示效果形如下：SDEPT %Student- -CS 33.3FL 22.2IS 22.2MA 22.2已选择 4 行。INSERT INTO STUDENT VALUES(95001,李明勇,男,20,CS);INSERT INTO STUDENT VALUES(95002,刘晨,女,19,IS);INSERT INTO STUDENT VALUES(95003,王名,女,18,MA);INSERT INT
7、O STUDENT VALUES(95004,张立,男,19,CS);INSERT INTO STUDENT VALUES(95005,张军,男,21,MA);INSERT INTO STUDENT VALUES(95006,王张凤,女,19,FL);INSERT INTO STUDENT VALUES(95007,王敬,女,18,IS);INSERT INTO STUDENT VALUES(95008,张名惠,男,19,FL);COMMIT;由表可知，一共有 8 人，而每个系有 2 人SELECT a.sdept,(A.student_SUM / B.student_TOTAL)*100 “
8、%student“FROM (SELECT sdept, COUNT(sno) student_SUMFROM studentGROUP BY sdept) A,(SELECT COUNT(sno) student_TOTALFROM student) B;4. 查询工资高于本部门平均工资的员工信息。SELECT a.*, b.平均工资FROM emp a, (SELECT deptno, avg(sal) 平均工资FROM empGROUP BY deptno) bWHERE a.deptno = b.deptnoAND a.sal b.平均工资;5. 显示与 30 部门任何雇员薪水及岗位相
9、匹配的雇员的姓名、部门编号、薪水及佣金。SELECT ename,deptno,sal,comm FROM empWHERE sal IN(SELECT sal FROM empWHERE deptno = 30)AND job IN(SELECT job FROM empWHERE deptno=30)and deptnoany(select sagefrom studentwhere sdept=CS)and sdept(select min(sage)from studentwhere sdept=CS)and sdept95002)8. 返回雇员号最小的 10 个员工信息。SELECT rownum,a.*FROM (SELECT *FROM EMP ORDER BY empno) aWHERE rownum(select count(empno)from empwhere deptno=20group by deptno)调试过程记录实验结果记录以及与预期结果比较以及分析总结以及心得体会
