数据库实验三分组查询和嵌套查询(河南科技大学).pdf

《数据库实验三分组查询和嵌套查询(河南科技大学).pdf》由会员分享，可在线阅读，更多相关《数据库实验三分组查询和嵌套查询(河南科技大学).pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验三：分组查询和嵌套查询(一)实验目的：熟练掌握用SQL 语句实现数据库和基本表的创建、数据的更新。(二)实验内容：(一)分组查询：1.求各种颜色零件的平均重量。2.求北京供应商和天津供应商的总个数。3.求各供应商供应的零件总数。4.求各供应商供应给各工程的零件总数。5.求使用了100 个以上P1 零件的工程名称。6.求各工程使用的各城市供应的零件总数。（二）嵌套查询：1.in 连接谓词查询：查询没有使用天津供应商供应的红色零件的工程名称。查询供应了1000 个以上零件的供应商名称。(having)2.比较运算符：求重量大于所有零件平均重量的零件名称。3.Exists 连接谓词：查询供应J1

2、 的所有的零件都是红色的供应商名称。至少用了供应商S1 所供应的全部零件的工程号JNO。(三)完成情况：(一)分组查询：1.求各种颜色零件的平均重量。select color,AVG(weight)from p group by color 2.求北京供应商和天津供应商的总个数。select count(city)from s where city=北京 or city=天津 3.求各供应商供应的零件总数。select sno,SUM(qty)from spj group by sno 4.求各供应商供应给各工程的零件总数。select sno,jno,SUM(qty)from spj gro

3、up by sno,jno 5.求使用了100 个以上P1 零件的工程名称（不用在pno=p1 时，再对qty求和，系统会自动求和）。select jname from j where jno in (select jno from spj where pno=p1 and qty100 )6.求各工程使用的各城市供应的零件总数。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 （二）嵌套查询：1.in 连接谓词查询：查询没有使用天

4、津供应商供应的红色零件的工程名称。select jname from j where jno not in (select jno from spj,s,p where s.sno=spj.sno and p.pno=spj.pno and s.city=天津 and p.color=红 )查询供应了1000 个以上零件的供应商名称。(having)select sname from s where sno in (select sno from spj group by sno having sum(qty)1000 )2.比较运算符：求重量大于所有零件平均重量的零件名称。select pn

5、ame from p where weight (select AVG(weight)from p )3.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

6、 where spjy.sno=s1 and not exists (select*from spj spjz where spjz.pno=spjy.pno and spjz.jno=spjx.jno and spjz.sno=s1 )(四)实验结果：(一)分组查询：1.求各种颜色零件的平均重量。2.求北京供应商和天津供应商的总个数。3.求各供应商供应的零件总数。4.求各供应商供应给各工程的零件总数。5.求使用了100 个以上P1 零件的工程名称。6.求各工程使用的各城市供应的零件总数。（二）嵌套查询：1.in 连接谓词查询：查询没有使用天津供应商供应的红色零件的工程名称。查询供应了1000

7、 个以上零件的供应商名称。(having)3.比较运算符：求重量大于所有零件平均重量的零件名称。3.Exists 连接谓词：查询供应J1 的所有的零件都是红色的供应商名称。至少用了供应商S1 所供应的全部零件的工程号JNO。(五)问题及解决：1、求使用了100 个以上P1 零件的工程名称。不用在pno=p1 时，再对qty求和，系统会自动求和。(六)思考题：（一）嵌套查询中的in 连接谓词查询，关键字in 可以直接用any 代替么？什么情况下in 和 any 可以互相代替？关键字in 不可以直接用any 代替。=Any 等价于 In 为此（二）嵌套查询中的内查询为外查询返回的内容是什么？是表达式？视图？还是物理数据集合？在嵌套查询中，子查询的结果往往是一个集合（带in、any 的嵌套查询），但是带有 Exists 谓词的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑真值“false”。(七)实验总结：带 exists 的语句不好理解，还有表的自身连接没有掌握住，还需要下去看书理解。