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

CREATE DATABASE S_T

ON PRIMARY(NAME=S_T,FILENAME='C:\ProgramFiles\Microsoft SQL

Server\MSSQL\data\S_T_Data.MDF',

SIZE=10MB,

MAXSIZE=15MB,

FILEGROWTH=20%)

LOG ON (NAME=S_T_Log,

FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\data\S_T_Log.LDF', SIZE=3MB,

MAXSIZE=5MB,

FILEGROWTH=1MB)

2..第三章第5题1-7

(1.)找出所以供应商的姓名和所在城市

SELECT SNAME.CITY

FROM S

（2.）找出所以零件的名称、颜色、重量

SELSCT PNAME,COLOR,WEIGHT

FROM P

（3.）找出使用供应商S1所供应零件的工程号码

SELECT JNO

FROM SPJ

WHERE SNO=’S1’

（4.）找出工程项目J2使用的各种零件的名称及其数量

SELECT P.PNAME,SPJ.QTY

FROM P,SPJ

WHERE P.PNO=SPJ.PNO

AND SPJ.JNO=’J2’

（5.）找出上海供应商的所有零件号码

SELECT JNAME

FROM J,SPJ IN

(SELECT SNO

FROM S

WHERE CITY=’上海’)

（6.）找出使用上海产的练剑的工程名称

SELECT JANME

FROM J,SPJ,S

WHERE J.JNO=SPJ.JNO

AND SPJ.SNO=S.SNO

AND S.CITY=’上海’

（7.）找出没有使用天津产的练剑的工程号码

SELECT JNO

FROM J

WHERE NOT EXISTS

(SELECT *

FROM SPJ

WEHERE SPJ.JNO=J.JNO

AND SNO IN