数据库习题练习2.6

发布于 2019-04-02  162 热度


设有一个SPJ数据库,包括S、P、J及SPJ4个关系模式:

S(SNO,SNAME,STATUS,CITY);
P(PNO,PNAME,COLOR,WEIGHT);
J(JNO,JNAME,CITY);
SPJ(SNO,PNO,JNO,QTY);
供应商表S由供应商代码(SNO)、零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。
零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。
工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,表示某供应商供应某种零件给某工程项目的数量为QTY。
今有若干数据如下:
SNO SNAME STATUS CITY
S1 精益 20 天津
S2 盛锡 10 北京
S3 东方红 30 北京
S4 丰泰盛 20 天津
S5 为民 30 上海
PNO PNAME COLOR WEIGHT
P1 螺母 12
P2 螺栓 绿 17
P3 螺丝刀 14
P4 螺丝刀 14
P5 凸轮 40
P6 齿轮 30
JNO JNAME CITY
J1 三建 北京
J2 一汽 长春
J3 弹簧厂 天津
J4 造船床 天津
J5 机车厂 唐山
J6 无线电厂 常州
J7 半导体厂 南京
SNO PNO JNO QTY
S1 P1 J1 200
S1 P1 J3 100
S1 P1 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 P1 J1 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(12) PRIMARY KEY,
	SNAME CHAR(20),
	STATUS SMALLINT,
	CITY CHAR(12)
);
CREATE TABLE P(
	PNO CHAR(12) PRIMARY KEY,
	PNAME CHAR(20),
	COLOR CHAR(12),
	WEIGHT INT
);
CREATE TABLE J(
	JNO CHAR(12) PRIMARY KEY,
	JNAME CHAR(20),
	CITY CHAR(12)
);
CREATE TABLE SPJ(
	SNO CHAR(12),
	PNO CHAR(12),
	JNO CHAR(12),
	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 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);



/***********题目:*************/
/*求供应工程J1零件的供应商号码SNO*/
SELECT SNO FROM SPJ WHERE JNO='J1';

/*求供应工程J1零件P1的供应商号码SNO*/
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1';

/*求供应工程J1零件为红色的供应商号码SNO*/
SELECT SNO FROM SPJ,P WHERE JNO='J1' AND P.PNO=SPJ.PNO AND COLOR='红';	/*方法1,多表连接查询*/
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO IN(SELECT PNO FROM P WHERE COLOR='红');	/*方法2,不相关子查询*/

/*求没有使用天津供应商生产的红色零件的工程号JNO*/
SELECT JNO FROM SPJ,S,P WHERE P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO AND CITY!='天津' AND COLOR='红';/*方法1,多表连接查询*/
SELECT JNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY!='天津') AND PNO IN(SELECT PNO FROM P WHERE COLOR='红');	/*方法2,不相关子查询*/

/*求至少用了供应商S1所供应的全部零件的工程号JNO*/
/*SELECT JNO FROM SPJ SPJ1 WHERE SNO='S1' AND NOT EXISTS (SELECT * FROM P WHERE NOT EXISTS (SELECT * FROM SPJ SPJ2 WHERE SPJ2.SNO='S1' AND P.PNO=SPJ2.PNO))*/

/*找出所有供应商的姓名和所在城市*/
SELECT SNAME,CITY FROM S;

/*找出所有零件的名称、颜色、重量*/
SELECT PNAME,COLOR,WEIGHT FROM P;

/*找出使用供应商S1所供应零件的工程号码*/
SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1';

/*找出工程项目J2使用的各种零件的名称及其数量*/
SELECT PNAME,WEIGHT FROM P,SPJ WHERE JNO='J2' AND P.PNO=SPJ.PNO;	/*方法1,多表连接*/
SELECT PNAME,WEIGHT FROM P WHERE PNO IN (SELECT PNO FROM SPJ WHERE JNO='J2');	/*方法2,不相关子查询*/

/*找出上海厂商供应的所有零件号码*/	
SELECT PNO FROM S,SPJ WHERE CITY='上海' AND S.SNO=SPJ.SNO;	/*方法1,多表连接*/
SELECT PNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='上海');	/*方法2,不相关子查询*/

/*找出使用上海产的零件的工程名称*/
SELECT DISTINCT JNAME FROM S,J,SPJ WHERE S.CITY='上海' AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO;	/*方法1,多表连接*/
SELECT DISTINCT JNAME FROM J WHERE JNO IN (SELECT JNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='上海')); 	/*方法2,不相关子查询*/

/*找出没有使用天津产的零件的工程号码*/
SELECT JNO FROM J WHERE JNO NOT IN (SELECT J.JNO FROM S,J,SPJ WHERE S.CITY='天津' AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO);	/*先找出使用了天津产的零件的工程号码,然后用NOT IN取反*/
SELECT JNO FROM J WHERE JNO NOT IN (SELECT JNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='天津')); 

/*把全部红色零件的颜色改成蓝色*/
UPDATE P SET COLOR='蓝' WHERE COLOR='红';

/*由S5供给J4的零件P6改为由S3供应,请作必要修改*/
UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6';

/*从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录*/
DELETE FROM SPJ WHERE SNO='S2';	/*先从SPJ中删除*/
DELETE FROM S WHERE SNO='S2';	/*从S中删除*/

/*请将(S2,J6,P4,200)插入供应情况关系*/
INSERT INTO SPJ VALUES ('S2','J6','P4',200);
0
扫描二唯码在微信中打开

我一直在开辟我的天空