--该题创建表时同时创建主键、外键。先创建表，再创建主键、外键的方法参见照实训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