数据库课程第三章课后题作业

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 values('S1','精益','20','天津');
insert into S values('S2','盛锡','10','北京');
insert into S values('S3','东方红','30','北京');
insert into S values('S4','丰泰盛','20','天津');
insert into S values('S5','为民','30','上海');

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',300);
insert into SPJ values('S5','P6','J4',200);

进行相关查询

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)

查询结果如下，从左到右为(1)~(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)

(1)~(5)查询结果如下：

(6)~(7)查询结果如下：

6、

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)

查询结果如下：