以下题目用到工程供应数据库关系模式:
供应商(供应商号,供应商名,城市)
S(Sno,Sname,City)
零件(零件号,零件名,零件颜色)
P(Pno,Pname,Color)
工程(工程号,工程名 )
J(Jno,Jname)
供应(供应商号,零件号,工程号,供应数量)
SPJ(Sno,Pno,Jno,Qty)
1. 求供应工程号‘J1’零件的供应商名SNAME。(用IN 子查询)
结果应该是S-A
SELECT SNAME FROM S WHERE SNO IN ( SELECT SNO FROM SPJ WHERE JNO='J1' )
2. 求供应工程号‘J1’零件的供应商名SNAME。(用连接, 查询结果要消除重复)
结果应该是S-A
SELECT DISTINCT SNAME FROM S,SPJ WHERE S.SNO=SPJ.SNO AND JNO='J1'
3. 求供应工程号‘J1’零件的供应商名SNAME。(EXISTS子查询)
结果应该是S-A
SELECT SNAME FROM S WHERE EXISTS ( SELECT * FROM SPJ WHERE S.SNO=SPJ.SNO AND JNO='J1' )
4求没有供应过零件的供应商名。(NOT IN)
结果应是S-C, S-D
SELECT SNAME FROM S WHERE SNO NOT IN ( SELECT SNO FROM SPJ WHERE QTY!=0 )