关于SPJ表的数据库作业

打字不易，且复制且珍惜

建表

use 库名;

create table S( --供应商
SNO char(6) not null,
SNAME char(10) not null,
STATUS INT,
CITY char(10),
primary key(SNO));

create table P( --零件
PNO char(6) not null,
PNAME char(12)not null,
COLOR char(4),
WEIGHT int,
primary key(PNO)
);

create table J( --工程
JNO char(6) not null,
JNAME char(12) not null,
CITY char(10),
primary key(JNO)
);

create table SPJ(
SNO char(6) not null,
PNO char(6) not null,
JNO char(6) not null,
QTY int,
primary key(SNO,PNO,JNO),
foreign key(SNO) references S(SNO),
foreign key(PNO) references P(PNO),
foreign key(JNO) references J(JNO)
);

插入

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',200);
INSERT INTO SPJ VALUES('S5','P6','J4',500);

查询

（1）	找出所有供应商的姓名和所在城市
		select sname,city 
		from s;
（2）	找出所有零件的名称，颜色，重量
		select pname,color,weight 
		from p;
（3）	找出使用供应商S1所供应零件的工程号码
		select jno 
		from spj 
		where sno='S1';
（4）	找出工程项目J2使用的各种零件的名称及其数量
		select p.pname,spj.qty 
		from p,spj 
		where p.pno=spj.pno and spj.jno='j2';
（5）	找出上海厂商供应的所有零件号码
		select distinct p.pno 
		from s,p,spj 
		where s.city='上海' and s.sno=spj.sno and p.pno=spj.pno;
		或
		select distinct pno 
		from spj 
		where sno in (
		select sno 
		from s 
		where city='上海');
（6）	找出使用上海产的零件的工程名称
		select distinct jname 
		from j 
		where jno in (
		select jno 
		from spj,s 
		where spj.sno=s.sno and s.city='上海');
（7）	找出没有使用天津产的零件的工程号码
		select jno 
		from j 
		where not exists(
		select * 
		from spj,s 
		where spj.jno=j.jno and spj.sno=s.sno and s.city='天津');
（8）	把全部红色零件的颜色改为蓝色
		update p 
		set color='蓝' 
		where color='红';
（9）	由S5供给J4的零件P6改为由S3供应，请做必要的修改
		update spj 
		set sno='S3' 
		where sno='S5' and jno='J4' and pno='P6';
（10）	从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录
		delete 
		from spj 
		where sno='s2';
		delete 
		from s 
		where sno='s2';
（11）	请将（S2，J6，P4，200）插入供应情况关系
		insert into s 
		values('s2','盛锡',10,'北京');
		insert into spj 
		values('s2','p4','j6',200);