S(sno,sname,status,city)
P(pno,pname,color,weight)
J(jno,jname,city)
SPJ(sno,pno,jno,qty)
供应商表S由供应商代码(sno)、供应商姓名(sname)、供应商状态(status)、供应商所在城市(city)组成
零件表P由零件代码(pno)、零件名(pname)、颜色(color)、重量(weight)组成
工程项目表J由工程项目代码(jno)、工程项目名(jname)、工程项目所在城市(city)组成
供应情况表SPJ由供应商代码(sno)、零件代码(pno)、工程项目代码(jno)、供应数量(qty)组成，表示某供应商供应某种零件给某工程项目的数量为qty
问题
7、查询没有使用天津供应商生产的红色零件的工程号jno
8、查询至少用了供应商S1所供应的全部零件的工程号jno
------解决方案--------------------
7. select 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='红色')
)
)
------解决方案--------------------
7.select jno from j where sno not in (select sno from s where city= '天津 ')and
pno not in(select pno from p where color='红色')
------解决方案--------------------
7. select jno from spj join s on spj.sno =s.sno where city !='天津' and color !='红色'
------解决方案--------------------
select jno from j where jno not in(select spj.jon from spj,s where spj.son=s.sno and s.city='天津' )
and jno not in(select spj.jon from spj,p where spj.pon=p.pno and p.color='红色')
------解决方案--------------------
declare @num int
select @num=count(pno) from spj where sno in (select sno from s where sname='s1' )group by sno
select p.pno,count(p.pno) as aaa into #temp from p,spj where p.jno=spj.jno and (spj.sno in (select sno from s where sname='s1' ))
group by spj.jno,p.pno
select pno from #temp where [email protected]
------解决方案--------------------
select j.jno from SPJ
left join s
on s.sno=SPJ.sno
left join p
on p.pno=SPJ.pno
left join j
on j.jno=SPJ.jno
where city='天津' AND color= '红色'
------解决方案--------------------
Select 工程项目表J.工程项目代码(jno)
From 工程项目表J
Where 工程项目表J.工程项目代码(jno)
NOT IN (
Select 工程项目表J.工程项目代码(jno)
FROM 工程项目表J
INNER JOIN 供应情况表SPJ
ON 工程项目表J.工程项目代码(jno) = 供应情况表SPJ.工程项目代码(jno)
INNER JOIN 零件表P
ON 供应情况表SPJ.零件代码(pno) = 零件表P.零件代码(pno)
INNER JOIN 供应商表S
ON 供应情况表SPJ.供应商代码(sno) = 供应商表S.供应商代码(sno)
WHERE 供应商表S.供应商所在城市(city) = '天津 '
AND 零件表P.颜色(color) = '红色 ')
这段是搂主的问题7的回答
下面会给出问题8的回答
------解决方案--------------------
关于问题8，想问楼主一个问题：
[至少用了供应商S1]这句话有点问题，是不是指至少有个供应商？
------解决方案--------------------
7.
select c.jno from
(
(select a.pno,a.jno from spj as a,s as b
where a.sno=b.sno and b.city<>'天津')
)
as c,
j as d
where c.jno=d.jno and d.color<>'红色'
------解决方案--------------------
#35这不是王珊书上的原题么