数据库系统概论第三章课后题（2020.3.23作业）

1、有两个关系S（A,B,C,D)和T（C,D,E,F），写出与下列查询等价的SQL表达式：
(1) ${\sigma }_{A=10}(S)$

SELECT *
FROM S
WHERE A=10

（2）${\Pi }_{A,B}(S)$

SELECT DISTINCT A,B
FROM S

（3）S ⟗ T

--错误
SELECT A,B,C,D,E,F
FROM S,T
WHERE S.C=T.C AND S.D=T.D

这里我居然忘记了当出现相同属性名的时候要标注一下，S.C也好T.C也好
憨憨挠头，改正之后

SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.C=T.C AND S.D=T.D

(4)$S\underset{S.C=T.C}{\bowtie }T$

SELECT *
FROM S,T
WHERE S.C=T.C

（5）$S\underset{A<E}{\bowtie }T$

SELECT *
FROM S,T
WHERE A<E

（6）${\Pi }_{C,D}(S)$ x T
这个题我开始有点蒙，因为我忘记了投影$\Pi$ 这个是不是去掉重复行，

我会去看了一下PPT，看来$\Pi$会将CD重复的去掉
那么思路应该是，先将CD从S中投影出来

SELECT DISTINCT C,D
FROM S

再与T做笛卡尔积，我们发现这样是做不了的，我们需要将上一操作的结果保存为一个临时派生表S1如：

SELECT DISTINCT C,D
FROM S
AS S1

然后再用S1与T做笛卡尔积

SELECT S1.C,S1.D,T.C,T.D,E,F
FROM T,(SELECT DISTINCT C,D
		FROM S)
		AS S1

对答案，正确。
2、用SQL语句建立第二章习题6中的4个表；针对建立的4个表用SQL完成第二章习题6中的查询。

用SQL语句建立第二章习题6中的4个表；

先建立表，注明表名和列名以及类型。
S表：

CREATE TABLE S(Sno CHAR(10),
			   SNAME CHAR(10),
		       STATUS SMALLINT,
               CITY CHAR(10),
			   );
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
			   );
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)
	           );
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
	             );
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);

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

（1）求供应工程J1零件的供应商号码SNO；

SELECT SNO
FROM SPJ
WHERE JNO='J1';

（2）求供应工程J1零件P1的供应商号码SNO；

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

（3）求供应工程J1零件为红色的供应商号码 SNO；
首先JNO=‘J1’,COLOR=’ 红色’，肯定是WHERE的条件，零件J1那么我们需要将目光放到J表，SPJ也必不可少，因为SPJ是承载着三个表的联系。

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

（4）求没有使用天津供应商生产的红色零件的工程号JNO；
我做的：

SELECT JNO
FROM SPJ,S,J
WHERE NOT EXISTS(S.CITY='天津',J.COLOR='红色')

想的太太太简单了
这里看到‘’没有使用‘’这个字眼首先想到的就是NOT EXISTS.
这里可以理解为将所有的零件，供应商，工程项目连接在一起，然后按题目要求的条件去SELECT
1：拼在一起

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​​​);

2.SELECT

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。
这道题实在没什么思路
但有一点我觉得肯定要有一个嵌套循环，因为这里涉及到全部零件。

3、针对习题4中的4个表使用SQL完成以下各项操作：
（1）找出所有供应商的姓名和所在城市；

SELECT SNAME,CITY
FROM   S

（2）找出所有零件的名称、颜色、重量；

SELECT PNAME,COLOR,WEIGHT
FROM   P

（3）找出使用供应商S1所供应零件的工程号码；

SELECT JNO
FROM   SPJ
WHERE  SNO='S1'

（4）找出工程项目J2使用的各种零件的名称及其数量；

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

（5）找出上海厂商供应的所有零件号码；
这个时候肯定就不要重复的结果了

SELECT DISTINCT PNO
FROM   SPJ,S
WHERE  S.SNO=SPJ.SNO AND CITY='上海'

（6）找出使用上海产的零件的工程名称；
条件：S.CITY='上海'

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

==（7）找出没有使用天津产的零件的工程号码；
没有使用 关键字 NOT EXISTS

SELECT JNO
FROM   J
WHERE NOT EXISTS(SELECT *
                 FROM  SPJ,S
				 WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND S.CITY='天津')

（8）把全部红色零件的颜色改成蓝色；

UPDATE P
SET    COLOR='蓝'
WHERE  COLOR='红'

（9）由S5供给J4的零件P6改为由S3供应，请作必要的修改；

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

（10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录；

DELETE
FROM  SPJ
WHERE SNO='S2';
DELETE
FROM  S
WHERE SNO='S2'

（11）请将（S2，J6，P4，200）插入供应情况关系；

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

4、请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO)、零件代码（PNO)、供应商数量（QTY）。针对该视图完成下列查询：

先建立视图
包括SNO,PNO,QTY,那么SELECT表达式就应该包括这三个，条件：JNAME=‘三建’。

CREATE VIEW VIEW_SPJ
AS
SELECT SNO,PNO,QTY
FROM   SPJ
WHERE  JNO=(SELECT JNO
            FROM   J
			WHERE  JNAME='三建')

（1）找出三建工程项目使用的各种零件代码及其数量；

SELECT PNO,QTY
FROM   VIEW_SPJ

（2）找出供应商S1的供应情况；

SELECT *
FROM SPJVIEW
WHERE SNO='S1'

恩…标黄的题目都是做题过程中不会的或者是不是太熟练的
用了大概3个多小时。