习题中和题解答.pptx

1、求没有使用天津供应商生产的红色零件的工程号JNO关系代数关系代数JNO(J)- JNO(sno(JNO(J)- JNO(sno(city=city=天津天津 (S)sno,pno,jno (S)sno,pno,jno (SPJ)pno(SPJ)pno(color=color=红红 (P)(P)关系演算关系演算RANGE SPJ SPJXRANGE SPJ SPJX P PX P PX S SX S SXGET W(J.JNO): ( GET W(J.JNO): ( SpjxSpjx（spjx.Jno=j.jno spjx.Jno=j.jno sx(sx.sno=spjx.sno sx(sx.sno=spjx.sno sx.ctiy=sx.ctiy=天津天津 px(px.pno=spjx.pno px(px.pno=spjx.pno px.color=px.color=红红)第1页/共5页SQL语言Select jno From jWhere not exists (select * from spj where spj.jno=j.jno and sno in (select sno from s where ctiy=天津) and pno in (select pno from p where color=红)第2页/共5页2、求至少用了供应商、求至少用了供应商s1所供应的全部零件的工程号所供应的全部零件的工程号JNO。关系代数关系代数JNO,PNOJNO,PNO(SPJ) (SPJ) PNOPNO( (sno=s1sno=s1(SPJ)(SPJ)关系演算关系演算RANGE SPJ SPJXRANGE SPJ SPJX SPJ SPJY SPJ SPJY P PX P PXGET W(J.JNO): GET W(J.JNO): PX(PX( SPJX.PNO=PX.PNO SPJX.PNO=PX.PNO SPJX.SNO=S1)SPJX.SNO=S1) SPJYSPJY（SPJY.JNO=J.JNO SPJY.JNO=J.JNO SPJY.PNO=PX.PNO)SPJY.PNO=PX.PNO)第3页/共5页SQL语言语言Select distinct jno from spj spjzWhere not exists (select * from spj spjx where sno=s1 and not exists (select * from spj spjy where spjy.pno=spjx.pno and spjy.jno=spjz.jno)所表达的语义是：不存在这样的零件y，供应商s1供应了y，而工程x没有选用y。第4页/共5页感谢您的观看！第5页/共5页