数据库答案第三章习题参考答案.ppt

《数据库答案第三章习题参考答案.ppt》由会员分享，可在线阅读，更多相关《数据库答案第三章习题参考答案.ppt（9页珍藏版）》请在三一办公上搜索。

1、1,1.求供应工程J1零件的供应商号码SNO。Select sno from spjWhere jno=J1;2.求供应工程J1零件P1的供应商号码SNO。Select sno from spjWhere jno=J1 and pno=P1;,习题三 第4题,2,3.求供应工程J1零件为红色的供应商号码。Select sno from spj,pWhere spj.pno=p.pno and jno=J1 and color=红;或：Select sno from spjWhere jno=J1 and pno in(Select pno from p where color=红);,3,4.

2、求没有使用天津供应商生产的红色零件的工程号JNO。Select jnoFrom jWhere not exists(Select*From spj,s,p where spj.jno=j.jno and spj.sno=s.sno and spj.pno=p.pno and s.city=天津 and p.color=红);,4,5.求至少用了供应商S1所供应的全部零件的工程号JNO。即查找：不存在这样的零件y，供应商S1供应了y，而工程x为选用y。Select distinct jnoFrom spj zWhere not exists(select*from spj x where sno

3、=S1 and not exists(select*from spj y where y.pno=x.pno and y.jno=z.jno);,5,习题三 第5题,1.找出所有供应商的姓名及其所在城市。Select sname,city from s;2.找出所有零件的名称、颜色、重量。Select pname,color,weight from p;3.找出使用供应商S1所供应零件的工程项目代码。Select jno from spj where sno=S1;,6,4.找出工程项目J2 所使用的各种零件的名称及其数量。Select p.pname,spj.qty from p,spjwh

4、ere p.pno=spj.pno and spj.jno=J2;5.找出上海厂商供应的所有零件的代码。Select distinct pno from spjwhere sno in(Select sno from s where city=上海);,7,6.找出使用上海产的零件的工程项目名。Select jname from j,spj,swhere j.jno=spj.jno and spj.sno=s.sno and s.city=上海;或：Select jname from jwhere jno in(Select jno from spj,s where spj.sno=s.sno and s.city=上海);,8,7.找出没有使用天津产的零件的工程项目代码。Select jno from j where not exists(Select*from spj where spj.jno=j.jno and sno in(Select sno from s where city=天津);,9,或：Select jno from j where not exists(Select*from spj,s where spj.jno=j.jno and spj.sno=s.sno and s.city=天津);,