【資料庫系統概論】作業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'