数据库第三章 关系数据库标准语言SQL 课后习题

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

答：
（1）

select *
from S
where A=10;

（2）

select A,B
from S;

（3）

select A,B,C,D,E,F
from S,T
where S.c=T.c AND S.d=T.d

（4）

select *
from S,T
where S.c=T.c

（5）

select *
from S,T
where A < E

（6）

select 
from (select C,D from S) as S1,T;

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

答：
建表：

CREATE TABLE S(SNO char(2),
				SNAME char(10),
			 	STATUS smallint,
			 	CITY char(10))

CREATE TABLE P(PNO char(2),
			   PNAME char(10),
			   COLOR char(2),
			   WEIGHT smallint)

CREATE TABLE J(JNO char(2),
			   JNAME char(10),
			   CITY char(10))

CREATE TABLE SPJ(SNO char(2),
				 PNO char(2),
				 JNO char(2),
				 QTY smallint)


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

--(1)
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO = 'J1';

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

--(3)
SELECT SNO
FROM SPJ 
WHERE JNO='J1' AND PNO IN (
	SELECT PNO 
	FROM P 
	WHERE COLOR = '红'
);

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

--(5)
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO='S1';
SELECT JNO
FROM J 
WHERE NOT EXISTS ( 
	SELECT *
    FROM SPJ SPJX
	WHERE SPJX.SNO='S1' AND NOT EXISTS( 
		SELECT *
		FROM SPJ SPJY
		WHERE SPJX.SNO=SPJY.SNO AND 
				SPJX.PNO=SPJY.PNO AND 
				J.JNO=SPJY.JNO
	)
);

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

--(1)
SELECT SNAME,CITY
FROM S;

--(2)
SELECT PNAME,COLOR,WEIGHT
FROM P;

--(3)
SELECT DISTINCT JNO
FROM SPJ
WHERE SPJ.SNO = 'S1';

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

--(5)
select PNO
from SPJ,S
where SPJ.SNO=S.SNO and S.CITY='上海';

--(6)
SELECT JNAME
FROM S,J,SPJ
WHERE S.SNO = SPJ.SNO AND 
		SPJ.JNO = J.JNO AND 
		S.CITY='上海';

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

--(8)
UPDATE P
SET COLOR = '蓝'
WHERE COLOR = '红';

--(9)
UPDATE SPJ
SET SNO = 'S3'
WHERE SNO = 'S5' AND PNO = 'P6' AND JNO = 'J4';

--(10)
DELETE
FROM S
WHERE SNO = 'S2';

DELETE
FROM SPJ
WHERE SNO = 'S2';

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

9.请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）、针对该视图完成下列查询
（1）找出三建工程项目使用的各种零件代码及其数量
（2）找出供应商S1的供应情况

--准备工作——建立视图
CREATE VIEW SJ_QTY
AS
SELECT SNO,PNO,QTY
FROM SPJ,J
WHERE SPJ.JNO = J.JNO AND J.JNAME = '三建'

--(1)
SELECT PNO,QTY
FROM SJ_QTY;
--(2)
SELECT PNO,QTY
FROM SJ_QTY
WHERE SNO='S1';

有的查询题想不明白，有借鉴同学的，所有题目和实验做完，感觉还是收获蛮大的