查询-SPJ练习3参考答案

1. 查询不供应红色产品的厂家号和厂家名。

select Sno, Sname

from S

where Sno not in (

select Sno from SPJ, P where SPJ.Pno=P.Pno and Color='红');

1. 查询没有供应过供应商S1供应的零件的供应商名称。

Select sname

From s

Where sno not in(

select sno from spj where pno in

 (select pno from spj where sno =’s1’)

);

或者：

Select sname

From s

Where sno not in(

Select a.sno from spj a, spj b

Where a.pno = b.pno and b.sno = ‘s1’);

错误如下：

Select sname

From s,spj

Where s.sno = spj.sno and pno not in(select pno from spj where sno =’s1’)

1. 查询不供应p1 但供应零件 P3的供应商名称。

Select sname

From s

Where sno in (

select sno from spj where pno = ‘p3’ and

sno not in (select sno from spj where pno = ‘p1’)

);

或者：

Select s.sno,sname

From spj,s

Where spj.sno = s.sno and pno = ‘P3’ 

and sno not in(

select sno from SPJ where pno =’P1’);

1. 查询没有供应全部零件的供应商的名称。

Select sname

From s

Where exists(

select *

from p

where not exists (

select *

from spj

where s.sno = spj.sno and p.pno = spj.pno));

1. 查询没有使用S3供应的零件的所有工程信息及其使用零件的情况。

Select J.*,pno,sno,qty

From J left join spj on j.jno = spj.jno

Where jno not in (select jno from spj where sno = ‘s3’);

1. 查询每个工程及其使用零件的供应商和零件的情况。

Select j.jno,jname,

From j left join spj on j.jno = spj.jno left join p on p.pno = spj.pno left join s on s.sno = spj.sno;

1. 查询各个工程的工程号、工程名以及所使用的的零件号、零件名及其数量。

Select j.jno,jname,p.pno,pname,qty

From j left join spj on j.jno = spj.jno left join p on p.pno = spj.pno

Group by spj.jno,spj.pno;