数据库l_分组查询和嵌套查询.docVIP

实验三：分组查询和嵌套查询 实验目的：熟练掌握用SQL语句实现多个数据表的分组查询和嵌套查询。 预习要求：实验前复习讲授过的有关分组查询和嵌套查询的知识，编写相应的SQL语句。 实验内容： 分组查询： 求各种颜色零件的平均重量。 Select AVG(WEIGHT) From P Group by COLOR 求北京供应商和天津供应商的总个数。 Select count(CITY) From S where CITY=北京or=天津’ 求各供应商供应的零件总数。 Select SNO,SUM(QTY) From SPJ GROUP BY SNO,JNO 求各供应商供应给各工程的零件总数。 select SNO,JNO,sum(QTY) from SPJ group by sno,jno 求使用了100个以上P1零件的工程名称。 Select distinct JNAME From J,SPJ Where QTY100 AND PNO=P1and J.JNO=SPJ.JNO 求各工程使用的各城市供应的零件总数。 Select JNAME ,S.CITY,SUM(QTY) FROM S,SPJ,J WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO GROUP BY S.CITY,J.JNO,JNAME （二） 嵌套查询： in连接谓词查询： 查询没有使用天津供应商供应的红色零件的工程名称。 select distinct JNAME from J where JNO not in (select JNO from SPJ,P,S where S.CITY=天津AND COLOR=红AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO) 查询供应了1000个以上零件的供应商名称。(having) Select SNAME From S WHERE SNO in ( Select sno From SPJ Group by sno Having sum (QTY)1000 ) 比较运算符：求重量大于所有零件平均重量的零件名称。 Select PNAME From P Where weight (select AVG (weight) From P) Exists连接谓词： 查询供应J1的所有的零件都是红色的供应商名称。 Select SNAME From S Where Exists ( select * From P,SPJ Where P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO AND COLOR=红 AND JNO=J1 ) 至少用了供应商S1所供应的全部零件的工程号JNO。 Select DISTINCT JNO FROM SPJ SPJx WHERE NOT EXISTS ( SELECT * FROM SPJ SPJy WHERE SPJy.SNO=S1AND NOT EXISTS ( SELECT* FROM SPJ SPJz WHERE SPJz.PNO=SPJy.PNO AND SPJz.JNO=SPJx.JNO AND SPJz.SNO=S1 ) ) 三、出现问题及其解决办法： 对分组查询概念理解不清，总是写错语句。 解决办法：在同学的帮助下，我又重新看了一下课本，基本掌握吧。 2、在做“至少用了供应商S1所供应的全部零件的工程号JNO。” 时开始分了两张表，但总表达不了完整的意思。 解决办法：最后想起了这里的表比书本上多了一张表，最后分成x y z三张表。 思考题： 嵌套查询中的in连接谓词查询，关键字in可以直接用any代替么？什么情况下in和any可以互相代替？ 答：关键字in不可以直接用any代替。但“=any”的等价于in 嵌套查询中的内查询为外查询返回的内容是什么？是表达式？视图？还是物理数据集合？ 答：含in，any的嵌套查询返回的结果常是一个集合，而还exists谓词的子查询不返回任何数据，只产生逻辑值“true”或“false”。 五、实验总结：心得体会，建议等。 ? 本次实验，使我对书本上的知识有了进一步的了解，尤其是分组查询那部分。只有自己亲自去做去实践，才能发现问题，自己解决问题，也只有这样才能对理论的知识掌握更牢固，理解更透彻。 10