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

文章目录

• • 4、用SOL语句建立第2章习题6中的4个表，针对建立的4个表用SQL完成第2章习题6中的查询。
  • • （1）求供应工程J1零件的供应商号码SNO；
    • （2）求供应工程J1零件P1的供应商号码SNO；
    • （3）求供应工程J1零件为红色的供应商号码SNO；
    • （4）求没有使用天津供应商生产的红色零件的工程号JNO；
    • （5）求至少用了供应商S1所供应的全部零件的工程号JNO。
  • 5、针对习题4中的4个表试用SQL完成以下各项操作：
  • • (1）找出所有供应商的姓名和所在城市；
    • (2）找出所有零件的名称、颜色、重量；
    • (3）找出使用供应商S1所供应零件的工程号码；
    • (4）找出工程项目J2使用的各种零件的名称及其数量；
    • (5）找出上海厂商供应的所有零件号码；
    • (6）找出使用上海产的零件的工程名称；
    • (7）找出没有使用天津产的零件的工程号码；
    • (8）把全部红色零件的颜色改成蓝色；
    • (9）由 S5供给J4的零件P6改为由S3供应，请作必要的修改；
    • (10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录；
    • (11）请将(S2,J6,P4,200)插入供应情况关系。
  • 9、请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询：
  • • （1）找出三建工程项目使用的各种零件代码及其数量。
    • （2）找出供应商S1的供应情况。

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

S表

SNO	SNAME	STATUS	CITY
S1	精益	20	天津
S2	盛锡	10	北京
S3	东方红	30	北京
S4	丰泰盛	20	天津
S5	为民	30	上海

P表

PNO	PNAME	COLOR	WEIGHT
P1	螺母	红	12
P2	螺栓	绿	17
P3	螺丝刀	蓝	14
P4	螺丝刀	红	14
P5	凸轮	蓝	40
P6	齿轮	红	30

J表

JNO	JNAME	CITY
J1	三建	北京
J2	一汽	长春
J3	弹簧厂	天津
J4	造船厂	天津
J5	机车厂	唐山
J6	无线电厂	常州
J7	半导体厂	南京

SPJ表

SNO	PNO	JNO	QTY
S1	P1	J1	200
S1	P1	J3	100
S1	Pl	J4	700
S1	P2	J2	100
S2	P3	J1	400
S2	P3	J2	200
S2	P3	J4	500
S2	P3	J5	400
S2	P5	J1	400
S2	P5	J2	100
S3	Pl	JI	200
S3	P3	J1	200
S4	P5	J1	100
S4	P6	J3	300
S4	P6	J4	200
S5	P2	J4	100
S5	P3	J1	200
S5	P6	J2	200
S5	P6	J4	500

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

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

供应商表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';

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

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

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

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

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

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

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

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

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

SELECT SNAME,CITY FROM S;

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

SELECT PNAME,COLOR,WEIGHT FROM P;

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

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

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

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

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

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

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

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

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

SELECT DISTINCT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND 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(SNO,PNO,JNO,QTY) VALUES('S2','J6','P4',200);

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）找出三建工程项目使用的各种零件代码及其数量。

SELECT PNO,QTY FROM V_SPJ;

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

SELECT * FROM V_SPJ WHERE SNO='S1'