数据库作业9：第三章作业

数据库作业9：第三章作业

第3题

3.有两个关系S(A,B,C,D)和T(C,D,E,F)，写出与下列查询等价的SQL表达式

(1)δA=10(S);

SELECT *
FROM S
WHERE A=10;

(2)∏A,B(S);

SELECT A,B
FROM S;

(3)S连接T;

SELECT A,B,C,D,E,F
FROM S,T
WHERE S.C=T.C AND S.D=T.D;

(4)S连接T当S.C=T.C时；

SELECT A,B,C,D,E,F
FROM S,T
WHERE S.C=T.C;

(5)S连接T当A<E时；

SELECT A,B,C,D,E,F
FROM S,T
WHERE S.A<T.E;

(6)∏C,D(S)xT

SELECT S.C,S.D
FROM S,T
WHERE S.C=T.C AND S.D=T.D

第4题

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

表的建立：

--P71
DROP TABLE IF EXISTS S;
DROP TABLE IF EXISTS P;
DROP TABLE IF EXISTS J;
DROP TABLE IF EXISTS SPJ;


CREATE TABLE S(--创建S表
	SNO CHAR(9),--供应商代码
	SNAME CHAR(20),--姓名
	STATUS1 CHAR(9),--状态
	CITY CHAR(20)--城市
);

CREATE TABLE P(--创建P表
	PNO CHAR(9),--零件代码
	PNAME CHAR(20),--零件名
	COLOR CHAR(9),--颜色
	WEIGHT1 SMALLINT--重量
);

CREATE TABLE J(--创建J表
	JNO CHAR(9),--工程项目代码
	JNAME CHAR(20),--工程项目名
	CITY CHAR(20)--工程所在城市
);

CREATE TABLE SPJ(--创建SPJ
	SNO CHAR(9),
	PNO CHAR(9),
	JNO CHAR(9),
	QTY SMALLINT--供应数量
)

--表格详情参照《数据库系统概论(第5版)》P71(S)
INSERT INTO S(SNO,SNAME,STATUS1,CITY) VALUES('S1','精益','20','天津');
INSERT INTO S(SNO,SNAME,STATUS1,CITY) VALUES('S2','盛锡','10','北京');
INSERT INTO S(SNO,SNAME,STATUS1,CITY) VALUES('S3','东方红','30','北京');
INSERT INTO S(SNO,SNAME,STATUS1,CITY) VALUES('S4','丰泰盛','20','天津');
INSERT INTO S(SNO,SNAME,STATUS1,CITY) VALUES('S5','为民','30','上海');

SELECT * FROM S;--查询学生表

--表格详情参照《数据库系统概论(第5版)》P71(P)
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT1) VALUES('P1','螺母','红',12);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT1) VALUES('P2','螺栓','绿',17);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT1) VALUES('P3','螺母','蓝',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT1) VALUES('P4','螺母','红',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT1) VALUES('P5','螺母','蓝',40);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT1) VALUES('P6','螺母','红',30);

SELECT *FROM P;

--表格详情参照《数据库系统概论(第5版)》P71(J)
INSERT INTO J(JNO,JNAME,CITY) VALUES('J1','三建','北京');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J2','一汽','长春');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J3','弹簧厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J4','造船厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J5','机车厂','唐山');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J6','无线电厂','常州');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J7','半导体厂','南京');

SELECT *FROM J;

--表格详情参照《数据库系统概论(第5版)》P71(SPJ)
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J3',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J4',700);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P2','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J4',500);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J5',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P5','J1',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J3',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J4',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P2','J4',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J4',500);

SELECT *FROM SPJ;

填入成功；

(1)求供应工程J1零件的供应商号码SNO；

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

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

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

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

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

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

SELECT DISTINCT JNO
FROM S,P,SPJ
WHERE JNO NOT IN
	(SELECT JNO 
	FROM S,P,SPJ 
	WHERE SPJ.SNO=S.SNO AND
		SPJ.PNO=P.PNO AND
		COLOR IN ('红') AND
		CITY IN('天津'));

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

SELECT JNO
FROM SPJ
WHERE SPJ.SNO='S1'

第5题

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

SELECT SNAME,CITY
FROM S;

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

SELECT PNAME,COLOR,WEIGHT1
FROM P;

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

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

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

SELECT DISTINCT P.PNAME,SPJ.QTY
FROM P,SPJ
WHERE SPJ.JNO='J2'

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

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

(6)找出使用上海产的零件的工程号码；

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

--嵌套查询如下
SELECT DISTINCT JNO
FROM SPJ
WHERE SNO IN
	(SELECT SNO
	FROM S
	WHERE CITY='上海')

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

SELECT DISTINCT JNO
FROM SPJ
WHERE SNO NOT IN
	(SELECT SNO
	FROM S
	WHERE S.CITY='天津');

(8)把全部红色零件的颜色改为蓝色；

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

SELECT *FROM P;

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

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

SELECT *FROM SPJ;

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

DELETE
FROM S
WHERE SNO='S2';

SELECT *FROM S;--查询S表检查

DELETE
FROM SPJ
WHERE SNO='S2';

SELECT *FROM SPJ;--查询SPJ表检查

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

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

SELECT *FROM SPJ;

第9题

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

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

查询检查：

SELECT *FROM IS_SPJ;

(1)找出三建工程项目使用的各种零件代码及其数量；

SELECT PNO,SUM(QTY) QTY
FROM IS_SPJ
GROUP BY PNO;

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

SELECT *
FROM IS_SPJ
WHERE SNO='S1';

至此，第三章作业已完成，在第二题和第五题中发现自己对于嵌套查询的使用不够熟练，更喜欢用“AND”连接，在平时应该多加练习。在第四题创建四个表的时候需要有耐心，仔细慢慢输入！在第九题中，视图的建立并不是十分熟悉~

在课下仍应该反复复习，练习，牢记常用SQL语句~

感谢阅读~