《数据库系统概论》课本P127 参考答案

create table s --3
(
 sno varchar(10) primary key,
 sname varchar(10),
 city varchar(12),
 status varchar(12)
)
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)

create table j
(
 jno varchar(10) primary key,
 jname varchar(30),
 city varchar(12)
)

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','半导体厂','南京')

create table p
(
 pno varchar(10) primary key,
 pname varchar(30),
    color varchar(10),
    weight int
)

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)

create table spj
(
 sno varchar(10) foreign key references s(sno),
 pno varchar(10) foreign key references p(pno),
 jno varchar(10) foreign key references j(jno),
    qty int,
    primary key (sno,jno,pno)
)

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 distinct sno from spj where jno='J1' --4_1

select distinct sno from spj where jno='J1' and pno='P1' --4_2

select distinct sno from spj,p where spj.pno=p.pno and jno='J1' and color='红' --4_3

select jno from j where jno not in
(select distinct jno from s,spj,p
 where s.sno=spj.sno and p.pno=spj.pno and city='天津' and color='红') --4_4

select  jno from j where not exists
(select * from spj t where sno='S1' and not exists
     (select * from spj where pno=t.pno and jno=j.jno)) --4_5

select  sname,city from s --5_1

select  pname,color,weight from p --5_2

select distinct jno from spj where sno='S1'--5_3

select pname,qty from spj,p where spj.pno=p.pno and jno='J2'--5_4

select distinct pno from s,spj where s.sno=spj.sno and city='上海'--5_5

select distinct jname from j,spj,s
where j.jno=spj.jno and s.sno=spj.sno and s.city='上海' --5_6

select jno from j where jno not in
              (select jno from s,spj
               where s.sno=spj.sno and city='天津')--5_7_1

select jno from j where not exists
              (select jno from s,spj
               where s.sno=spj.sno and j.jno=spj.jno and city='天津')--5_7_2

update p set color='蓝' where color='红'--5_8
 
update spj set  sno='S3' where sno='S5' and j='J4' and pno='P6' --5_9

delete from spj where sno='S2' --5_10
delete from s where sno='S2'

insert into spj values('S2','P4','J6',200) --5_11

go
create view 三建项目供应情况 as
select sno,pno,qty
from spj,j
where spj.jno=j.jno and jname='三建' --11

go
select pno,qty from 三建项目供应情况 --11_1

select * from 三建项目供应情况 where sno='S1'--11_2