数据库原理与应用 考试复习资料整理(2)

FROM SRJ

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=’红’

(4) 求没有使用天津供应商生产的红色零件的工程号JNO；

SELECT JNO

FROM J

WHERE NOT EXISTS

(SELECT*

FROM SPJ.JNO=J.JNO

AND SNO IN

(SELECT SNO

FROM S

WHERE SPJ.JNO=’天津’)

AND PNO IN

(SELECT PNO

FROM P

WHERE COLOR=’红’))

或

SELECT JNO

FROM J

WHERE NOT EXISTS

(SELECT*

FROM SPJ,S,P

WHERE SPJ.JNO=J.JNO

AND SPJ.SNO=S.SNO

AND SPJ.PNO=P.PNO

AND S.CITY=’天津’

AND P.COLOR=’红’)

四.数据库设计与查询实现（共20分）

1. 在MS SQL2000中创建数据库, 主文件(. mdf)大小为10MB，日志文件大小为3MB。

数据库将驻留在'C:\Program Files\Microsoft SQL Server\MSSQL\data\S_T.MDF'中。

用SQL语言实现此数据库设计.