departmentalstore.sql

来自「用户名：liuxinrui 密码：zhongyong 您可以在 linu」· SQL 代码 · 共 88 行

/* ************************* 创建DepartmentalStore库 ************************* */
go
use master
go

if exists (select * from sysdatabases where name='DepartmentalStore')
drop database DepartmentalStore

go

create database DepartmentalStore
on 
(name = DepartmentalStore_dat,
filename = 'D:\DepartmentalStore.mdf',size = 12,maxsize = 100,filegrowth = 2)
log on
(name = 'DepartmentalStore_log',filename = 'D:\DepartmentalStore.ldf',size = 4,maxsize =50,filegrowth = 2)

go

use DepartmentalStore

go

create table Product
(
	iProductId int identity constraint pk_prodid primary key,
	vProductName varchar(20) not null,
	cMetric char(4) not null,
	fSafeValue float not null,
)


go

create table Supplier
(
	cSupplierId char(4) constraint pk_suppid primary key,
	vSupplierName varchar(50) not null,
	vSupplierAddress varchar(100) not null,
	cSupplierPhone char(15) not null,
	iProductId int references Product(iProductId) 	
)


go

create table Trade
(
	iTradeId int identity constraint pk_trid primary key,
	cTradeType char(4) not null,
	cTradeTime char(15) not null,
	vOtherName varchar(30) not null,
	cOtherPhone char(15) not null,
	iProductId int references Product(iProductId),
	mPrice float not null,
	fProductCount float not null,
	mPriceSum float not null
)

go

------------------------------------创建存储过程----------------------------------------------------
--显示所有的产品，并显示相应的现有库存。
create proc proc_ProductInfo  
as
begin
select Product.iProductId,Product.vProductName,Product.cMetric,Product.fSafeValue,'NowValue'=sum(fProductCount)
from Trade
join Product
on Trade.iProductId = Product.iProductId
group by Product.iProductId,Product.vProductName,Product.cMetric,Product.fSafeValue
end

go

--显示低于安全库存的产品，及现有库存量。
create proc proc_NeedOrder 
as
begin
select Product.iProductId,Product.vProductName,Product.cMetric,Product.fSafeValue,'NowValue'=sum(fProductCount)
from Trade
join Product
on Trade.iProductId = Product.iProductId
group by Product.iProductId,Product.vProductName,Product.cMetric,Product.fSafeValue
having Product.fSafeValue>sum(fProductCount)
end

go