供应情况表SPJ(SNO,PNO,JNO,QTY)
工程项目表J(JNO,JNAME,CITY)
零件表P(PNO,PNAME,COLOR,WEIGHT)
供应商表S(SNO,SNAME,STATUS,CITY) 求所有supplier-number / part-number 对,其中指定的供应商不供应指定的零件本来就是select sno ,pno from s,p 减去 select sno,pno from spj,但是不知道怎么实现啊 然后是~
[42] 求对所有工程都提供了同一零件的供应商号码
暂时无头绪[47] 求供应商号码对(如Sx 和Sy),其中Sx 和Sy 供应的零件都相同。
本来应该是求出sno和pno对,然后做自身的交集操作,但是不知道怎么实现~~~
工程项目表J(JNO,JNAME,CITY)
零件表P(PNO,PNAME,COLOR,WEIGHT)
供应商表S(SNO,SNAME,STATUS,CITY) 求所有supplier-number / part-number 对,其中指定的供应商不供应指定的零件本来就是select sno ,pno from s,p 减去 select sno,pno from spj,但是不知道怎么实现啊 然后是~
[42] 求对所有工程都提供了同一零件的供应商号码
暂时无头绪[47] 求供应商号码对(如Sx 和Sy),其中Sx 和Sy 供应的零件都相同。
本来应该是求出sno和pno对,然后做自身的交集操作,但是不知道怎么实现~~~
create view all_info
as
select a.sno,b.pno
from s a cross join p b select distinct a.sno,b.pno
from all_info a join spj b on (a.sno<>b.sno and a.pno<>b.pno)
select s.name
from s,spj,j
where s.sno=spj.sno and spj.jno=j.jno and pno= 要查询的零件号或者 查询全部
select s.name
from s,spj,j
where s.sno=spj.sno and spj.jno=j.jno
group by j.jno
1.
select sno,sname,pno,pname
from p
cross join s
where not exists(select 1 from SPJ where PNO=p.pno and SNO=s.sno)
2.
select pno from
(select distinct sno,pno from spj)a
group by name
having count(1)=(select count(1) from (select distinct sno from spj)a)
3.找出不供应某零件的供应商
select sno from spj
where not exists(select 1 from spj where pno='在这里输入指定零件')
group by sno
3.找出不供应某零件的供应商
select * from s
where not exists(select 1 from spj where sno=s.sno and pno='在这里输入指定零件')