当前位置: 技术问答>java相关
"JAVA新人看看"的SQL文件
来源: 互联网 发布时间：2015-05-23
本文导语: /* Install ToyUniverse database */ set nocount on USE master declare @dttm varchar(55) select @dttm=convert(varchar,getdate(),113) raiserror('Starting installation of ToyUniverse Database at %s ....',1,1,@dttm) with nowa...
/* Install ToyUniverse database */
set nocount on
USE master
declare @dttm varchar(55)
select @dttm=convert(varchar,getdate(),113)
raiserror('Starting installation of ToyUniverse Database at %s ....',1,1,@dttm) with nowait
GO
if exists (select * from sysdatabases where name='ToyUniverse')
begin
raiserror('Dropping existing ToyUniverse database ....',0,1)
DROP database ToyUniverse
end
GO
CHECKPOINT
go
raiserror('Creating ToyUniverse database....',0,1)
go
CREATE DATABASE ToyUniverse
GO
CHECKPOINT
GO
USE ToyUniverse
GO
if db_name() 'ToyUniverse'
raiserror('Error in installToy.SQL, ''USE ToyUniverse'' failed! Killing the SPID now.'
,22,127) with log
GO
execute sp_dboption 'ToyUniverse','trunc. log on chkpt.' ,'true'
checkpoint
USE ToyUniverse
go
/* creating required data types */
execute sp_addtype id ,'char(6)' ,'NOT NULL'
raiserror('Now at the Create Table section ....',0,1)
Go
raiserror('Creating Table Category....',0,1)
create table Category
(
cCategoryId char(3) constraint ct_pk primary key,
cCategory char(20) not null,
vDescription varchar(100)
)
go
raiserror('Creating Table Wrapper....',0,1)
create Table Wrapper
(
cWrapperId char(3) constraint w_id primary key clustered,
vDescription varchar(20),
mWrapperRate money not null,
imPhoto image null,
vWrapperImgPath varchar(50) null
)
go
raiserror('Creating Table ToyBrand....',0,1)
create table ToyBrand
(
cBrandId char(3) constraint TB_pk primary key,
cBrandName char(20) not null,
)
go
/***************************/
raiserror('Creating Table Country....',0,1)
create table Country
(
cCountryId char(3) constraint c_pk primary key,
cCountry char(25) not null,
)
go
raiserror('Creating Table ShippingMode....',0,1)
create table ShippingMode
(
cModeId char(2) constraint spm_pk primary key,
cMode char(25) not null,
iMaxDelDays int,
)
go
raiserror('Creating Table ShippingRate....',0,1)
create table ShippingRate
(
cCountryID char(3) references Country(cCountryId) ,
cModeId char(2) references ShippingMode(cModeId),
mRatePerPound money not null,
constraint SR_PRK primary key(cCountryID,cModeId)
/* need to create composite primary key */
)
raiserror('Creating Table Shopper....',0,1)
create table Shopper
(
cShopperId char(6) constraint s_id primary key CLUSTERED,
cPassword char(10) not null,
vFirstName varchar(20) not null,
vLastName varchar(20) not null,
vEmailId varchar(40) not null,
vAddress varchar(40) not null,
cCity char(15) not null,
cState char(15) not null,
cCountryId char(3) references Country(cCountryId),
cZipCode char(10), /* check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), */
cPhone char(15) not null ,
cCreditCardNo char(16) not null,
vCreditCardType varchar(15) not null,
dExpiryDate datetime
)
go
raiserror('Creating Table Toys....',0,1)
/* toys table */
create table Toys
(
cToyId char(6) check(cToyId like('[0-9][0-9][0-9][0-9][0-9][0-9]') )
constraint t_id primary key clustered,
vToyName varchar(20) not null,
vToyDescription varchar(250),
cCategoryId char(3) references Category(cCategoryId) ,
mToyRate money not null,
cBrandId char(3)references ToyBrand(cBrandId),
imPhoto image,
siToyQoh smallint not null,
siLowerAge smallint not null,
siUpperAge smallint not null,
siToyWeight smallint,
vToyImgPath varchar(50) null
)
go
/* */
/* ShoppingCart */
raiserror('Creating Table ShoppingCart....',0,1)
create table ShoppingCart
(
cCartId char(6) not null,
cToyId char(6)REFERENCES Toys(cToyId),/* foreign key to Toys table */
siQty smallint not null,
constraint SCHP_PK primary key(cCartId,cToyId)
)
go
/***********************/
/* Order */
raiserror('Creating Table Order....',0,1)
create table Orders
(
cOrderNo char(6) constraint CO_PK Primary key,
dOrderDate datetime not null,
cCartId char(6) not null,
cShopperId char(6) not null references Shopper(cShopperId),
cShippingModeId char(2) null references ShippingMode(cModeId),
mShippingCharges money null,
mGiftWrapCharges money null,
cOrderProcessed char null ,
mTotalCost money null,
dExpDelDate DateTime null
)
go
/* OrderDetail table */
raiserror('Creating Table OrderDetails....',0,1)
create table OrderDetail
(
cOrderNo char(6) references Orders(cOrderNo),
cToyId char(6) references toys(cToyId),
siQty smallint not null,
cGiftWrap char null,
cWrapperId char(3) references Wrapper(cWrapperId) null,
vMessage varchar(256) null,
mToyCost money null,
constraint z_key primary key(cOrderNo,cToyId)
)
go
/* shipping mode */
/* Shipment */
raiserror('Creating Shippment....',0,1)
create table Shipment
(
cOrderNo char(6) REFERENCES Orders(cOrderNo) constraint SHP_PK primary key,
dShipmentDate datetime null, /* cannot be before order date */
cDeliveryStatus char null,
dActualDeliveryDate datetime null
)
go
/* Recipient table */
raiserror('Creating Table Recipient....',0,1)
create table Recipient
(
cOrderNo char(6) REFERENCES Orders(cOrderNo) constraint RCP_PK primary key, /* foreign key to order table */
vFirstName varchar(20) not null,
vLastName varchar(20) not null,
vAddress varchar(20) not null,
cCity char(15) not null,
cState char(15) not null,
cCountryId char(3) references Country(cCountryId),
cZipCode char(10) check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
cPhone char(15)
)
go
/* pick of the month */
raiserror('Creating Table PickOfMonth....',0,1)
create table PickOfMonth
(
cToyId char(6)REFERENCES Toys(cToyId), /* foreign key to toy table */
siMonth smallint,
iYear int,
iTotalSold int,
constraint POM_PK primary key(cToyId,siMonth,iYear)
)
go
/*****************************************************/
-- Populating data into the tables
/********************/
insert into Category values('001','Activity','Activity toys encourage the childs social skills and interest in the world around them.')
insert into Category values('002','Dolls','A wide range of dolls from all the leading brands.')
/* data for country */
insert into Country values('001','United states of America')
/*data into shopper */
insert into shopper values('000001','angels','Angela','Smith','angelas@qmail.com','16223 Radiance Court','Woodbridge','Virginia','001','22191','227-2344','6947343412896785','Master Card',"08/09/1999")
insert into shopper values('000002','guide','Barbara','Johnson','barbaraj@speedmail.com','227 Beach Ave.','Sunnyvale','California','001','94087-1147','123-5673','5345146765854356','Master Card',"04/10/1999")
/* data for toys */
CREATE PROCEDURE prcGenOrder
@OrderNo char(6)OUTPUT
as
SELECT @OrderNo=Max(cOrderNo) FROM Orders
SELECT @OrderNo=
CASE
WHEN @OrderNo >=0 and @OrderNo=9 and @OrderNo=99 and @OrderNo=999 and @OrderNo=9999 and @OrderNo=99999 Then Convert(char,@OrderNo+1)
END
RETURN
go
Create view vwOrderWrapper
as
SELECT cOrderNo,cToyId,siQty,vDescription,mWrapperRate
FROM OrderDetail JOIN Wrapper
ON OrderDetail.cWrapperId=Wrapper.cWrapperId
go
raiserror('The ToyUniverse database in now ready for use....',0,1)
set nocount on
USE master
declare @dttm varchar(55)
select @dttm=convert(varchar,getdate(),113)
raiserror('Starting installation of ToyUniverse Database at %s ....',1,1,@dttm) with nowait
GO
if exists (select * from sysdatabases where name='ToyUniverse')
begin
raiserror('Dropping existing ToyUniverse database ....',0,1)
DROP database ToyUniverse
end
GO
CHECKPOINT
go
raiserror('Creating ToyUniverse database....',0,1)
go
CREATE DATABASE ToyUniverse
GO
CHECKPOINT
GO
USE ToyUniverse
GO
if db_name() 'ToyUniverse'
raiserror('Error in installToy.SQL, ''USE ToyUniverse'' failed! Killing the SPID now.'
,22,127) with log
GO
execute sp_dboption 'ToyUniverse','trunc. log on chkpt.' ,'true'
checkpoint
USE ToyUniverse
go
/* creating required data types */
execute sp_addtype id ,'char(6)' ,'NOT NULL'
raiserror('Now at the Create Table section ....',0,1)
Go
raiserror('Creating Table Category....',0,1)
create table Category
(
cCategoryId char(3) constraint ct_pk primary key,
cCategory char(20) not null,
vDescription varchar(100)
)
go
raiserror('Creating Table Wrapper....',0,1)
create Table Wrapper
(
cWrapperId char(3) constraint w_id primary key clustered,
vDescription varchar(20),
mWrapperRate money not null,
imPhoto image null,
vWrapperImgPath varchar(50) null
)
go
raiserror('Creating Table ToyBrand....',0,1)
create table ToyBrand
(
cBrandId char(3) constraint TB_pk primary key,
cBrandName char(20) not null,
)
go
/***************************/
raiserror('Creating Table Country....',0,1)
create table Country
(
cCountryId char(3) constraint c_pk primary key,
cCountry char(25) not null,
)
go
raiserror('Creating Table ShippingMode....',0,1)
create table ShippingMode
(
cModeId char(2) constraint spm_pk primary key,
cMode char(25) not null,
iMaxDelDays int,
)
go
raiserror('Creating Table ShippingRate....',0,1)
create table ShippingRate
(
cCountryID char(3) references Country(cCountryId) ,
cModeId char(2) references ShippingMode(cModeId),
mRatePerPound money not null,
constraint SR_PRK primary key(cCountryID,cModeId)
/* need to create composite primary key */
)
raiserror('Creating Table Shopper....',0,1)
create table Shopper
(
cShopperId char(6) constraint s_id primary key CLUSTERED,
cPassword char(10) not null,
vFirstName varchar(20) not null,
vLastName varchar(20) not null,
vEmailId varchar(40) not null,
vAddress varchar(40) not null,
cCity char(15) not null,
cState char(15) not null,
cCountryId char(3) references Country(cCountryId),
cZipCode char(10), /* check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), */
cPhone char(15) not null ,
cCreditCardNo char(16) not null,
vCreditCardType varchar(15) not null,
dExpiryDate datetime
)
go
raiserror('Creating Table Toys....',0,1)
/* toys table */
create table Toys
(
cToyId char(6) check(cToyId like('[0-9][0-9][0-9][0-9][0-9][0-9]') )
constraint t_id primary key clustered,
vToyName varchar(20) not null,
vToyDescription varchar(250),
cCategoryId char(3) references Category(cCategoryId) ,
mToyRate money not null,
cBrandId char(3)references ToyBrand(cBrandId),
imPhoto image,
siToyQoh smallint not null,
siLowerAge smallint not null,
siUpperAge smallint not null,
siToyWeight smallint,
vToyImgPath varchar(50) null
)
go
/* */
/* ShoppingCart */
raiserror('Creating Table ShoppingCart....',0,1)
create table ShoppingCart
(
cCartId char(6) not null,
cToyId char(6)REFERENCES Toys(cToyId),/* foreign key to Toys table */
siQty smallint not null,
constraint SCHP_PK primary key(cCartId,cToyId)
)
go
/***********************/
/* Order */
raiserror('Creating Table Order....',0,1)
create table Orders
(
cOrderNo char(6) constraint CO_PK Primary key,
dOrderDate datetime not null,
cCartId char(6) not null,
cShopperId char(6) not null references Shopper(cShopperId),
cShippingModeId char(2) null references ShippingMode(cModeId),
mShippingCharges money null,
mGiftWrapCharges money null,
cOrderProcessed char null ,
mTotalCost money null,
dExpDelDate DateTime null
)
go
/* OrderDetail table */
raiserror('Creating Table OrderDetails....',0,1)
create table OrderDetail
(
cOrderNo char(6) references Orders(cOrderNo),
cToyId char(6) references toys(cToyId),
siQty smallint not null,
cGiftWrap char null,
cWrapperId char(3) references Wrapper(cWrapperId) null,
vMessage varchar(256) null,
mToyCost money null,
constraint z_key primary key(cOrderNo,cToyId)
)
go
/* shipping mode */
/* Shipment */
raiserror('Creating Shippment....',0,1)
create table Shipment
(
cOrderNo char(6) REFERENCES Orders(cOrderNo) constraint SHP_PK primary key,
dShipmentDate datetime null, /* cannot be before order date */
cDeliveryStatus char null,
dActualDeliveryDate datetime null
)
go
/* Recipient table */
raiserror('Creating Table Recipient....',0,1)
create table Recipient
(
cOrderNo char(6) REFERENCES Orders(cOrderNo) constraint RCP_PK primary key, /* foreign key to order table */
vFirstName varchar(20) not null,
vLastName varchar(20) not null,
vAddress varchar(20) not null,
cCity char(15) not null,
cState char(15) not null,
cCountryId char(3) references Country(cCountryId),
cZipCode char(10) check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
cPhone char(15)
)
go
/* pick of the month */
raiserror('Creating Table PickOfMonth....',0,1)
create table PickOfMonth
(
cToyId char(6)REFERENCES Toys(cToyId), /* foreign key to toy table */
siMonth smallint,
iYear int,
iTotalSold int,
constraint POM_PK primary key(cToyId,siMonth,iYear)
)
go
/*****************************************************/
-- Populating data into the tables
/********************/
insert into Category values('001','Activity','Activity toys encourage the childs social skills and interest in the world around them.')
insert into Category values('002','Dolls','A wide range of dolls from all the leading brands.')
/* data for country */
insert into Country values('001','United states of America')
/*data into shopper */
insert into shopper values('000001','angels','Angela','Smith','angelas@qmail.com','16223 Radiance Court','Woodbridge','Virginia','001','22191','227-2344','6947343412896785','Master Card',"08/09/1999")
insert into shopper values('000002','guide','Barbara','Johnson','barbaraj@speedmail.com','227 Beach Ave.','Sunnyvale','California','001','94087-1147','123-5673','5345146765854356','Master Card',"04/10/1999")
/* data for toys */
CREATE PROCEDURE prcGenOrder
@OrderNo char(6)OUTPUT
as
SELECT @OrderNo=Max(cOrderNo) FROM Orders
SELECT @OrderNo=
CASE
WHEN @OrderNo >=0 and @OrderNo=9 and @OrderNo=99 and @OrderNo=999 and @OrderNo=9999 and @OrderNo=99999 Then Convert(char,@OrderNo+1)
END
RETURN
go
Create view vwOrderWrapper
as
SELECT cOrderNo,cToyId,siQty,vDescription,mWrapperRate
FROM OrderDetail JOIN Wrapper
ON OrderDetail.cWrapperId=Wrapper.cWrapperId
go
raiserror('The ToyUniverse database in now ready for use....',0,1)
|
老大，没必要搞地这么复杂，上个帖子我已经回复你了
怎么你提问不抓关键啊？
怎么你提问不抓关键啊？
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。