实验六 多表查询
四、实验内容
1．查询为工程J1供应红色零件的供应商号码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．查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号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．找出使用上海产的零件的工程名称。
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．找出没有使用天津产的零件的工程号码。
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为工程名中含有“厂”字的工程供应的零件数量总和。
use shiyan go
select sum(qty) TotalQTY from spj
where sno='s1' and jno in(select jno
from j
where jname like '%厂%')
