1.求供应工程求供应工程J1零件的供应商号码零件的供应商号码SNO。Select Select snosno from from spjspjWhere Where jnojno=J1;=J1;2.求供应工程求供应工程J1零件零件P1的供应商号码的供应商号码SNO。Select Select snosno from from spjspjWhere jnojno=J1 and =J1 and pnopno=P1;=P1;习题三习题三 第第4 4题题1 13.求供应工程求供应工程J1零件为红色的供应商号码。零件为红色的供应商号码。Select Select snosno from from spjspj, p, pWhere Where spj.pnospj.pno=p.pnop.pno and and jnojno=J1 and =J1 and color=color=红红红红 ; ;或：或：或：或：Select Select snosno from from spjspjWhere Where jnojno =J1 and =J1 and pnopno in in (Select (Select pnopno from p from p where color= where color=红红红红 ););2 24.求没有使用天津供应商生产的红色零件的工程号求没有使用天津供应商生产的红色零件的工程号JNO。Select Select jnojnoFrom jFrom jWhere not existsWhere not exists (Select *(Select * From From spjspj, s, p, s, p where where spj.jnospj.jno=j.jnoj.jno and and spj.snospj.sno=s.snos.sno and and spj.pnospj.pno=p.pnop.pno and and s.citys.city=天津天津天津天津 and and p.colorp.color=红红红红 ););3 35.求至少用了供应商求至少用了供应商S1所供应的全部零件的工程号所供应的全部零件的工程号JNO。即查找：不存在这样的零件即查找：不存在这样的零件y，供应商，供应商S1供应了供应了y，而工程，而工程x为选用为选用y。Select distinct Select distinct jnojnoFrom From spjspj z zWhere not existsWhere not exists(select * (select * from from spjspj x x where where snosno=S1=S1 and not exists and not exists(select * (select * from from spjspj y y where where y.pnoy.pno=x.pnox.pno and and y.jnoy.jno=z.jnoz.jno););4 4习题三习题三 第第5 5题题1. 1. 找出所有供应商的姓名及其所在城市。找出所有供应商的姓名及其所在城市。找出所有供应商的姓名及其所在城市。找出所有供应商的姓名及其所在城市。Select Select snamesname, city , city from s;from s;2. 2. 找出所有零件的名称、颜色、重量。找出所有零件的名称、颜色、重量。找出所有零件的名称、颜色、重量。找出所有零件的名称、颜色、重量。Select Select pnamepname, color, weight , color, weight from p;from p;3.3.找出使用供应商找出使用供应商找出使用供应商找出使用供应商S1S1所供应零件的工程项目代码。所供应零件的工程项目代码。所供应零件的工程项目代码。所供应零件的工程项目代码。Select Select jnojno from from spjspj where where snosno=S1;=S1;5 54.4.找出工程项目找出工程项目找出工程项目找出工程项目J2 J2 所使用的各种零件的名称及其所使用的各种零件的名称及其所使用的各种零件的名称及其所使用的各种零件的名称及其数量。数量。数量。数量。Select Select p.pnamep.pname, , spj.qtyspj.qty from p, from p, spjspjwhere where p.pnop.pno=spj.pnospj.pno and and spj.jnospj.jno=J2;=J2;5.5.找出上海厂商供应的所有零件的代码。找出上海厂商供应的所有零件的代码。找出上海厂商供应的所有零件的代码。找出上海厂商供应的所有零件的代码。Select distinct Select distinct pnopno from from spjspjwhere where snosno in in (Select (Select snosno from s from s where city= where city=上海上海上海上海 ););6 66. 6. 找出使用上海产的零件的工程项目名。找出使用上海产的零件的工程项目名。找出使用上海产的零件的工程项目名。找出使用上海产的零件的工程项目名。Select Select jnamejname from from j,spj,sj,spj,swhere where j.jnoj.jno=spj.jnospj.jno and and spj.snospj.sno=s.snos.sno and and s.citys.city=上海上海上海上海 ; ;或：或：或：或：Select Select jnamejname from jfrom jwhere where jnojno in (Select in (Select jnojno from from spjspj, s, s where where spj.snospj.sno=s.snos.sno and and s.citys.city=上海上海上海上海 ););7 77. 7. 找出没有使用天津产的零件的工程项目代码。找出没有使用天津产的零件的工程项目代码。找出没有使用天津产的零件的工程项目代码。找出没有使用天津产的零件的工程项目代码。Select Select jnojno from j from j where not existswhere not exists (Select * (Select * from from spjspj where where spj.jnospj.jno=j.jnoj.jno and and snosno in (Select in (Select snosno from s from s where city= where city=天津天津天津天津 ) ) ); );8 8或：或：或：或： Select Select jnojno from j from j where not exists where not exists (Select * (Select * from from spj,sspj,s where where spj.jnospj.jno=j.jnoj.jno and and spj.snospj.sno=s.snos.sno and and s.citys.city=天津天津天津天津 ););9 9