数据库系统概论第五版第3章课后习题

create database SPJ;

create table S
	(sno char(4),
	sname varchar(20),
	status int,
	city varchar(20));

create table P
	(pno char(4),
	pname varchar(20),
	color char(4),
	weight int);

create table J
	(jno char(4),
	jname varchar(20),
	city varchar(20));

create table SPJ
	(sno char(4),
	pno char(4),
	jno char(4),
	qty int);

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

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

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

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

select * from S;
select * from P;
select * from J;
select * from SPJ;

--找出所有供应商的姓名和所在的城市
select sname,city from S;

--找出零件的名称、颜色、重量
select pname,color,weight from P;

--找出使用供应商S1所供应零件的工程代码
select jno from SPJ where sno='S1';

--找出工程项目J2使用的各种零件的名称及其数量
select pname,qty from P,(select pno,qty from SPJ where jno='J2') as D
where P.pno=D.pno;

--找出上海厂商供应的所有零件代码
select distinct pno from SPJ where sno in (select sno from S where city like '上海');

--找出使用上海产的零件的工程名称
select distinct jname from J where jno in
(select distinct jno from SPJ where sno in (select sno from S where city like '上海'));

--找出没有使用天津产的零件的工程代码
select distinct jno from J where jno not in(
select distinct jno from SPJ where sno in
(select sno from S where city='天津'));

--把全部红色的零件改为蓝色
update P set color='蓝' where color='红';
select * from P;

--把由S5供给J2的零件P6改为S3供应
update SPJ set sno='S3' where sno='S5' and jno='J2';

--从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录
delete from SPJ where sno='S2';
delete from S where sno='S2';
select * from S;
select * from SPJ;

--请将（S2,P6,J4,200）插入供应情况关系
insert into SPJ values ('S2','P6','J4',200);