13141实验2 连接查询,集合运算,子查询,TOP-N查询.doc

《数据库开发技术》课程之实验二 实验目的 掌握分组查询，连接查询和集合运算的使用方法。 掌握子查询及内嵌视图的使用方法。 掌握多列子查询和相关子查询的使用。 掌握TOP-N分析方法。 掌握Oracle常用单行函数的使用方法。 了解数据类型隐式转换的原则，熟练掌握显式转换函数的使用方法； 掌握Oracle组函数的正确使用方法。 掌握数据分级统计的正确使用方法与技巧。 实验内容和要求 第一部分：连接查询，集合运算，子查询和TOP-N分析 (实验用的数据表在本文档的最后面) *查询使用红色零件的工程名称。(考核多表（三表）连接操作) (分别使用相关的子查询、带in谓词的子查询、带exists谓词的子查询和连接查询实现。注意体会不同实现方式的区别。) /* 连接查询 */ SELECT DISTINCT SNAME FROM S,SPJ,P WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND P.COLOR=红 /*三层带IN谓词的嵌套查询 */ SELECT SNAME FROM S WHERE SNO IN (SELECT SPJ.SNO FROM SPJ WHERE SPJ.PNO IN (SELECT P.PNO FROM P WHERE P.COLOR=红)) /* 带exists谓词的子查询 */ SELECT SNAME FROM S WHERE EXISTS (SELECT S.SNO FROM SPJ,P WHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND COLOR=红) /* 相关子查询 */ SELECT SNAME FROM S WHERE S.SNO IN (SELECT SPJ.SNO FROM SPJ,P WHERE SPJ.PNO=P.PNO AND P.COLOR=红) 查询每一种零件被供应的次数，要求： 1）结果显示零件号、零件名称和被供应的次数。 SELECT P.PNO,PNAME,COUNT(SPJ.PNO) FROM P,SPJ WHERE P.PNO=SPJ.PNO GROUP BY P.PNO,PNAME 2）零件表中的所有零件都要统计。 显示效果形如下： PNO PNAME COUNT_P --- ---------- -------------- P1 螺母 4 P2 螺栓 2 P3 螺丝刀 5 P4 螺丝刀 0 P5 凸轮 3 P6 齿轮 3 P7 把手 0 已选择7行。 问题1？用GROUP BY P.PNO还是SPJ.PNO? Answer: P.PNO 问题2？GROUP BY P.PNO和GROUP BY P.PNO, PNAME结果是否一样？ Answer: 因为要显示零件名，所以PNAME不可或缺。 问题3？用COUNT(SPJ.PNO)还是COUNT(P.PNO) Answer: COUNT(SPJ.PNO),因为供应是在SPJ表中。 问题4？如果查询结果不要求显示零件号，只要求显示零件名称和数量，以下这个SQL语句对吗？ SELECT PNAME, COUNT(SPJ.PNO) FROM P, SPJ WHERE P.PNO = SPJ.PNO(+) GROUP BY PNAME; Answer: 零件名唯一且非空，正确；若不唯一，可能为空，则 GROUP BY P.PNO,PNAME; 查询student表中各系学生数占全校学生人数的百分比 显示效果形如下： SDEPT %Student ---------- ---------- CS 33.3 FL 22.2 IS 22.2 MA 22.2 已选择4行。 SELECT A.sdept, ROUND((A.s_sum / B.s_total)*100,1) “%Student” FROM (SELECT sdept, COUNT(sno) s_sum FROM student GROUP BY sdept) A, (SELECT COUNT(sno) s_total FROM student) B; SELECT sdept, ROUND