SQL 第二章 作业
/*第二章 作业*/ 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='蓝' );