四、實驗內(nèi)容

1．查詢?yōu)楣こ蘆1供應紅色零件的供應商號碼SNO。

use shiyan go

select sno from spj

where jno='j1' and pno in (select pno

from p

where color='紅')

或

select sno from spj,p

where jno='j1' and spj.pno=p.pno and color='紅'

2．查詢沒有使用天津供應商生產(chǎn)的零件并且當前工程所使用零件的顏色全部為紅色的工程號JNO。

use shiyan go

select jno from spj

where sno in (select sno from s

where city<>'天津') and

pno in (select pno from p

where color='紅')

或

use shiyan go

select jno from spj,s,p

where spj.sno=s.sno and spj.pno=p.pno and city<>'天津' and color='紅'

3．查詢至少選用了供應商S1所供應的全部零件的工程號JNO。

select distinct jno from spj as x

where not exists ( select * from spj as y where sno='s1'

and not exists

(select * from spj as z

where z.pno=y.pno and

z.jno=x.jno))

4．找出工程項目J2使用的各種零件的名稱及其重量。

use shiyan go

select pname,weight from p,spj

where spj.jno='j2' and spj.pno=p.pno

或者

select pname,weight from p

where pno in (select distinct pno from spj where jno='j2')

5．找出上海廠商供應的所有零件號碼。

use shiyan go

select distinct pno from spj

where sno=(select sno

from s

where city='上海')

6．找出使用上海產(chǎn)的零件的工程名稱。

use shiyan go

select distinct jname from s,j,spj

where spj.jno=j.jno and spj.sno=(select sno

from s

where s.city='上海')

或者

select distinct jname from j

where jno in(select jno

from spj

where sno in(select sno

from s

where city=’上海’))

7．找出沒有使用天津產(chǎn)的零件的工程號碼。

use shiyan go

select distinct jno from spj

where sno in (select sno

from s

where city<>'天津')

8．找出重量最輕的紅色零件的零件編號PNO。

use shiyan go

select pno from p

where weight=(select min(weight)

from p

where color='紅')

或者

select top 1 pno from p

where color='紅' order by weight

9．找出供應商與工程所在城市相同的供應商提供的零件號碼。

use shiyan go

select distinct pno from s,j,spj

where s.sno=spj.sno and j.jno=spj.jno and s.city=j.city

10．找出所有這樣的一些＜CITY，CITY，PNAME＞三元組，使得第一個城市的供應商為第二個城市的工程供應零件的名稱為PNAME。

use shiyan go

select distinct s.city CITYA, j.city CITYB,p.pname PNAME from s,p,j,spj

where s.sno=spj.sno and j.jno=spj.jno and p.pno=spj.pno

11．重復第10題，但不檢索兩個CITY值相同的三元組。

use shiyan go

select distinct s.city CITYA, j.city CITYB,p.pname PNAME from s,p,j,spj

where s.sno=spj.sno and j.jno=spj.jno and p.pno=spj.pno and s.city<>j.city

12．找出供應商S1為工程名中含有“廠”字的工程供應的零件數(shù)量總和。

use shiyan go

select sum(qty) TotalQTY from spj

where sno='s1' and jno in(select jno

from j

where jname like '%廠%')