37205-00 - SQL Server 2005课后实训参考答案(5)

--该题创建表时同时创建主键、外键。先创建表，再创建主键、外键的方法参见照实训5_3 Use Sale Go

CREATE TABLE Customer

(CusNo nvarchar(3) Primary key, CusName nvarchar(10)not null, Address nvarchar(20), Tel Char(20)) Go

/*创建产品表*/

CREATE TABLE Product

(ProNo nvarchar(5)Primary key, ProName nvarchar(20)not null, Price Decimal(8,2)not null, Stocks Decimal(8,0)not null) Go

/*创建入库表*/

徐人凤

共 21 页 第 21 页

5/19/2015 21

21

CREATE TABLE ProIn (InputDate DateTime not null,

ProNo nvarchar(5)References Product(ProNo), Quantity Decimal(6,0)not null) Go

/*创建销售表*/

CREATE TABLE ProOut (SaleDate DateTime not null,

CusNo nvarchar(3)not null References Customer(CusNo),

ProNo nvarchar(5)not null References Product(ProNo),

Quantity Decimal(6,0)not null) Go

--向Customer表输入数据-- INSERT INTO

Customer VALUES('001','杨婷','深圳','0755-22221111') INSERT INTO

Customer VALUES('002','陈萍','深圳

徐人凤

共 22 页 第 22 页

5/19/2015 22

22

','0755-22223333') INSERT INTO

Customer VALUES('003','李东','深圳','0755-22225555') INSERT INTO

Customer VALUES('004','叶合','广州','020-22227777') INSERT INTO

Customer VALUES('005','谭新','广州','020-22229999') Go

--向Product表中插入数据-- INSERT INTO

Product VALUES('00001','电视',3000.00,800) INSERT INTO

Product VALUES('00002','空调',2000.00,500) INSERT INTO

Product VALUES('00003','床',1000.00,300) INSERT INTO

Product VALUES('00004','餐桌',1500.00,200) INSERT INTO

徐人凤

共 23 页 第 23 页

5/19/2015 23

23

Product VALUES('00005','音响',5000.00,600) INSERT INTO

Product VALUES('00006','沙发',6000.00,100) Go

--向ProIn表中插入数据-- INSERT INTO

ProIn VALUES('2006-1-1','00001',10) INSERT INTO

ProIn VALUES('2006-1-1','00002',5) INSERT INTO

ProIn VALUES('2006-1-2','00001',5) INSERT INTO

ProIn VALUES('2006-1-2','00003',10) INSERT INTO

ProIn VALUES('2006-1-3','00001',10) INSERT INTO

ProIn VALUES('2006-2-1','00003',20) INSERT INTO

ProIn VALUES('2006-2-2','00001',10) INSERT INTO

ProIn VALUES('2006-2-3','00004',30)

徐人凤

共 24 页 第 24 页

5/19/2015 24

24

INSERT INTO

ProIn VALUES('2006-3-3','00003',20) Go

--向ProOut表中插入数据-- INSERT INTO

ProOut VALUES('2006-1-1','001','00001',10) INSERT INTO

ProOut VALUES('2006-1-2','001','00002',5) INSERT INTO

ProOut VALUES('2006-1-3','002','00001',5) INSERT INTO

ProOut VALUES('2006-2-1','002','00003',10) INSERT INTO

ProOut VALUES('2006-2-2','001','00001',10) INSERT INTO

ProOut VALUES('2006-2-3','001','00003',20) INSERT INTO

ProOut VALUES('2006-3-2','003','00001',10) INSERT INTO

ProOut VALUES('2006-3-2','003','00004',30) INSERT INTO

徐人凤

共 25 页 第 25 页

5/19/2015 25

25