create database 订货数据库
create schema xyz
create table xyz.仓库
(仓库号 char(6) primary key check(substring(仓库号,1,2)>='AA'and substring(仓库号,1,2)<='ZZ'and substring(仓库号,3,4)>='0000'and
substring(仓库号,3,4)<='9999'),
城市 char(10) unique not null,
面积 numeric check(面积>=50)
)
create table xyz.职工
(职工号 char(8) primary key check (right(职工号,2)>='00' and right(职工号,2)<='99')，
仓库号 as convert(char(6),left(职工号,6)) persisted foreign key references xyz.仓库(仓库号),
经理 char(8) foreign key references xyz.职工(职工号),
工资 money check(工资 between 1000 and 10000)
)
create table xyz.供应商
(供应商号 char(4) primary key check(left(供应商号,1)>'R'and left(供应商号,1)<'T'and Right(供应商号,3)>='000' and Right(供应商号,3)<='999'),
供应商名 char(16),
地址 char(30)
)
create table xyz.订购单
(职工号 char(8) not null foreign key references xyz .职工(职工号),
供应商号 char(4) foreign key references xyz.供应商(供应商号),
订购单号 char(6) primary key check(left( 订购单号,1)>'N' and left( 订购单号,1)<'P'and substring(订购单号,2,1)>'Q'and substring(订购单号,2,1)>'S'and right(订购单号,4)>=‘0000’
and Right(订购单号,4)<='9999'),
订购日期 datetime,
金额 money default null
)
create table xyz.订购单明细
(订购单号 char(6) not null foreign key references xyz.订购单(订购单号),
序号 numeric(2) primary key,
产品名称 char(20),
单价 money default null check(单价>0),
数量 int not null check(数量>0)
)
insert into xyz.仓库 values('AB1101','赣州',500)
insert into xyz.仓库 values('CD1102','广州',1000)
insert into xyz.仓库 values('EF1103','惠州',1200)
insert into xyz.仓库 values('GH1104','上海',2000)
insert into xyz.仓库 values('RS1105','北京',3000)
insert into xyz.职工 values('AB110123','罗鹏',1500)
insert into xyz.职工 values('AB110124','杨靖',2000)
insert into xyz.职工 values('CD110230','黄蓉',3500)
insert into xyz.职工 values('CD110238','杨龙',4500)
insert into xyz.职工 values('EF110326','俞婷',2500)
insert into xyz.职工 values('EF110363','黄婷',7500)
insert into xyz.职工 values('GH110452', '廖晨',1500)
insert into xyz.职工 values('GH110423','舒怡',6500)
insert into xyz.职工 values('RS110518','杨进',1700)
insert into xyz.职工 values('RS110523','钟鑫 ',1800)
insert into xyz.供应商 values('S123','李四','赣州')
insert into xyz.供应商 values('S456','杨大帅','吉安')
insert into xyz.供应商 values('S789','杨过','惠州')
insert into xyz.供应商 values('S110','罗鹏','吉安')
insert into xyz.供应商 values('S129','古拯','安远')
insert into xyz.订购单 values('AB110123','S123','OR1101','20111102',1000)
insert into xyz.订购单 values('AB110124','S456','OR1102','20111103',1200)


insert into xyz.订购单 values('CD110230','S789','OR1103','20111104',1500)
insert into xyz.订购单 values('CD110238','S110','OR1104','20111105',1800)
insert into xyz.订购单 values('EF110326','S129','OR1105','20111106',2000)
insert into xyz.订购单 values('EF110363','S123','OR1106','20111107',2100)
insert into xyz.订购单 values('GH110452','S456','OR1107','20111108',2400)
insert into xyz.订购单 values('GH110423','S789','OR1108','20111109',2500)
insert into xyz.订购单 values('RS110518','S110','OR1109','20111120',3000)
insert into xyz.订购单 values('RS110523','S129','OR1110','20111122',3200)
insert into xyz.订购单 values('AB110123','S123','OR1112','20111123',3400)
insert into xyz.订购单 values('AB110124','S456','OR1113','20111124',3500)
insert into xyz.订购单 values('CD110230','S789','OR1114','20111125',3600)
insert into xyz.订购单 values('CD110238','S110','OR1115','20111126',4000)
insert into xyz.订购单 values('EF110326','S129','OR1116','20111127',4500)
insert into xyz.订购单 values('EF110363','S123','OR1117','20111128',4800)
insert into xyz.订购单 values('GH110452','S456','OR1118','20111129',6000)
insert into xyz.订购单 values('GH110423','S789','OR1119','20111130',7000)
insert into xyz.订购单 values('RS110518','S110','OR1120','20111002',7500)
insert into xyz.订购单 values('RS110523','S129','OR1121','20111003',8000)
insert into xyz.订购单明细 values('OR1101',11,'香蕉',2,500)
insert into xyz.订购单明细 values('OR1102',12,'苹果',3,500)
insert into xyz.订购单明细 values('OR1103',13,'橘子',4,500)
insert into xyz.订购单明细 values('OR1104',14,'李子',5,500)
insert into xyz.订购单明细 values('OR1105',15,'西红柿',6,500)
insert into xyz.订购单明细 values('OR1106',16,'桃子',7,500)
insert into xyz.订购单明细 values('OR1107',17,'菠萝',8,500)
insert into xyz.订购单明细 values('OR1108',18,'西瓜',9,500)
insert into xyz.订购单明细 values('OR1109',19,'冬瓜',1,500)
insert into xyz.订购单明细 values('OR1110',20,'南瓜',2,500)
insert into xyz.订购单明细 values('OR1112',21,'葡萄',3,500)
insert into xyz.订购单明细 values('OR1113',22,'鸡蛋',4,500)
insert into xyz.订购单明细 values('OR1114',23,'马铃薯',5,500)
insert into xyz.订购单明细 values('OR1115',24,'萝卜',6,500)
insert into xyz.订购单明细 values('OR1116',25,'非菜',7,500)
insert into xyz.订购单明细 values('OR1117',26,'黄瓜',8,500)
insert into xyz.订购单明细 values('OR1118',27,'青椒',9,500)
insert into xyz.订购单明细 values('OR1119',28,'茄子',1,500)
insert into xyz.订购单明细 values('OR1120',29,'圆白菜',2,500)
insert into xyz.订购单明细 values('OR1121',30,'菠菜',3,500)
insert into xyz.订购单明细 values('OR1101',31,'香菜',4,500)
insert into xyz.订购单明细 values('OR1102',32,'菜花',5,500)
insert into xyz.订购单明细 values('OR1103',33,'油菜',6,500)
insert into xyz.订购单明细 values('OR1104',34,'藕',7,500)


insert into xyz.订购单明细 values('OR1105',35,'生菜',8,500)
insert into xyz.订购单明细 values('OR1106',36,'韭黄',9,500)
insert into xyz.订购单明细 values('OR1107',37,'毛豆',1,500)
insert into xyz.订购单明细 values('OR1108',38,'大蒜',2,500)
insert into xyz.订购单明细 values('OR1109',39,'盛笋',3,500)
insert into xyz.订购单明细 values('OR1110',40,'红薯',4,500)
insert into xyz.订购单明细 values('OR1112',41,'香蕉',5,500)
insert into xyz.订购单明细 values('OR1113',42,'麻山药',6,500)
insert into xyz.订购单明细 values('OR1114',43,'金黄',7,500)
insert into xyz.订购单明细 values('OR1115',44,'百合',8,500)
insert into xyz.订购单明细 values('OR1116',45,'四季豆',9,500)
insert into xyz.订购单明细 values('OR1117',46,'西兰花',1,500)
insert into xyz.订购单明细 values('OR1118',47,'苦瓜',2,500)
insert into xyz.订购单明细 values('OR1119',48,'蘑菇',3,500)
insert into xyz.订购单明细 values('OR1120',49,'哈密瓜',4,500)
insert into xyz.订购单明细 values('OR1121',50,'草莓',5,500)
insert into xyz.订购单明细 values('OR1101',51,'核桃',6,500)
insert into xyz.订购单明细 values('OR1102',52,'杏仁',7,500)
insert into xyz.订购单明细 values('OR1103',53,'杨梅',8,500)
insert into xyz.订购单明细 values('OR1104',54,'花生',9,500)
insert into xyz.订购单明细 values('OR1105',55,'瓜子',2,500)
insert into xyz.订购单明细 values('OR1106',56,'人参果',3,500)
insert into xyz.订购单明细 values('OR1107',57,'榴莲',4,500)
insert into xyz.订购单明细 values('OR1108',58,'芒果',2,500)
insert into xyz.订购单明细 values('OR1109',59,'甘蔗',6,500)
insert into xyz.订购单明细 values('OR1120',60,'小番茄',2,500)
select工资from xyz.职工
select 职工号，仓库号 from xyz.职工
where 工资<=5000
select * from xyz.订购单
where 供应商号 is not null
select 订购单.职工号 ,经理,订购单号 from
xyz. 职工 join xyz.订购单 on 职工. 职工号=订购单.职工号
select 订购单.供应商号,供应商名,订购单号,订购日期 from
xyz.供应商 right join xyz.订购单 on 供应商.供应商号=订购单.供应商号
select 仓库.仓库号, 城市, 职工.职工号,供应商.供应商号 ,订购单号 from
xyz.仓库 join xyz.职工 on 仓库.仓库号=职工.仓库号
join xyz.订购单 on 职工.职工号=订购单.职工号 join
xyz.供应商 on 订购单.供应商号=供应商.供应商号
where 地址='赣州'
select *from xyz.仓库
where 仓库号=any
(select 仓库号 from xyz.职工
where 职工号 is null)
select 职工.仓库号, avg(面积) 平均面积 from xyz. 职工 join
xyz.仓库 on 职工.仓库号 =仓库.仓库号
where 工资>4000
group by 职工.仓库号
select 仓库.仓库号,职工号,经理,工资 from
xyz.仓库 join xyz.职工 on 仓库.仓库号 = 职工.仓库号
order by 仓库.仓库号
compute avg(工资),sum(工资）by 仓库.仓库号
compute avg(工资),sum(工资)


美女，话说你怎么有我做的数据库文件啊