数据库系统概论（第五版）查询习题解

–求至少用了供应商S1所供应的全部零件的工程号JNO
select DISTINCT SNO
FROM SPJ
WHERE SNO NOT IN(
SELECT SPJ.SNO
FROM SPJ,P,S
WHERE SPJ.PNO=P.PNO AND SPJ.SNO=S.SNO AND P.COLOR=‘红’ AND S.CITY=‘天津’
)

–求供应工程J1零件为红色的供应商号码SNO
select DISTINCT SNO
from SPJ,P
where JNO='J1’and SPJ.PNO IN(
select DISTINCT PNO
FROM P
WHERE COLOR=‘红’
)

–求供应工程J1零件P1的供应商号码SNO
select DISTINCT SNO
from SPJ
where JNO=‘J1’ and PNO=‘P1’

–求供应工程J1零件的供应商号码SNO
select DISTINCT SNO
from SPJ
where JNO=‘J1’

–找出所有供应商的姓名和所在城市:
select SNAME,CITY
from S

–找出所有零件的名称，颜色，重量
select PNAME,COLOR,WEIGTH
from P

–找出使用供应商s1所供应零件的工程号码
select DISTINCT JNO
FROM SPJ
WHERE SNO=‘S1’

–找出工程项目J2使用的各种零件的名称及其数量
select P.PNAME,QTY
FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND JNO=‘J2’

–找出上海厂商供应的所有零件号码
select DISTINCT PNO
FROM SPJ
WHERE SPJ.SNO IN(
SELECT SNO
FROM S
WHERE CITY=‘上海’
)

–找出使用上海产的零件的工程名称
select DISTINCT J.JNAME
FROM J,S,SPJ
WHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO
AND S.CITY=‘上海’

–找出没有使用天津产的零件的工程号码
select DISTINCT SPJ.JNO
FROM SPJ
WHERE SPJ.JNO NOT IN (
SELECT SPJ.JNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY=‘天津’
)

–把全部红色零件的颜色改为蓝色
update P
set COLOR=‘红’

–由S5供给J4的零件P6改为由S3供应

update SPJ
set SNO=‘S3’
WHERE SNO='S5’AND JNO=‘J4’