数据库第三章课后作业

第三题

3.1

SELECT *
FROM S
WHERE A=10

3.2

SELECT A.B
FROM S

3.3

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

3.4

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

3.5

SELECT *
FROM S,T
WHERE A<E

3.6

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

第四题

这是创建表的信息

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)

向表中插入数据

--S表
	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,'上海');
	
--P表
	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);

--J表
	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','半导体厂','南京')；

--SPJ表
	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）求供应工程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 SPJ,P
where Jno='J1'and COLOR='红'and SPJ.Pno=P.Pno;

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

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

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

--S1生产的零件
SELECT DISTINCT PNO 
FROM SPJ 
where Sno = 'S1'
--需要选择P1 P2均有的Jno
select Jno
from SPJ
where Pno ='P1' and Jno in
(select Jno
 from SPJ
 where Pno='P2')
 或者使用NOT EXISTS语句
 --不存在这样的零件y，供应商s1用了y，而工程x没有用
select DISTINCT Jno
from SPJ J1
where NOT EXISTS
(
		SELECT *
		FROM SPJ J2
		WHERE J2.SNO ='S1' AND NOT EXISTS
		(
			SELECT *
			FROM SPJ J3
			WHERE J3.JNO=J1.JNO
			AND  J3.PNO=J2.PNO
		)
)
--这是模仿书上的例子写的
 

第五大题

5.1

select SNAME,CITY
from S;

5.2

select PNAME,COLOR,WEIGHT
from P;

5.3

select distinct JNO
from SPJ
where SNO='S1';

5.4

select PNAME,QTY
from SPJ,P
where SPJ.PNO=P.PNO AND SPJ.JNO='J2';

5.5

select PNO
from SPJ,S
where S.SNO=SPJ.SNO AND S.CITY='上海'

5.6

select JNO
from SPJ,S
where S.SNO=SPJ.SNO AND S.CITY='上海'

5.7

select distinct Jno
from J
where Jno not in (
select SPJ.Jno
from SPJ,P,S
where
S.CITY='天津'and S.Sno = SPJ.Sno and SPJ.Pno=P.Pno);

5.8

update P
set P.COLOR='蓝'
where P.COLOR='红';

5.9

update SPJ
set SPJ.Sno = 'S3'
where SPJ.Sno = 'S5' and SPJ.Jno='J4' and SPJ.Pno ='P6';

5.10

delete from S where Sno='S2';
delete from SPJ where Sno='S2';

5.11

insert into SPJ
values('S2','J6','P4',200);

第九大题

建立视图

create view SJ_QTY
as
	select SNO,PNO,QTY
	from SPJ,J
	where  SPJ.JNO=J.JNO AND J.JNAME='三建'

9.1

select Pno,QTY
from SJ_QTY;

9.2

select *
from SJ_QTY
where Sno=‘S1’;
感觉not exists 还不是很熟悉
别的知识需要多巩固。