数据库作业10：第三章课后题

3. 有两个关系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 A,B
FROM S;

（3）$S\bowtie T$

SELECT *
FROM S,T
WHERE S.C=T.C AND S.D=T.D;

（4）$S\underset{S.C=TC}{\bowtie }T$

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

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

SELECT *
FROM S,T
WHERE S.A<T.E;

（6）${\Pi }_{C,D}(S)\times T$

SELECT S.C,S.D,T.*
FROM S,T;

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

--S表
 CREATE TABLE S(SNO CHAR(5), SNAME CHAR(5), STATUS INT, CITY CHAR(5));
 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(5),PNAME CHAR(5), COLOR CHAR(5), WEIGHT INT);
 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(5),JNAME CHAR(10),CITY CHAR(5)); 
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(5), PNO CHAR(5), JNO CHAR(5),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); 

（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：

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

（4）求没有使用天津供应商生产的红色零件的工程号JNO：（已经使用的除掉没有使用的）

SELECT JNO
FROM SPJ
WHERE JNO NOT IN(SELECT JNO
                 FROM SPJ,P,S
                 WHERE S.CITY='天津' AND COLOR='红' AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)

（5）求至少使用了供应商S1所供应的全部零件的工程号JNO：

SELECT DISTINCT  JNO
FROM SPJ X
WHERE NOT EXISTS(
 SELECT *
 FROM SPJ Y
 WHERE Y.SNO = 'S1' AND
 NOT EXISTS(
  SELECT *
  FROM SPJ Z
  WHERE Z.PNO = Y.PNO AND Z.JNO = X.JNO));

5. 针对上一题的四个表试用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 SPJ INNER JOIN P ON P.PNO = SPJ.PNO AND
SPJ.JNO = 'J2'

（5）找出上海厂商供应的所有零件号码

SELECT DISTINCT SPJ.PNO
FROM SPJ
WHERE SNO IN
 (SELECT SNO
 FROM S
 WHERE CITY = '上海')

（6） 找出使用上海产的零件的工程名称

SELECT JNAME
FROM J INNER JOIN SPJ ON J.JNO = SPJ.JNO
WHERE SPJ.SNO IN
 (SELECT SNO
 FROM S
 WHERE CITY = '上海')

（7）找出没有使用天津产的零件的工程号码

SELECT DISTINCT JNO
FROM SPJ
WHERE SNO NOT IN
 (SELECT SNO
 FROM S
 WHERE CITY = '天津')

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

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

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

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

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

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

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

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

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

AS SELECT SNO,PNO,QTY 
FROM SPJ,J  
WHERE SPJ.JNO=J.JNO AND J.JNAME='三建'

针对该视图完成下列查询：
（1）找出三建工程项目使用的各种零件代码及其数量

SELECT DISTINCT PNO,QTY
FROM V

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

SELECT *
FROM V
WHERE SNO = 'S1'

ps：