【数据库系统概论】作业3 第三章 习题4|5|9

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

S表

P表

J表

SPJ表

CREATE TABLE S(
  SNO CHAR(3) PRIMARY KEY,
  SNAME CHAR(10),
  STATUS CHAR(2),
  CITY CHAR(10)
);

CREATE TABLE P(
  PNO CHAR(3),
  PNAME CHAR(10),
  COLOR CHAR(4),
  WEIGHT INT,
  PRIMARY KEY(PNO)
);

CREATE TABLE J(
  JNO CHAR(3),
  JNAME CHAR(10),
  CITY CHAR(10),
  PRIMARY KEY(JNO)
);

CREATE TABLE SPJ(
  SNO CHAR(3),
  PNO CHAR(3),
  JNO CHAR(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)
);
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

INSERT INTO S(SNO,SNAME,STATUS,CITY) 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','上海');

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

INSERT INTO J VALUES('J1','三建','北京');
INSERT INTO J VALUES('J2','一汽','长春');
INSERT INTO J VALUES('J3','弹簧厂','天津');
INSERT INTO J VALUES('J4','造船厂','天津');
INSERT INTO J VALUES('J5','机车厂','唐山');
INSERT INTO J VALUES('J6','无限电厂','常州');
INSERT INTO J VALUES('J7','半导体厂','南京');

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

供应商表S，供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）

零件表P，零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）

工程项目表J，工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）

供应情况表SPJ，供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（OTY）

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

SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1';
1

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

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

（3）求供应工程J1零件为红色的供应商号码SNO；

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

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

SELECT DISTINCT S.SNO FROM P,S,SPJ WHERE P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO AND S.CITY!='天津';
1

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

SELECT DISTINCT JNO FROM SPJ SPJZ WHERE NOT EXISTS(SELECT * FROM SPJ WHERE SNO='S1' AND NOT EXISTS (SELECT * FROM SPJ SPJY WHERE SPJY.PNO=SPJZ.PNO AND SPJY.JNO=SPJZ.JNO));
1

5、针对习题4中的4个表试用SQL完成以下各项操作：

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

SELECT SNAME,CITY FROM S;
1

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

SELECT PNAME,COLOR,WEIGHT FROM P;
1

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

SELECT DISTINCT PNO FROM SPJ WHERE SNO='S1';
1

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

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

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

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

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

SELECT DISTINCT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND CITY='上海';
1

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

SELECT DISTINCT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND CITY!='天津';
1

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

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

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

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

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

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

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

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

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

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

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

SELECT PNO,QTY FROM V_SPJ;
1

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

SELECT * FROM V_SPJ WHERE SNO='S1'
1