实验二 交互式SQL 邓云

实验内容概况 完成情况基本良好 大多数同学态度都很端正 实验之星：徐晨灿，陈佳威，丁迪童，史少晨，仇 浩波 39

实验2.1 67人提交了报告，12人未提交 未提交报告的同学： 王干，揭宇如，王庆一，李广耀，王嘉良，余岸轩， 丁海涛，安东，杜紫薇，彭芃，王灏 39

实验2.2 64人提交了报告，15人未提交，1人文件损坏 未提交报告的同学： 王干，揭宇如，卓炜，王庆一，李广耀，王嘉良，余 岸轩，丁海涛，刘一鸣，邓捷，张微，梁雨诗，许 玉珏，王灏，汪洲 提交作业文件损坏同学：陈晓钟 18

建表及数据插入 在SQLSERVER中，如果列名和关键字同名，则 须用“[]”括起来 注意添加外键约束 CREARE TABLE S( SNO VARCHAR(3) PRIMARY KEY ， SNAME VARCHAR(24), [STATUS] INT, CITY VARCHAR(24) ) CREARE TABLE SPJ( SNO VARCHAR(3), PNO VARCHAR(3), JNO VARCHAR(3), QTY INT, PRIMARY KEY (SNO,PNO,JNO), FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO) 在SQLSERVER中，如果列名和关键字同名，则 须用“[]”括起来 注意添加外键约束

SQL查询 求供应工程J1零件P1的供应商号码 求供应工程J1零件为红色的供应商号码，并按其供 应数量之和降序排列显示 SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1' 结果：(S1,S3) 求供应工程J1零件为红色的供应商号码，并按其供 应数量之和降序排列显示 SELECT SNO FROM SPJ, P WHERE SPJ.PNO = P.PNO AND P.COLOR='红' AND JNO='J1' GROUP BY SNO ORDER BY SUM(QTY) DESC; 结果：(S1,S3)

SQL查询 求没有使用天津供应商生产的红色零件的工程号 结果：(J2, J5, J6, J7) SELECT JNO FROM J WHERE JNO NOT IN ( SELECT JNO FROM SPJ, P, S WHERE SPJ.PNO=P.PNO AND SPJ.SNO=S.SNO AND S.CITY='天津' AND P.COLOR='红‘ ) 结果：(J2, J5, J6, J7) SELECT JNO FROMS,P,SPJ WHERE S.CITY != '天津' AND COLOR!='红' AND P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO 没有考虑用了天津供应商提供的非红色的零件的工程和用了红色但非天津供应商提供的零件的工程

SQL查询 求被供应零件P1的平均数量大于供应给工程J1的任 意零件的最大数量的工程号 SELECT JNO FROM J T1 嵌套查询和被嵌套查询的关联一定要记得描述 SELECT JNO FROM J T1 WHERE (SELECT AVG(QTY) FROM SPJ WHERE PNO='P1' AND JNO=T1.JNO) > (SELECT MAX(T2.QTY) FROM SPJ T2 WHERE JNO='J1') 结果：(J4)

SQL查询 求被供应零件P1的平均数量大于供应给工程J1的任 意零件的最大数量的工程号 结果：空 SELECT DISTINCT SNO FROM SPJ T1 WHERE PNO='P1‘ AND QTY > ( SELECT AVG(QTY) FROM SPJ T2 T2.JNO=T1.JNO ) 结果：空

SQL查询 求至少有一个供应商、零件或工程所在的城市 SELECT CITY FROM S UNION SELECT CITY FROM J 结果：(北京, 长春, 常州, 南京, 上海, 唐山, 天津)

SQL查询——除法 语义描述： 在一个集合A中寻在满足如下条件的元组，该元组在关系C中跟集合B的所有元 组都有关系 普通的SQL表示形式： result A B C a2 a1 b1 AID BID a2 b2 a1.id b1.id a2.id b1.id … … a2.id b2.id an bm a2.id bm.id an.id b2.id an.id bm.id … … 普通的SQL表示形式： SELECT a FROM A WHERE NOT EXISTS （SELECT * FROM B WHERE NOT EXISTS （ SELECT * FROM C WHERE C.AID=A.ID AND C.BID=B.ID ）

SQL查询——除法 文艺的SQL表示形式： SELECT a FROM A WHERE NOT EXISTS （SELECT * FROM B WHERE B.ID NOT IN ( SELECT C.BID FROM C WHERE C.AID=A.ID ） SELECT a FROM A WHERE (SELECT COUNT(*) FROM B) = (SELECT COUNT(DISTINCT C.BID) FROM C WHERE C.AID=A.ID) ( (SELECT B.ID FROM B) EXCEPT (SELECT C.BID FROM C WHERE C.AID=A.ID) ) 此处默认C中C.BID存在外键引用B.ID。 如果没有则需要再添加什么条件呢？

SQL查询 求至少用了供应商S1所供应的全部零件的工程号 结果：(J4) SELECT WHERE NOT EXISTS JNO FROM J T1 * FROM SPJ T2 WHERE T2.SNO=‘S1’ AND SELECT * FROM SPJ WHERE T2.PNO = SPJ.PNO AND T1.JNO = SPJ.JNO 结果：(J4)

SQL查询 求对所有工程都提供了同一零件的供应商号码 (零件，供应商，工程) / (工程) 结果：空 (零件，供应商，工程) / (工程) SELECT DISTINCT SNO FROM SPJ T1 WHERE NOT EXISTS (SELECT * FROM J WHERE NOT EXISTS (SELECT * FROM SPJ T3 WHERE T1.SNO = T3.SNO AND T1.PNO = T3.PNO AND J.JNO = T3.JNO) ) 结果：空

SQL查询 求供应商号码对，其中Sx和Sy供应的零件都相同 (SX.id，工程) / (SY供应零件的工程工程) (SY.id，工程) / (SX供应零件的工程工程) SELECT T1.SNO, T2.SNO FROM J T1, J T2 WHERE T1.SNO > T2.SNO AND NOT EXISTS( SELECT * FROM SPJ T3 WHERE T3.SNO=T2.SNO AND NOT EXISTS (SELECT * FROM SPJ T4 WHERE T4.PNO = T3.PNO AND T4.SNO=T1.SNO) ) SELECT * FROM SPJ T5 WHERE T5.SNO=T1.SNO AND NOT EXISTS (SELECT * FROM SPJ T6 WHERE T6.PNO = T5.PNO AND T6.SNO=T2.SNO) 第一列供应商提供了了第二列供应商提 供的所有零件 第二列供应商提供了了第一列供应商提 供的所有零件 结果：空

SQL更新 将所有工程中红色零件的使用数量加100 UPDATE SPJ SET QTY=QTY+100 WHERE PNO in( SELECT PNO FROM P WHERE COLOR='红‘ )

SQL删除与临时表 删除工程J1和J2都使用的零件及相关记录 步骤： 第一步和第二步也可以用一句select into语句完成。 删除SPJ表中相关记录 删除P表中相关记录 删除临时表 CREATE TABLE #TMP( PNO VARCHAR(3) ); INSERT INTO #TMP(PNO) SELECT DISTINCT X.PNO FROM SPJ X, SPJ Y WHERE X.PNO = Y.PNO AND X.JNO = 'J1' AND Y.JNO = 'J2'; DELETE FROM SPJ WHERE PNO IN (SELECT * FROM #TMP); DELETE FROM P WHERE DROP TABLE #TMP; 第一步和第二步也可以用一句select into语句完成。