数据库l分组查询和嵌套查询.docx

1、数据库l分组查询和嵌套查询实验三：分组查询和嵌套查询一、 实验目的：熟练掌握用SQL语句实现多个数据表的分组查询和嵌套查询。二、 预习要求：实验前复习讲授过的有关分组查询和嵌套查询的知识，编写相应的SQL语句。三、 实验内容：(一) 分组查询：1. 求各种颜色零件的平均重量。Select AVG(WEIGHT)From PGroup by COLOR2. 求北京供应商和天津供应商的总个数。Select count(CITY)From Swhere CITY=北京or=天津3. 求各供应商供应的零件总数。Select SNO,SUM(QTY)From SPJGROUP BY SNO,JNO4.

2、求各供应商供应给各工程的零件总数。select SNO,JNO,sum(QTY)from SPJgroup by sno,jno5. 求使用了100个以上P1零件的工程名称。Select distinct JNAMEFrom J,SPJWhere QTY100 AND PNO=P1and J.JNO=SPJ.JNO6. 求各工程使用的各城市供应的零件总数。Select JNAME ,S.CITY,SUM(QTY)FROM S,SPJ,JWHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNOGROUP BY S.CITY,J.JNO,JNAME（二） 嵌套查询：1. in连接

3、谓词查询：1 查询没有使用天津供应商供应的红色零件的工程名称。 select distinct JNAMEfrom Jwhere JNO not in (select JNOfrom SPJ,P,Swhere S.CITY=天津AND COLOR=红AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)查询供应了1000个以上零件的供应商名称。(having)Select SNAMEFrom SWHERE SNO in (Select snoFrom SPJGroup by snoHaving sum (QTY)1000)2. 比较运算符：求重量大于所有零件平均重量的零件名称

4、。Select PNAMEFrom PWhere weight (select AVG (weight)From P)3. Exists连接谓词：1 查询供应J1的所有的零件都是红色的供应商名称。Select SNAMEFrom SWhere Exists (select *From P,SPJWhere P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO AND COLOR=红AND JNO=J1)2 至少用了供应商S1所供应的全部零件的工程号JNO。Select DISTINCT JNOFROM SPJ SPJxWHERE NOT EXISTS(SELECT *FROM SPJ

5、 SPJyWHERE SPJy.SNO=S1AND NOT EXISTS (SELECT*FROM SPJ SPJzWHERE SPJz.PNO=SPJy.PNO ANDSPJz.JNO=SPJx.JNO ANDSPJz.SNO=S1) 三、出现问题及其解决办法：1、对分组查询概念理解不清，总是写错语句。 解决办法：在同学的帮助下，我又重新看了一下课本，基本掌握吧。2、在做“至少用了供应商S1所供应的全部零件的工程号JNO。”时开始分了两张表，但总表达不了完整的意思。解决办法：最后想起了这里的表比书本上多了一张表，最后分成x y z三张表。四、 思考题：（1）嵌套查询中的in连接谓词查询，关键字in可以直接用any代替么？什么情况下in和any可以互相代替？答：关键字in不可以直接用any代替。但“=any”的等价于in（2）嵌套查询中的内查询为外查询返回的内容是什么？是表达式？视图？还是物理数据集合？答：含in，any的嵌套查询返回的结果常是一个集合，而还exists谓词的子查询不返回任何数据，只产生逻辑值“true”或“false”。五、实验总结：心得体会，建议等。 本次实验，使我对书本上的知识有了进一步的了解，尤其是分组查询那部分。只有自己亲自去做去实践，才能发现问题，自己解决问题，也只有这样才能对理论的知识掌握更牢固，理解更透彻。