--求供应工程J1零件的供应商号码SNO
select distinct SNO
from SPJ
where JNO='J1'
--求供应工程J1零件P1的供应商号码SNO
select SNO
from SPJ
where JNO='J1' and PNO='P1'
--求供应工程J1零件为红色的供应商号码SNO
select SNO
from P,SPJ
where P.PNO=SPJ.PNO and JNO='J1' and COLOR='红'
select SNO
from SPJ
where JNO='J1' and PNO in
(
select PNO
from P
where COLOR='红'
)
select SNO
from SPJ
where JNO='J1' and exists --相干子查询
(
select *
from P
where COLOR='红' and P.PNO=SPJ.PNO
)
--求没有使用天津供应商生产的红色零件的工程号JNO
select JNO
from J
where JNO not in
(
select JNO
from S,P,SPJ
where CITY='天津' and COLOR='红' and S.SNO=SPJ.SNO and P.PNO=SPJ.PNO
)
select JNO
from J
where JNO <>all
(
select JNO
from S,P,SPJ
where CITY='天津' and COLOR='红' and S.SNO=SPJ.SNO and P.PNO=SPJ.PNO
)
select JNO
from J
where JNO !=all
(
select JNO
from S,P,SPJ
where CITY='天津' and COLOR='红' and S.SNO=SPJ.SNO and P.PNO=SPJ.PNO
)
select JNO
from J
where not exists
(
select *
from S,P,SPJ
where CITY='天津' and COLOR='红' and S.SNO=SPJ.SNO and P.PNO=SPJ.PNO and SPJ.JNO=J.JNO
)
select JNO
from J
except
select JNO
from S,P,SPJ
where CITY='天津' and COLOR='红' and S.SNO=SPJ.SNO and P.PNO=SPJ.PNO
--求至少使用了供应商S1所供应的全部零件的工程号JNO
select distinct JNO
from SPJ x
where not exists
(
select *
from SPJ y
where SNO='S1' and not exists
(
select *
from SPJ z
where JNO=x.JNO and PNO=y.PNO
)
)