数据库答案习题参考答案市公开课金奖市赛课一等奖课件

1.求供给工程J1零件供给商号码SNO。SelectsnofromspjWherejno=‘J1’;2.求供给工程J1零件P1供给商号码SNO。SelectsnofromspjWherejno=‘J1’andpno=‘P1’;习题三第4题1第1页3.求供给工程J1零件为红色供给商号码。Selectsnofromspj,pWherespj.pno=p.pnoandjno=‘J1’andcolor=‘红’;或：SelectsnofromspjWherejno=‘J1’andpnoin(Selectpnofrompwherecolor=‘红’);2第2页4.求没有使用天津供给商生产红色零件工程号JNO。SelectjnoFromjWherenotexists (Select* Fromspj,s,p wherespj.jno=j.jnoandspj.sno=s.snoandspj.pno=p.pnoands.city=‘天津’ andp.color=‘红’);3第3页5.求最少用了供给商S1所供给全部零件工程号JNO。即查找：不存在这么零件y，供给商S1供给了y，而工程x为选取y。SelectdistinctjnoFromspjzWherenotexists (select*fromspjxwheresno=‘S1’andnotexists (select*fromspjy wherey.pno=x.pnoandy.jno=z.jno));4第4页习题三第5题1.找出全部供给商姓名及其所在城市。Selectsname,cityfroms;2.找出全部零件名称、颜色、重量。Selectpname,color,weightfromp;3.找出使用供给商S1所供给零件工程项目代码。Selectjnofromspjwheresno=‘S1’;5第5页4.找出工程项目J2所使用各种零件名称及其数量。Selectp.pname,spj.qtyfromp,spjwherep.pno=spj.pnoandspj.jno=‘J2’;5.找出上海厂商供给全部零件代码。Selectdistinctpnofromspjwheresnoin(Selectsnofromswherecity=‘上海’);6第6页6.找出使用上海产零件工程项目名。Selectjnamefromj,spj,swherej.jno=spj.jnoandspj.sno=s.snoands.city=‘上海’;或：Selectjnamefromjwherejnoin(Selectjnofromspj,swherespj.sno=s.snoands.city=‘上海’);7第7页7.找出没有使用天津产零件工程项目代码。Selectjnofromjwherenotexists(Select*fromspjwherespj.jno=j.jnoandsnoin(Selectsnofromswherecity=‘天津’));8第8页或：

Selectjnofromjwherenotexists(Select*fromspj,swheresp