《数据库答案第三章习题参考答案》由会员分享,可在线阅读,更多相关《数据库答案第三章习题参考答案(9页珍藏版)》请在金锄头文库上搜索。
1、11.求供应工程J1零件的供应商号码SNO。Select sno from spjWhere jno=J1;2.求供应工程J1零件P1的供应商号码SNO。Select sno from spjWhere jno=J1 and pno=P1;习题三习题三 第第4题题23.求供应工程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=红 );
2、34.求没有使用天津供应商生产的红色零件的工程号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=红 );45.求至少用了供应商S1所供应的全部零件的工程号JNO。即查找:不存在这样的零件y,供应商S1供应了y,而工程x为选用y。Select distinct jnoFrom spj zWhere not exists(select * from spj x
3、where sno=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;64.找出工程项目J2 所使用的各种零件的名称及其数量。Select p.pname, spj
4、.qty from p, spjwhere p.pno=spj.pno and spj.jno=J2;5.找出上海厂商供应的所有零件的代码。Select distinct pno from spjwhere sno in (Select sno from s where city=上海);76. 找出使用上海产的零件的工程项目名。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=上海);87. 找出没有使用天津产的零件的工程项目代码。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=天津);