Transcript 《数据库系统概论》第一章作业

邓云



完成情况基本良好
大多数同学态度都很端正
实验之星：徐晨灿，陈佳威，丁迪童，史少晨，仇
浩波
30
40
35
30
25
20
15
10
5
0
38
39
24
25
19
20
15
15
10
11
2
12
9
5
0
A+
A
B+
B
A+
A
B
C
67人提交了报告，12人未提交
 未提交报告的同学：
王干，揭宇如，王庆一，李广耀，王嘉良，余岸轩，
丁海涛，安东，杜紫薇，彭芃，王灏

40
35
30
25
20
15
10
5
0
39
38
15
11
2
A+
A
B+
B
64人提交了报告，15人未提交，1人文件损坏
 未提交报告的同学：
王干，揭宇如，卓炜，王庆一，李广耀，王嘉良，余
岸轩，丁海涛，刘一鸣，邓捷，张微，梁雨诗，许
玉珏，王灏，汪洲
 提交作业文件损坏同学：陈晓钟

30
24
25
19
18
20
15
10
12
9
5
0
A+
A
B
C
CREARE TABLE S(
SNO VARCHAR(3) PRIMARY KEY ，
SNAME VARCHAR(24),
在SQLSERVER中，如果列名和关键字同名，则
须用“[]”括起来
[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)
)
注意添加外键约束

求供应工程J1零件P1的供应商号码
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)

求没有使用天津供应商生产的红色零件的工程号
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
没有考虑用了天津供应商提供的非红色的零件的工程和用了红色但非天津供应商提供的零件的工程

求被供应零件P1的平均数量大于供应给工程J1的任
意零件的最大数量的工程号
嵌套查询和被嵌套查询的关联一定要记得描述
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)

求被供应零件P1的平均数量大于供应给工程J1的任
意零件的最大数量的工程号
SELECT DISTINCT SNO FROM SPJ T1
WHERE
PNO='P1‘
AND
QTY > (
SELECT AVG(QTY) FROM SPJ T2
WHERE
T2.JNO=T1.JNO
AND
PNO='P1‘
)
结果：空

求至少有一个供应商、零件或工程所在的城市
SELECT CITY FROM S UNION SELECT CITY FROM J
结果：(北京, 长春, 常州, 南京, 上海, 唐山, 天津)
语义描述：
在一个集合A中寻在满足如下条件的元组，该元组在关系C中跟集合B的所有元
组都有关系
result
A
B
a2
a1
b1
a2
b2
…
…
an
bm
C
AID
a1.id
a2.id
a2.id
a2.id
an.id
an.id
BID
b1.id
b1.id
b2.id
bm.id
b2.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表示形式：
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
此处默认C中C.BID存在外键引用B.ID。
(SELECT COUNT(*) FROM B)
如果没有则需要再添加什么条件呢？
=
(SELECT COUNT(DISTINCT C.BID) FROM C WHERE C.AID=A.ID)
SELECT a FROM A WHERE NOT EXISTS
(
(SELECT B.ID FROM B)
EXCEPT
(SELECT C.BID FROM C WHERE C.AID=A.ID)
)

求至少用了供应商S1所供应的全部零件的工程号
SELECT
JNO FROM J T1 WHERE NOT EXISTS
(SELECT * FROM SPJ T2 WHERE T2.SNO=‘S1’ AND
NOT EXISTS( SELECT * FROM SPJ WHERE T2.PNO = SPJ.PNO AND T1.JNO = SPJ.JNO)
)
结果：(J4)

求对所有工程都提供了同一零件的供应商号码
(零件，供应商，工程) / (工程)
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)
)
结果：空

求供应商号码对，其中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)
)
AND
第二列供应商提供了了第一列供应商提
供的所有零件
NOT EXISTS(
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)
)
结果：空

将所有工程中红色零件的使用数量加100
UPDATE SPJ
SET QTY=QTY+100
WHERE PNO in(
SELECT PNO FROM P
WHERE COLOR='红‘
)

删除工程J1和J2都使用的零件及相关记录
步骤：
1.
2.
3.
4.
创建临时表，将J1和J2都是用的零件插入临时表中
删除SPJ表中相关记录
删除P表中相关记录
删除临时表
CREATE TABLE #TMP(
第一步和第二步也可以用一句select
PNO VARCHAR(3)
into语句完成。
);
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
PNO IN (SELECT * FROM #TMP);
DROP TABLE #TMP;