福建工程学院Oracle实验二下.doc

福建工程学院信息科学与工程学院实验报告 2013 2014 学年第 一 学期 任课老师： 胡文瑜 课程名称数据库开发技术班级信管1102座号20姓名郭明光实验题目实验二、Oracle函数、组函数和数据分组统计实验时间2013.10.10实验目的、内容一、 实验目的1 掌握分组查询，连接查询和集合运算的使用方法。2 掌握子查询及内嵌视图的使用方法。3 掌握多列子查询和相关子查询的使用。4 掌握TOP-N分析方法。掌握INSERT ALL语句和MERGE语句的使用方法。实验设计过程二、 实验内容和要求第一部分：使用SQL语句完成以下查询要求。(实验用的数据表在本文档的最后面)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 jno from spj where pno in( select pno from p where color=红)/* 带exists谓词的子查询 */select jname from jwhere exists (select * from p where exists (select * from spj where j.jno=jno and p.pno=pno and p.color=红);/* 相关子查询 */select jname from jwhere jno in (select jno from spj,p where p.pno=spj.pno and color=红 )2. 查询每一种零件被供应的次数，要求： 1）结果显示零件号、零件名称和被供应的次数。 2）零件表中的所有零件都要统计。显示效果形如下：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.pno则结果如下问题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语句对吗？SELECTPNAME, COUNT(SPJ.PNO) FROM P, SPJWHEREP.PNO = SPJ.PNO(+)GROUP 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 INTO 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 %studentFROM (SELECT sdept, COUNT(sno) student_SUM FROM student GROUP BY sdept) A, (SELECT COUNT(sno) student_TOTAL FROM student) B;4. 查询工资高于本部门平均工资的员工信息。 SELECT a.*, b.平均工资 FROM emp a, (SELECT deptno, avg(sal) 平均工资 FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno AND a.sal b.平均工资;5. 显示与30部门任何雇员薪水及岗位相匹配的雇员的姓名、部门编号、薪水及佣金。SELECT ename,deptno,sal,comm FROM empWHERE sal IN(SELECT sal FROM emp WHERE deptno = 30)AND job IN(SELECT job FROM emp WHERE deptno=30)and deptno30 6. 查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，用两种方法实现。显示效果形如下：SNAME SAGE- -张军 21select sname,sagefrom studentwhere sageany (select sage from student where sdept=CS)and sdeptCS select sname,sagefrom studentwhere sage (select min(sage) from student where sdept=CS)and sdeptCS 7. *查询至少选修了学生95002选修的全部课程的学生学号(不包括95002学生本身)。显示效果形如下： SNO- 95001select distinct snofrom sc scxwhere not exists (select * from sc scy where scy.sno=95002 and not exists (select * from sc scz where scz.sno=scx.sno and o=o and scx.sno95002)8. 返回雇员号最小的10个员工信息。SELECT rownum,a.*FROM (SELECT * FROM EMP ORDER BY empno) aWHERE rownum=109. *查询工资第3低的员工的工号、姓名和薪水（假设SAL子段作了惟一限制。）（提示：使用from子查询语句、内嵌视图和ROWNUM伪列）显示效果形如下： EMPNO ENAME SAL- - - 7876 ADAMS 1100已选择 1 行。select * from (SELECT a.* FROM (SELECT EMPNO,ENAME,SAL FROM E