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

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

2、供应商名称。至少用了供应商S1所供应的全部零件的工程号JNO。(三完成情况:(一 分组查询:1.求各种颜色零件的平均重量。select color,A VG(weightfrom pgroup by color2.求北京供应商和天津供应商的总个数。select count(cityfrom swhere city='北京'or city='天津'3.求各供应商供应的零件总数。select sno,SUM(qtyfrom spjgroup by sno4.求各供应商供应给各工程的零件总数。select sno,jno,SUM(qtyfrom spjgroup by

3、 sno,jno5.求使用了100个以上P1零件的工程名称(不用在pno=p1时,再对qty 求和,系统会自动求和。select jnamefrom jwhere jno in(select jnofrom spjwhere pno='p1'andqty>1006.求各工程使用的各城市供应的零件总数。select jname,s.city,sum(qtyfrom s,spj,jwhere s.sno=spj.sno andj.jno=spj.jnogroup by s.city,j.jno,jname(二嵌套查询:1.in连接谓词查询:查询没有使用天津供应商供应的红色零件

4、的工程名称。select jnamefrom jwhere jno not in(select jnofrom spj,s,pwheres.sno=spj.sno andp.pno=spj.pno ands.city='天津'andp.color='红'查询供应了1000个以上零件的供应商名称。(havingselect snamefrom swhere sno in(select snofrom spjgroup by snohaving sum(qty>10002.比较运算符:求重量大于所有零件平均重量的零件名称。select pnamefrom pw

5、here weight>(select A VG(weightfrom p3.Exists连接谓词:查询供应J1的所有的零件都是红色的供应商名称。select snamefrom swhere exists(select*from p,spjwhere p.pno=spj.pno ands.sno=spj.sno andcolor='红'andjno='j1'至少用了供应商S1所供应的全部零件的工程号JNO。select distinct jnofrom spj spjxwhere not exists(select*from spj spjywhere

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

7、应了 1000 个以上零件的供应商名称。(having 3.比较运算符：求重量大于所有零件平均重量的零件名称。 3.Exists 连接谓词： 查询供应 J1 的所有的零件都是红色的供应商名称。 至少用了供应商 S1 所供应的全部零件的工程号 JNO。 (五 问题及解决： 问题及解决 1、求使用了 100 个以上 P1 零件的工程名称。不用在 pno=p1 时，再对 qty 求和，系统会自动求和。 (六 思考题： 思考题： 关键字 in 可以直接用 any 代替么？什 （一）嵌套查询中的 in 连接谓词查询， 么情况下 in 和 any 可以互相代替？ 关键字 in 不可以直接用 any 代替。=Any 等价于 In 为此 （二）嵌套查询中的内查询为外查询返回的内容是什么？是表达式？视图？ 还是物理数据集合？ 在嵌套查询中，子查询的结果往往是一个集合（带 in、any 的嵌套查询）