create table stock(
sto_id int identity(1,1) primary key,
sto_barcode varchar(13) not null,
sto_name varchar(100) not null,
sto_supplier varchar(100),
sto_price decimal(10,2),
sto_unit smallint not null default 1,
sto_currency_type varchar(3),
sto_quantity int not null default 0,
sto_create_date date,
sto_update_datetime datetime,
sto_status varchar(10)
)
create table addresses(
add_id int identity(1,1) primary key,
add_address1 varchar(100) not null,
add_address2 varchar(50) not null,
add_address3 varchar(50) not null,
add_address4 varchar(50) not null,
add_address5 varchar(50) not null,
add_address6 varchar(20) not null,
add_create_date date not null default GETDATE(),
add_update_datetime datetime,
add_status varchar(10)
)
create table users(
use_id int identity(1,1) primary key,
use_add_id int not null,
use_ship_add_id int not null,
use_firstname varchar(20) not null,
use_surname varchar(20) not null,
use_gender varchar(6) not null,
use_dob date not null,
use_username varchar(20) not null,
use_password varchar(20) not null,
use_star smallint not null default 0,
use_create_date date not null default GETDATE(),
use_update_datetime datetime,
use_status varchar(10)
)
create table orders(
ord_id int identity(1,1) primary key,
ord_use_id int not null,
ord_sto_id int not null,
ord_quantity int not null default 0,
ord_bill decimal(10,2),
ord_payment_status varchar(10),
ord_shippment_status varchar(10),
ord_ordernumber varchar(20) not null,
ord_create_datetime datetime not null default GETDATE(),
ord_update_datetime datetime,
ord_status varchar(10)
)
---------------------------------------------------------------------
--2. insert data
---------------------------------------------------------------------
insert stock(
sto_barcode,
sto_name,
sto_supplier,
sto_price,
sto_unit,
sto_currency_type,
sto_quantity,
sto_create_date,
sto_update_datetime,
sto_status
) values
('1234567890123','boy T-shirt','HM',80.00,1,'RMB',10,'2008-08-20','2017-09-10 10:30:20','approved'),
('1234567890124','girl T-shirt','HM',120.00,1,'RMB',5,'2008-08-21','2015-05-28 15:20:22','approved'),
('1234567890125','Size 55in TV','ChangHong',3500.00,1,'RMB',10,'2008-08-22','2016-10-01 10:30:20','approved'),
('1234567890126','Java book','QingHua',45.00,1,'RMB',3,'2008-08-23','2017-08-25 10:30:20','approved'),
('1234567890127','Size 32in TV','LEV',1000.00,1,'RMB',7,'2008-08-24','2017-10-17 10:30:20','approved')
insert addresses(
add_address1,
add_address2,
add_address3,
add_address4,
add_address5,
add_address6,
add_status
) values
('China','Fujian','Xiamen','Huli','ZhongSan','5','approved'),
('China','Fujian','ZhangZhou','LongWen','HuanHai','6','approved'),
('China','Fujian','LongHai','Port','BingHai','7','approved'),
('China','Fujian','QuanZhou','ZhongSan','Road','8','approved'),
('China','Fujian','FuZhou','YanAn','Road','9','approved')
insert users(
use_add_id,
use_ship_add_id,
use_firstname,
use_surname,
use_gender,
use_dob,
use_username,
use_password,
use_status
) values
(1,2,'David','Smith','Male','1990-10-25','David-1','123456','approved'),
(3,3,'Lucy','Lee','Female','1992-11-23','Lucy-1','123456','approved'),
(4,4,'Nick','Wang','Male','1991-10-26','Nick-1','123456','approved'),
(5,5,'Anna','Liu','Female','1993-11-27','Anna-1','123456','approved')
insert users(
use_add_id,
use_ship_add_id,
use_firstname,
use_surname,
use_gender,
use_dob,
use_username,
use_password,
use_status
) values
(1,1,'Luke','Smith','Male','1992-11-25','Luke-1','123456','approved')
insert orders(
ord_use_id,
ord_sto_id,
ord_quantity,
ord_payment_status,
ord_ordernumber,
ord_status,
ord_create_datetime
) values
(1,5,1,'full','ord0001','approved','2017-11-20 10:20:30'),
(2,2,1,'full','ord0002','approved','2017-11-21 10:20:30'),
(2,4,1,'full','ord0002','approved','2017-11-21 10:20:30'),
(3,1,3,'full','ord0003','approved','2017-11-22 10:20:30'),
(1,4,5,'full','ord0004','approved','2017-11-22 11:20:30'),
(4,2,2,'full','ord0005','approved','2017-11-23 10:20:30'),
(4,5,1,'full','ord0005','approved','2017-11-23 10:20:30')
---------------------------------------------------------------------
--3. create function
---------------------------------------------------------------------
create function getStar(@useId int)
returns int as
begin
declare @star int
set @star=0
declare @totalCost decimal(10,2)
set @totalCost=(select sum(ord_bill) from orders where ord_use_id=@useId)
--rule
--if @totalCost100 and @totalCost500 and @totalCost1000 and @totalCost2000 and @totalCost3000
begin
set @star=5
end
return @star
end
create function findFullnameCost()
returns @fullnameCostTable table(fullName varchar(50), totalCost decimal(10,2)) as
begin
declare @useId int
set @useId=1
declare @maxUseId int
set @maxUseId=(select max(use_id) from users)
declare @fullName varchar(50)
set @fullName='unknown'
declare @totalCost decimal(10,2)
set @totalCost=0
while @useId<=@maxUseId
begin
set @fullName=(select use_firstname+' '+ use_surname from users where use_id=@useId and use_status='approved')
set @totalCost=(select sum(ord_bill) from orders where ord_use_id=@useId and ord_status='approved')
if @totalCost is null
begin
set @totalCost=0
end
if @fullName is not null and @fullName != 'unknown'
begin
--insert data
insert @fullnameCostTable(fullName, totalCost) values(@fullName,@totalCost)
end
set @useId=@useId+1
end
return
end
---------------------------------------------------------------------
--4. update
---------------------------------------------------------------------
update orders set
ord_bill=ord_quantity*(select sto_price from stock where sto_id=ord_sto_id),
ord_shippment_status='sent',
ord_update_datetime=ord_create_datetime
update users set use_star=dbo.getStar(use_id)
---------------------------------------------------------------------
--5. procedure
---------------------------------------------------------------------
create procedure testP1 as
begin
print 'here'
end
execute dbo.testP1
----------------------------------
create procedure testP2(@n int) as
begin
print @n
end
execute dbo.testP2 10
----------------------------------
create procedure testP3(@n int, @m int) as
begin
print @n+@m
end
execute dbo.testP3 10,20
------------------------------------------------------------------
create procedure buyItemP(@useName varchar(20), @barCode varchar(13), @ordNumber varchar(20), @quantity int) as
begin
--1.(userName:@useName) bought a item(barcode: @barCode),ordernumber:@ordNumber, make an order with a full payment
declare @useId int
set @useId=(select use_id from users where use_username=@useName)
declare @stoId int
set @stoId=(select sto_id from stock where sto_barcode=@barCode)
--step.1. insert
insert orders(
ord_use_id,
ord_sto_id,
ord_quantity,
ord_payment_status,
ord_ordernumber,
ord_status
) values
(@useId,@stoId,@quantity,'full',@ordNumber,'approved')
--step.2. update bill
update orders set
ord_bill=(ord_quantity*(select sto_price from stock where sto_id=@stoId)),
ord_update_datetime=GETDATE()
where ord_ordernumber=@ordNumber
--step.3. send out the item, update the shippment status
update orders set
ord_shippment_status='sent',
ord_update_datetime=GETDATE()
where ord_ordernumber=@ordNumber
--step.4. update stock quantity
update stock set
sto_quantity=sto_quantity-@quantity,
sto_update_datetime=GETDATE()
where sto_id=@stoId
--step.5. update stock status, if quantity<=6, set an alert for importing item
update stock set
sto_status='importing',
sto_update_datetime=GETDATE()
where sto_quantity<=6
--step.6. buy 10 items and import
update stock set
sto_quantity=sto_quantity+10,
sto_status='approved',
sto_update_datetime=GETDATE()
where sto_status='importing'
--step.7. star,
update users set use_star=dbo.getStar(use_id) where use_id=@useId
end
---------------------------------------------------------
--example
execute dbo.buyItemP 'Lucy-1', '1234567890124' ,'ord0006',2
--------------------------------------------------------
--view
create view userDetailView as
select use_firstname+' '+use_surname as FullName, use_gender as Gender from users
select * from userDetailView
----------------------------------------------------------------
--index, found slow down, ord_ordernumber='ord0001' and ord_status='approved'
select * from orders where ord_ordernumber='ord0001' and ord_status='approved'
create index orderNumberAndStatusIndex on orders(ord_ordernumber,ord_status)
----------------------------------------------------------
--trigger
create trigger changePasswordTri on users for update as
begin
if UPDATE(use_password)
begin
print 'updated'
end
else
begin
print 'no update'
end
end
create trigger changePasswordInsteadTri on users instead of update as
begin
if UPDATE(use_password)
begin
print 'instead of updated'
end
else
begin
print 'instead of, no update'
end
end
update users set use_password='abc123' where use_id=1
select * from addresses
select * from users
select * from stock
select * from orders