/*第二章 作业*/
create table S
(
sno char(2) NOT NULL UNIQUE,
sname char(3),
city char(2)
);
alter table S add constraint s_k primary key(sno); create table P
(
pno char(2) NOT NULL,
pname char(3),
color char(1),
weight int
);
alter table P add constraint p_k primary key(pno); create table J
(
jno char(2) NOT NULL,
jname char(3),
city char(2)
);
alter table J add constraint j_k primary key(jno); create table SPJ
(
sno char(2) NOT NULL,
pno char(2) NOT NULL,
jno char(2) NOT NULL,
qty int
);
alter table SPJ add constraint spj_k primary key(sno,pno,jno);
alter table SPJ add constraint spj_fk foreign key(sno) references s(sno);
alter table SPJ add constraint spj_fk2 foreign key(pno) references p(pno);
alter table SPJ add constraint spj_fk3 foreign key(jno) references J(jno); alter table s alter column sname char(6);
alter table s alter column city char(4); insert into s values('S1', '精 益', '天津');
insert into s values('S2', '万 胜', '北京');
insert into s values('S3', '东 方', '北京');
insert into s values('S4', '丰泰隆', '上海');
insert into s values('S5', '康 健', '南京'); alter table p alter column pname char(6);
alter table p alter column color char(2); 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); alter table j alter column jname char(8);
alter table j alter column city char(4);
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); /*(4)求没有使用天津供应商生产的红色零件的工程号*/
select distinct jno
from spj
where jno not in
( select jno
from spj
where sno in
(select sno
from s
where city='天津'
and pno in
( select pno
from p
where color='红'))
); /*(5)求至少用了S1供应商所供应的全部零件的工程号JNO */
select distinct jno
from spj x
where not exists
(
select distinct pno
from spj y
where y.sno='S1' and
not exists
(
select *
from spj z
where z.jno = x.jno and
z.pno = y.pno));
/*首先查询SPJ表得到一个工程号的集合对于集合中的每一条记录做如下查询：取出一个工程号，比如J1 如果不存在这样的记录 查询SPJ表得到这样一个集合 -- 由供应商S1供应的零件号，这里是P1, P2 1 取出一个零件号，比如pno = 'P1' 查询spj表，对于jno='J1' 并且 pno='P1' 2 取出下一个零件号，pno = 'P2' 查询spj表，对于jno='J1' 并且 pno='P2'
如果J1使用了由S1供应的零件则最后一个存在量词始终返回false 那么第一个存在量词也就始终返回false，两个存在量词都返回 false就表示该工程至少使用了S1供应商所供应的全部零件。
对于一个工程，不存在这种情况，S1供应了一个零件，而该工程没有使用
*/
/*（1） 统计每种零件的供应总量*/
select pno, sum(qty)
from spj
group by pno
/*(2) 求零件供应总量在1000以上的供应商名字*/
select s.sname
from s
where s.sno in
(
select spj.sno
from spj
group by spj.sno having sum(qty)>1000
);
insert into s values('S6', '华天', '深圳');
alter table p alter column color char(6);
update p
set color='粉红'
where color='红';
/*(5) 将S1供应给J1的零件P1改为由P2供给*/
update spj
set pno='P2'
where sno='S1' and jno='J1' and pno='P1';
delete
from spj
where pno in
(
select pno
from p
where color='蓝'
);