本文所属《数据库系统原理》分类专栏,相关文章导航请点击《数据库系统原理》目录汇总贴


课后题3/4/5题目

3.8 SQL课后题3~5.png


第3题

(1)

1
2
3
SELECT *
FROM S
WHERE A='10'

(2)

1
2
SELECT A,B
FROM S

(3) S ⋈ T

1
2
3
4
-- 共同属性列是 C 和 D
SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.C=T.C AND S.D=T.D

(4)

1
2
3
SELECT *
FROM S,T
WHERE S.C=T.C

(5)

1
2
3
SELECT *
FROM S,T
WHERE S.A<T.E

(6)

1
2
SELECT S.C,S.D,T.*
FROM S,T

第4题

用SQL语句建立第二章习题6中的4个表;针对建立的4个表用SQL完成第二章习题6中的查询。

3.8 SQL课后题4(也是第二章课后题6)图1.png
3.8 SQL课后题4(也是第二章课后题6)图2.png
3.8 SQL课后题4(也是第二章课后题6)图3.png

【建表】:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
--建“供应商表 S”
CREATE TABLE S (SNO CHAR(4) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/
SNAME CHAR(20) UNIQUE, /* Sname取唯一值*/
STATUS SMALLINT, /* smallint是数据类型的短整型*/
CITY CHAR(20)
);

--在“供应商表 S”中插入数据
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES ('S1','精益',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S2','盛锡',10,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S3','东方红',30,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S4','丰泰盛',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S5','为民',30,'上海');

--建“零件表 P”
CREATE TABLE P (PNO CHAR(10),
PNAME CHAR(10),
COLOR CHAR(10),
WEIGHT SMALLINT
);

--在“零件表 P”中插入数据
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P1','螺母','红',12);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P2','螺栓','绿',17);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P4','螺丝刀','红',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P5','凸轮','蓝',40);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P6','齿轮','红',30);

--建“工程项目表 J”
CREATE TABLE J(JNO CHAR(10),
JNAME CHAR(10),
CITY CHAR(10)
);

--在“工程项目表 J”中插入数据
INSERT INTO J(JNO,JNAME,CITY) VALUES('J1','三建','北京');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J2','一汽','长春');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J3','弹簧厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J4','造船厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J5','机车厂','唐山');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J6','无线电厂','常州');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J7','半导体厂','南京');

--建“供应情况表 SPJ”
CREATE TABLE SPJ(SNO CHAR(4),
PNO CHAR(4),
JNO CHAR(4),
QTY SMALLINT
);

--在“供应情况表 SPJ”中插入数据
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J3',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J4',700);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P2','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J4',500);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J5',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P5','J1',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J3',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J4',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P2','J4',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J4',500);

上方那些代码的用法参考如下:

  1. 光标选中 S表 的建表代码,执行
  2. 光标选中 S表 的插入代码,执行
  3. 任意空白处输入下面查询语句,光标选中,执行
    1
    2
    SELECT *
    from S
    建完表后开始做题。

【查询】:
(1)求供应工程J1零件的供应商号码SNO;

1
2
3
SELECT SNO
FROM SPJ
WHERE JNO='J1';

(2)求供应工程J1零件P1的供应商号码SNO;

1
2
3
SELECT SNO
FROM SPJ
WHERE JNO='J1' AND PNO ='P1';

(3)求供应工程J1零件为红色的供应商号码 SNO;

1
2
3
SELECT SNO
FROM SPJ,P
WHERE P.PNO=SPJ.PNO AND JNO='J1' AND COLOR='红';

(4)求没有使用天津供应商生产的红色零件的工程号JNO;

1
2
3
4
5
6
SELECT JNO
FROM J
WHERE NOT EXISTS(
SELECT *
FROM SPJ,S,P
WHERE S.CITY='天津' AND P.COLOR='红' AND SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO​​​);

(5)求至少用了供应商S1所供应的全部零件的工程号JNO。

1
2
3
4
5
6
7
8
9
SELECT DISTINCT JNO
FROM SPJ SPJX
WHERE NOT EXISTS
(SELECT *
FROM SPJ SPJY
WHERE SPJY.SNO='S1'AND NOT EXISTS
(SELECT *
FROM SPJ SPJZ
WHERE SPJZ.PNO=SPJY.PNO AND SPJZ.JNO=SPJX.JNO​​​)​);​​​

第5题

针对习题4中的4个表使用SQL完成以下各项操作:

(1)找出所有供应商的姓名和所在城市;

1
2
SELECT SNO,CITY
FROM S

(2)找出所有零件的名称、颜色、重量;

1
2
SELECT PNAME,COLOR,WEIGHT
FROM P

(3)找出使用供应商S1所供应零件的工程号码;

1
2
3
SELECT JNO
FROM SPJ
WHERE SNO='S1'

(4)找出工程项目J2使用的各种零件的名称及其数量;

1
2
3
SELECT PNAME,QTY
FROM SPJ,P
WHERE JNO='J2' AND P.PNO=SPJ.PNO

(5)找出上海厂商供应的所有零件号码;

1
2
3
4
SELECT DISTINCT PNO		
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND CITY='上海'
-- 为什么这里要用DISTINCT消除取值重复行?因为查出结果会有三个P6

或者

1
2
3
4
5
6
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO IN(SELECT SNO
FROM S
WHERE CITY='上海'
);

(6)找出使用上海产的零件的工程名称;

1
2
3
SELECT DISTINCT JNAME 
FROM SPJ,S,J
WHERE S.SNO=SPJ.SNO AND S.CITY='上海' AND J.JNO=SPJ.JNO

1
2
3
4
5
SELECT JNAME 
FROM J
WHERE JNO IN(SELECT JNO
FROM SPJ, S
WHERE SPJ. SNO=S.SNO AND S.CITY='上海');

我的错误答案:

1
2
3
4
5
6
7
8
9
SELECT JNO	--查找对象都能看错
FROM SPJ
WHERE PNO IN (SELECT DISTINCT PNO
FROM SPJ
WHERE SNO IN(SELECT SNO
FROM S
WHERE CITY='上海'
)
);

(7)找出没有使用天津产的零件的工程号码;

1
2
3
4
5
6
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ,S
WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND S.CITY='天津')

1
2
3
4
5
6
7
8
9
SELECT JNO 
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ
WHERE SJP.JNO=J.JNO AND SNO IN
(SELECT SNO
FROM S
WHERE CITY='天津'))

我的错误答案:

1
2
3
SELECT DISTINCT PNO
FROM P,S,SPJ
WHERE P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO AND S.CITY= --写一半卡住

(8)把全部红色零件的颜色改成蓝色;

1
2
3
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红'

(9)由S5供给J4的零件P6改为由S3供应,请作必要的修改;

1
2
3
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6';

(10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录;

1
2
3
4
5
6
DELETE
FROM SPJ
WHERE SNO='S2';
DELETE
FROM S
WHERE SNO='S2'

(11)请将(S2,J6,P4,200)插入供应情况关系;

1
INSERT INTO SPJ VALUES('S2','P4','J6',200);

1
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P4','J6',200);

第9题

请为三建工程项目建立一个供应情况的视图,包括供应商代码(SNO)、零件代码(PNO)、供应商数量(QTY)。

1
2
3
4
5
6
7
CREATE VIEW VIEW_SPJ
AS
SELECT SNO,PNO,QTY
FROM SPJ
WHERE JNO=(SELECT JNO
FROM J
WHERE JNAME='三建')

针对该视图完成下列查询:

(1)找出三建工程项目使用的各种零件代码及其数量;

1
2
SELECT PNO,QTY
FROM VIEW_SPJ

(2)找出供应商S1的供应情况;

1
2
3
SELECT *
FROM VIEW_SPJ
WHERE SNO='S1'

答案参考:
[1] 第三章课后题 https://blog.csdn.net/weixin_44652687/article/details/105051082
[2] 第三章课后题 https://blog.csdn.net/MooM_X/article/details/105072849 - 主要参考
[3] 数据库第5版全书答案 https://blog.csdn.net/weixin_46037153/article/details/103936161