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

课本第三章课后第五大题、  第五章第六题

建供求表

 create table S(
sno char(10) unique,
sname char(20),
statu smallint,
city char(20)
);
create table P(
pno char(10) unique,
pname char(20),
color char(9),
weigh smallint
);
create table J(
jno char(10) unique,
jname char(40),
city char(20)
);
create table spj(
sno char(10),
pno char(10),
jno char(10),
qty int
);

insert 
into S(sno,sname,statu,city)
values('s1','精益',20,'天津');
insert 
into S(sno,sname,statu,city)
values('s2','盛锡',10,'北京');
insert 
into S(sno,sname,statu,city)
values('s3','东方红',30,'北京');
insert 
into S(sno,sname,statu,city)
values('s4','丰盛泰',20,'天津');
insert 
into S(sno,sname,statu,city)
values('s5','为民',30,'上海');

insert 
into P(pno,pname,color,weigh)
values('p1','螺母','红',12);
insert 
into P(pno,pname,color,weigh)
values('p2','螺栓','绿',17);
insert 
into P(pno,pname,color,weigh)
values('p3','螺丝刀','蓝',14);
insert 
into P(pno,pname,color,weigh)
values('p4','螺丝刀','红',14);
insert 
into P(pno,pname,color,weigh)
values('p5','凸轮','蓝',40);
insert 
into P(pno,pname,color,weigh)
values('p6','齿轮','红',30);

insert 
into J(jno,jname,city )
values('j1','三建','北京');
insert 
into J(jno,jname,city )
values('j2','一汽','长春');
insert 
into J(jno,jname,city )
values('j3','弹簧厂','天津');
insert 
into J(jno,jname,city )
values('j4','造船厂','天津');
insert 
into J(jno,jname,city )
values('j5','机车厂','唐山');
insert 
into J(jno,jname,city )
values('j6','无线电厂','常州');
insert 
into J(jno,jname,city )
values('j7','半导体厂','南京');

insert 
into spj(sno,pno,jno,qty)
values('s1','p1','j1',200);
insert 
into spj(sno,pno,jno,qty)
values('s1','p1','j3',100);
insert 
into spj(sno,pno,jno,qty)
values('s1','p1','j4',700);
insert 
into spj(sno,pno,jno,qty)
values('s1','p2','j2',100);
insert 
into spj(sno,pno,jno,qty)
values('s2','p3','j1',400);
insert 
into spj(sno,pno,jno,qty)
values('s2','p3','j2',200);
insert 
into spj(sno,pno,jno,qty)
values('s2','p3','j4',500);
insert 
into spj(sno,pno,jno,qty)
values('s2','p3','j5',400);
insert 
into spj(sno,pno,jno,qty)
values('s2','p5','j1',400);
insert 
into spj(sno,pno,jno,qty)
values('s2','p5','j2',100);
insert 
into spj(sno,pno,jno,qty)
values('s3','p1','j1',200);
insert 
into spj(sno,pno,jno,qty)
values('s3','p3','j1',200);
insert 
into spj(sno,pno,jno,qty)
values('s4','p5','j1',100);
insert 
into spj(sno,pno,jno,qty)
values('s4','p6','j3',300);
insert 
into spj(sno,pno,jno,qty)
values('s4','p6','j4',200);
insert 
into spj(sno,pno,jno,qty)
values('s5','p2','j4',100);
insert 
into spj(sno,pno,jno,qty)
values('s5','p3','j1',200);
insert 
into spj(sno,pno,jno,qty)
values('s5','p6','j2',200);
insert 
into spj(sno,pno,jno,qty)
values('s5','p6','j4',500); 

 

 

Selectdistinct sno

From spj

Where  jno=’j1’ and pno in(

Selectpno

Fromp

         Where color=’红’

);

P71

select distinct sno from spj

where jno='j1'

 

 

select distinct sno from spj

where jno='j1' and pno='p1'

 

select sno

from spj

where jno='j1' and pno in(

      select pno

      from p

      where color='红'

      )

select jno
from j
where not exists(
select * 
from spj
where sno in(
select sno
from s
where city='天津'
)
and   pno in(
select pno
from p
where color='红')
and spj.jno=j.jno
)

求没有使用天津供应商生产的红色零件的工程号 JNO

select jno
from j
where not exists(
select *
from spj,s,p
where spj.sno=s.sno
and spj.pno=p.pno
and s.city='天津'
and p.color='红'
and jno=j.jno

）

至少用了供应商 Sl 所供应的全部零件的工程号 JNO

select jno

from j

where not exists (

      select *

      from spj x

      where x.sno='s1' and not exists(

                       select *

                       from spj y

                       where y.pno=x.pno and

                       y.jno=j.jno and

                       y.sno='s1'

      )

)