第三章习题

(3)

select * 
from S 
where A=10; --1

select A,B
from S;  --2

select A,B,S.C,S.D,E,F 
from S,T
where S.C=T.C and S.D=T.D; --3

select A,B,S.C,S.D,T.C,T.D,E,F 
from S,T 
where S.C=T.C; --4

select A,B,S.C,S.D,T.C,T.D,E,F
from S,T 
where A<E; --5

select *
from T,
	(select C,D from S); --6

(4)
建立表

create table S
(
	SNO varchar(10) primary key,
	SNAME varchar(10),
	STATUS_ int,
	CITY varchar(10)
);   --S表

create table P
(
	PNO varchar(10) primary key,
	PNAME varchar(10),
	COLOR varchar(5),
	WEIGHT_ int
);   --P表

create table J
(
	JNO varchar(10) primary key,
	JNAME varchar(10),
	CITY varchar(10)
);   --J表

create table SPJ
(
	SNO varchar(10),
	PNO varchar(10),
	JNO varchar(10),
	QTY int,
	foreign key(SNO) references S(SNO),
	foreign key(PNO) references P(PNO),
	foreign key(JNO) references J(JNO)
);   --SPJ表

插入数据

insert into S(Sno,Sname,Status,City) --s表
values('S1','精益','20','天津'),
('S2','盛锡','10','北京'),
('S3','东方红','30','北京'),
('S4','丰泰盛','20','天津'),
('S5','为民','30','上海');

insert into P (Pno,Pname,Color,Weight)  --P表
values ('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);

insert into J (Jno,Jname,City)   --J表
values('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机船厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');

insert into SPJ (Sno,Pno,Jno,Qty)   --SPJ表
values('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);

查询

select SNO 
from SPJ 
where JNO='J1';  --1

select SNO 
from SPJ 
where JNO='J1' 
	   and PNO='P1';  --2

select SNO
from SPJ,P
where JNO='J1' and p.PNO=SPJ.PNO 
		and COLOR='红';   --3

select JNO
from J
where not exists
	(select *
	 from S,P,SPJ
	 where SPJ.JNO=J.JNO and SPJ.SNO=S.SNO
		and SPJ.PNO=P.PNO and S.CITY='天津'
		and P.COLOR='红'
	);   --4
	
select JNO
from SPJ as x
where not exists
	(select *
	from SPJ as y
	where SNO='S1' and not exists
		(select *
		 from SPJ as z
		 where z.PNO=y.PNO and z.JNO=x.JNO
		)
	);   --5

（5）

select SNAME,CITY 
from S;    --1

select PNAME,COLOR,WEIGHT_ 
from P;  --2

select JNO 
FROM SPJ 
where SNO='S1';  --3

select P.PNAME,SPJ.QTY
from P,SPJ
where SPJ.JNO='J2'
		and P.PNO=SPJ.PNO; --4

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

select JNAME
from S,J,SPJ
where J.JNO=SPJ.JNO 
		and SPJ.SNO=S.SNO 
		and S.CITY='上海'; --6

select JNO
from J
where not exists
	(select *
	 from S,SPJ
	 where SPJ.JNO=J.JNO and SPJ.SNO=S.SNO
		and S.CITY='天津'
	);   -- (7)

update P 
set COLOR='蓝色' 
where COLOR='红';  --8

update SPJ 
set SNO='S3' 
where SNO='S5' 
		and JNO='J4' 
		and PNO='P6'; --9

delete 
from S 
where SNO='S2';

delete 
from SPJ 
where SNO='S2';  --10

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

（9）
建立视图

create view san_jian
as
select SNO,PNO,QTY
from J,SPJ
where J.JNO=SPJ.JNO 
		and JNAME='三建工程';   --视图

查询

select PNO,QTY 
from san_jian; --1


select * 
from san_jian 
where SNO='S1'; --2