第三章课后习题

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 DISTINCT A,B
FROM S;

(3)S⋈T

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 }C$

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

(5)$S\underset{A<E}{\bowtie }C$

SELECT *
FROM S,T
WHERE A < E;

(6)${\Pi }_{C,D}(S)$ × T

笛卡尔积

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

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

有4个表
S(SNO,SNAME,STATUS,CITY);
P(PNO,PNAME,COLOR,WEIGHT);
J(JNO,JNAME,CITY);
SPJ(SNO,PNO,JNO,QTY);

S表：

CREATE TABLE S
(SNO CHAR(10) PRIMARY KEY,
SNAME CHAR(20),
STATUS INT,
CITY CHAR(20)
);

INSERT 
INTO S
VALUES('S1','精益'，20,'天津');

INSERT
INTO S
VALUES('S2','盛锡'，10,'北京');

INSERT
INTO S
VALUES('S3','东方红'，30,'北京');

INSERT
INTO S
VALUES('S4','丰泰盛'，20,'天津');

INSERT
INTO S
VALUES('S5','为民'，30,'上海');

P表：

CREAT TABLE P
(PNO CHAR(10) PRIMARY KEY,
PNAME CHAR(20),
COLOE CHAR(10),
WEIGHT INT
);

INSERT 
INTO P
VALUES('P1','螺母','红'，12);

INSERT 
INTO P
VALUES('P2','螺栓','绿'，17);

INSERT 
INTO P
VALUES('P3','螺丝刀','蓝'，14);

INSERT 
INTO P
VALUES('P4','螺丝刀','红'，14);

INSERT 
INTO P
VALUES('P5','凸轮','蓝'，40);

INSERT 
INTO P
VALUES('P6','齿轮','红'，30);

J表：

CREAT TABLE J
(JNO CHAR(10) PRIMARY KEY,
JNAME CHAR(20),
CITY CHAR(20)
);

INSERT 
INTO
VALUES('J1','三建','北京');

INSERT 
INTO
VALUES('J2','一汽','长春');

INSERT 
INTO
VALUES('J3','弹簧厂','天津');

INSERT 
INTO
VALUES('J4','造船厂','天津');

INSERT 
INTO
VALUES('J5','机车厂','唐山');

INSERT 
INTO
VALUES('J6','无线电厂','常州');

INSERT 
INTO
VALUES('J7','半导体厂','南京');

SPJ表：

CREAT TABLE SPJ
(SNO CHAR(5) PRIMARY KEY,
PNO CHAR(5),
JNO CHAR(5),
QTY SMALLINT
);

INSERT 
INTO SPJ
VALUES('S1','P1','J1',200);

INSERT 
INTO SPJ
VALUES('S1','P1','J3',100);

INSERT 
INTO SPJ
VALUES('S1','P1','J4',700);

INSERT 
INTO SPJ
VALUES('S1','P2','J2',100);

INSERT 
INTO SPJ
VALUES('S2','P3','J1',400);

INSERT 
INTO SPJ
VALUES('S2','P3','J2',200);

INSERT 
INTO SPJ
VALUES('S2','P3','J4',500);

INSERT 
INTO SPJ
VALUES('S2','P3','J5',400);

INSERT 
INTO SPJ
VALUES('S2','P5','J1',400);

INSERT 
INTO SPJ
VALUES('S2','P5','J2',100);

INSERT 
INTO SPJ
VALUES('S3','P1','J1',200);

INSERT 
INTO SPJ
VALUES('S3','P3','J1',200);

INSERT 
INTO SPJ
VALUES('S4','P5','J1',100);


INSERT 
INTO SPJ
VALUES('S4','P6','J3',300);

INSERT 
INTO SPJ
VALUES('S4','P6','J4',200);


INSERT 
INTO SPJ
VALUES('S5','P2','J4',100);


INSERT 
INTO SPJ
VALUES('S5','P3','J1',200);

INSERT 
INTO SPJ
VALUES('S5','P6','J2',200);

INSERT 
INTO SPJ
VALUES('S5','P6','J4',500);

好长的表！！终于建完啦！！

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

在SPJ表中可以找到 J1 和 SNO

SELECT SNO
FRON SPJ
WHERE JNO = 'J1';

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

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

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

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

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

将四张表连接起来

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

这里只需要用到SPJ表，但需要用到多次，SPJ 表中的 供应商，PJ 表中的 零件号，PJ 表中的 工程号

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 = SPJY.JNO));

5. 针对习题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 S,SPJ
WHERE S.CITY = '上海' AND S.SNO = SPJ.SNO;

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

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

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

SELECT JNO
FROM J
WHERE NOT EXISTS(
      SELECT *
      FROM S,SPJ
      WHERE SPJ.SNO = S.SNO AND SPJ.JNO=J.JNO 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的记录，并从供应情况关系中删除相应的记录

从两个表中删除

DELECT 
FROM SPJ
WHERE SNO = 'S2';

DELECT 
FROM S
WHERE SNO = 'S2';

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

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

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

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

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

SELECT PNO,QTY
FROM VIEW1;

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

SELECT *
FROM VIEW1
WHERE SNO = 'S1';

有些“存在”的题还是不太能熟练掌握，还需要加强理解！