3db.txt

the original code of the book the principle and practice in sql server 2005

use 企业销售管理系统
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg1','wh1','刘岗','女',1250)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg2','wh1','林安平',null,1350)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg3','wh2','张可','女',2050)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg4','wh1','吴平台','男',1850)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg5','wh3','王峰','女',1960)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg6','wh2','吴艳平','女',1830)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg7','wh4','张亮','男',1850)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg8','wh2','刘或生','男',1750)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg9','wh3','王静','女',1250)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg10','wh4','李明','男',1850)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg11','wh3','鄱平','男',1890)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg12','wh4','李宁','女',1550)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg13','wh2','吴忠诚','男',2150)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg14','wh4','周围','男',1850)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg15','wh1','林林',null,1910)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg16','wh2','陈濡','女',2050)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg17','wh4','吴生亮','男',1850)
insert into 职工(职工号,仓库号,姓名,性别,工资)
values('zg18','wh3','王平峰','女',1962)









use 企业销售管理系统
create table 供应商
(
  供应商ID int identity(1,1) primary key,
  供应商号 varchar(50) unique,
  供应商名 varchar(50),
  城市 varchar(50),
  联系方式 varchar(50),
  电子邮件 varchar(100) check(电子邮件 like '%@%.%') default '[e-mail]'

)

use 企业销售管理系统
insert into 供应商(供应商号,供应商名,城市,联系方式,电子邮件)values
('s1','青岛三元电子厂','青岛','0532-83698754','[e-mail]')
insert into 供应商(供应商号,供应商名,城市,联系方式,电子邮件)values
('s2','济南轻工业集团','济南','0531-83698754','[e-mail]')
insert into 供应商(供应商号,供应商名,城市,联系方式,电子邮件)values
('s3','济南电子元件有限公司','济南','0531-83698684','[e-mail]')
insert into 供应商(供应商号,供应商名,城市,联系方式,电子邮件)values
('s4','上海天桥厂','上海','83699684','[e-mail]')
insert into 供应商(供应商号,供应商名,城市,联系方式,电子邮件)values
('s5','青岛天桥厂','青岛','83698684','[e-mail]')


use 企业销售管理系统
create table 订购单
(
  订购单ID int identity(1,1) primary key,
  供应商号 varchar(50) references 供应商(供应商号),
  职工号 varchar(50) references 职工(职工号),
  订购单号 varchar(50) unique,
  订购日期 datetime,
  金额 int
)

use 企业销售管理系统
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s5','zg1','or1','2008-03-01',5000)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s1','zg2','or2','2008-03-02',8600)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s1','zg1','or3','2008-03-03',12000)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s5','zg1','or4','2008-03-04',16000)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s3','zg1','or5','2008-03-05',5000)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s3','zg11','or6','2008-03-06',8600)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s4','zg13','or7','2008-03-07',12000)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s5','zg15','or8','2008-03-08',16600)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s5','zg4','or9','2008-03-09',8120)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s2','zg8','or10','2008-03-10',16600)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s4','zg8','or11','2008-03-11',19641)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s2','zg10','or12','2008-03-12',16600)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s5','zg9','or13','2008-03-13',8120)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s2','zg9','or14','2008-03-14',5784)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s4','zg7','or15','2008-03-15',19641)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s2','zg18','or16','2008-03-16',16600)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s5','zg5','or17','2008-03-17',8120)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s2','zg2','or18','2008-03-18',5784)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s4','zg15','or19','2008-03-19',19641)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s2','zg15','or20','2008-03-20',16600)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s5','zg16','or21','2008-03-21',8120)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s2','zg16','or22','2008-03-22',5784)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s4','zg17','or23','2008-03-01',19641)
insert into 订购单(供应商号,职工号,订购单号,订购日期,金额)
values ('s2','zg18','or24','2008-06-01',16600)