【2019-2020春学期】数据库作业10：第三章课后题

(1)

	SELECT *
	FROM S
	WHERE A=10;

(2)

	SELECT A,B
	FROM S;

(3)

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

(4)

	SELECT S.*,T.*
	FROM S,T
	WHERE S.C=T.C;

(5)

	SELECT S.*,T.*
	FROM S,T
	WHERE S.A<T.E;

(6)

	SELECT 	S.C,S.D,T.*
	FROM S,T;

建表：

	CREATE TABLE S
	(SNO CHAR(5) PRIMARY KEY,
	SNAME CHAR(20),
	STATUS INT,
	CITY CHAR(10)
	);
	CREATE TABLE P
	(PNO CHAR(5) PRIMARY KEY,
	PNAME CHAR(20),
	COLOR CHAR(5),
	WEIGHT INT
	);
	CREATE TABLE J
	(JNO CHAR(5) PRIMARY KEY,
	JNAME CHAR(20),
	CITY CHAR(10)
	);
	CREATE TABLE SPJ
	(SNO CHAR(5),
	PNO CHAR(5),
	JNO CHAR(5),
	QTY INT
	);
	INSERT INTO S
	VALUES ('S1','精益',20,'天津');
	INSERT INTO S
	VALUES ('S2','盛锡',10,'北京');
	INSERT INTO S
	VALUES ('S3','东方红',20,'北京');
	INSERT INTO S
	VALUES ('S4','丰泰盛',20,'天津');
	INSERT INTO S
	VALUES ('S5','为民',20,'上海');

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

查询

	SELECT SNO
	FROM SPJ
	WHERE JNO='J1';   /*  (1)    */

	SELECT SNO
	FROM SPJ
	WHERE JNO='J1'AND PNO='P1';    /*   (2)   */

	SELECT SNO
	FROM SPJ
	WHERE JNO='J1'AND PNO IN(
							SELECT PNO
							FROM P
							WHERE COLOR='红'      /*  （3）  */
	);	

（3）还有下列写法

	SELECT SNO
	FROM SPJ,P
	WHERE JNO='J1'AND SPJ.PNO=P.PNO AND COLOR='红';   /*  (3)    */

（4）

	SELECT JNO   /*  （4）  */
	FROM SPJ
	WHERE PNO NOT IN(SELECT PNO
					FROM P
					WHERE COLOR='红')
		  AND SNO  NOT IN(SELECT SNO
							FROM S
							WHERE CITY='天津');

或

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

（5）

	SELECT DISTINCT JNO
	FROM SPJ
	WHERE PNO >= ALL
				(SELECT PNO
				FROM SPJ
				WHERE SNO='S1'
				);

	UPDATE P   /*  （8）*/
	SET COLOR='蓝'
	WHERE  COLOR='红';

	SELECT * 
	FROM P;

在这里插入代码片

	SELECT SNAME,CITY         /*  （1）*/
	FROM S;

	SELECT PNAME,COLOR,WEIGHT         /*  （2）*/
	FROM P;

	SELECT JNO         /*  （3）*/
	FROM SPJ
	WHERE SNO='S1';

	SELECT PNAME, QTY    /*  （4）*/
	FROM SPJ,P
	WHERE JNO='J2'AND SPJ.PNO=P.PNO ;

	SELECT PNO       /*  （5）*/
	FROM S,SPJ
	WHERE CITY='上海'AND S.SNO=SPJ.SNO ;

	SELECT JNAME       /*  （6）*/
	FROM SPJ,J,S
	WHERE S.CITY='上海'AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO;

	SELECT DISTINCT JNO        /*  （7）*/
	FROM SPJ
	WHERE SNO NOT IN(
					SELECT SNO
					FROM S
					WHERE CITY='天津'
					);

	UPDATE SPJ       /*  （9）*/
	SET SNO='S3'
	WHERE SNO='S5' AND JNO='J4' AND PNO='P6';

	SELECT *
	FROM SPJ;

	DELETE      /*  （10）*/
	FROM SPJ
	WHERE SNO='S2';

	DELETE
	FROM S
	WHERE SNO='S2';

	INSERT        /*  （11）*/
	INTO SPJ
	VALUES('S2','J6','P4',200);
	SELECT *
	FROM SPJ;

建视图

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

(1)

	SELECT PNO,QTY
	FROM SJ;

(2)

	SELECT *
	FROM SJ
	WHERE SNO='S1';

比我预计做完的时间晚了好多，好像不太难，但要做好也得好不少时间啊。感觉更扎实了。