Quản lý sản phẩm - Lập trình SQL Server

-- Tao database BT1809
create database BT1809
go

-- Kich hoat database
use BT1809
go

-- Tao tables
create table tblUser (
	UserID int not null,
	UserName nvarchar(50)
)
go

create table tblProduct (
	ProductID int not null,
	ProductName nvarchar(50),
	Quanlity int,
	Price money,
	Description ntext
)
go

create table tblOrder (
	OrderID int not null,
	UserID int not null,
	OrderDate datetime
)
go

create table tblOrderDetail (
	OrderID int not null,
	ProductID int not null,
	Quanlity int,
	Price money
)
go

-- clustered index
create clustered index CI_tblUser_UserID on tblUser (UserID)
go

drop index CI_tblUser_UserID on tblUser
go

alter table tblUser
add BirthDate datetime
go

-- constraint
alter table tblOrder
add constraint DF_tblOrder_OrderDate default getdate() for OrderDate
go

-- primary key
alter table tblUser
add constraint PK_tblUser primary key (UserID)
go

alter table tblOrder
add constraint PK_tblOrder primary key (OrderID)
go

alter table tblProduct
add constraint PK_tblProduct primary key (ProductID)
go

alter table tblOrderDetail
add constraint PK_tblOrderDetail primary key (OrderID, ProductID)
go

-- foreign key
alter table tblOrder
add constraint FK_tblOrder_tblUser foreign key (UserID) references tblUser (UserID)
go

alter table tblOrderDetail
add constraint FK_tblOrderDetail_tblOrder foreign key (OrderID) references tblOrder (OrderID)
go

alter table tblOrderDetail
add constraint FK_tblOrderDetail_tblProduct foreign key (ProductID) references tblProduct (ProductID)
go

-- Check
alter table tblOrder
add constraint CK_tblOrder_OrderDate check (OrderDate between '2000-01-01' and getdate())
go

-- Unique
alter table tblUser
add constraint UN_tblUser_UserName unique(UserName)
go

-- Inserting Data
insert into tblUser (UserID, UserName, BirthDate)
values
(1, 'A', '1996-08-28'),
(2, 'B', '1998-06-18'),
(3, 'C', '1997-05-25'),
(4, 'D', '1984-04-27'),
(5, 'E', '1955-08-15'),
(6, 'F', '1955-07-28')
go

insert into tblOrder (OrderID, UserID, OrderDate)
values
(1, 2, '2002-12-01'),
(2, 3, '2000-03-02'),
(3, 2, '2004-08-03'),
(4, 1, '2001-05-12'),
(5, 4, '2002-10-04'),
(6, 6, '2002-03-08'),
(7, 5, '2002-05-02')
go

insert into tblProduct (ProductID, ProductName, Quanlity, Price, Description)
values
(1, 'PA', 2, 10, 'AA'),
(2, 'PB', 10, 20, 'AB'),
(3, 'PC', 13, 300, 'AC'),
(4, 'PD', 7, 80, 'AD'),
(5, 'PE', 12, 120, 'AE')
go

insert into tblOrderDetail(OrderID, ProductID, Quanlity, Price)
values
(1, 1, 10, 10),
(1, 2, 4, 20),
(2, 3, 5, 50),
(3, 3, 6, 80),
(4, 2, 21, 120),
(5, 2, 122, 300)
go

-- Query operation
select * from tblProduct

update tblProduct set Price = 0.9 * Price where ProductID = 3
go

select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quanlity, tblOrderDetail.Price, tblProduct.ProductName
from tblUser, tblOrder, tblOrderDetail, tblProduct
where tblUser.UserID = tblOrder.UserID
	and tblOrder.OrderID = tblOrderDetail.OrderID
	and tblOrderDetail.ProductID = tblProduct.ProductID
go

-- View
select tblProduct.ProductID, tblProduct.ProductName, tblOrderDetail.Price, sum(tblOrderDetail.Quanlity) TotalQuanlity
from tblProduct, tblOrderDetail
where tblProduct.ProductID = tblOrderDetail.ProductID
group by tblProduct.ProductID, tblProduct.ProductName, tblOrderDetail.Price
order by TotalQuanlity desc
go

select tblProduct.ProductID, tblProduct.ProductName, sum(tblOrderDetail.Quanlity) TotalQuanlity
from tblProduct, tblOrderDetail
where tblProduct.ProductID = tblOrderDetail.ProductID
group by tblProduct.ProductID, tblProduct.ProductName
order by TotalQuanlity desc
go

create view view_Top2Product
as
select top(2) tblProduct.ProductID, tblProduct.ProductName, sum(tblOrderDetail.Quanlity) TotalQuanlity
from tblProduct, tblOrderDetail
where tblProduct.ProductID = tblOrderDetail.ProductID
group by tblProduct.ProductID, tblProduct.ProductName
order by TotalQuanlity desc
go

select * from view_Top2Product

-- Produre
create proc sp_TimSanPham
	@GiaMua money,
	@count int output
as
begin
	select tblProduct.ProductID, tblProduct.ProductName, tblProduct.Quanlity, tblProduct.Price, tblProduct.Description
	from tblProduct
	where tblProduct.Price <= @GiaMua

	select @count = count(*) from tblProduct
	where tblProduct.Price <= @GiaMua
end

declare @count int
exec sp_TimSanPham 50, @count = @count output
print N'Tim thay ' + convert(nvarchar, @count) + ' san pham'

-- Trigger
create trigger TG_tblProduct_Update on tblProduct
for update
as
begin
	if (select count(*) from inserted where Price < 10) > 0
	begin
		print N'You don"t price less than 10'
		rollback transaction
	end
end

update tblProduct set Price = 9

update tblProduct set Price = 2 where ProductID = 2

-- Trigger
create trigger TG_tblUser_Update on tblUser
for update
as
begin
	if update(UserName)
	begin
		print N'You don"t update this column UserName'
		rollback transaction
	end
end

select * from tblUser

update tblUser set BirthDate = '1999-01-02' where UserID = 1

update tblUser set UserName = 'AA' where UserID = 1