Bài tập - Chương trình quản lý bán hàng - Lập trình SQL Server

-- Tao CSDL
create database BT1801
go

-- Kich hoat CSDL
use BT1801
go

-- Tao tables
create table product (
	id int primary key identity(1,1),
	title nvarchar(250),
	nsx nvarchar(50),
	madein nvarchar(30),
	buy_price float,
	sell_price float,
	created_at date
)
go

create table orders (
	id int primary key identity(1,1),
	product_id int references product (id),
	note nvarchar(500),
	order_date datetime,
	num int,
	price float
)
go

-- Insert Data
insert into product (title, nsx, madein, buy_price, sell_price, created_at)
values
('San pham 1', 'nsx 1', 'VN', 3000, 1000, '2021-06-12'),
('San pham 2', 'nsx 2', 'VN', 5000, 3000, '2021-08-21'),
('San pham 3', 'nsx 3', 'JP', 8000, 5000, '2021-06-12'),
('San pham 4', 'nsx 4', 'VN', 7000, 2000, '2021-09-22'),
('San pham 5', 'nsx 5', 'LAO', 2000, 1000, '2021-06-12')
go

insert into orders(product_id, num, price, order_date)
values
(1, 1, 3000, '2022-01-19 13:00:00'),
(2, 2, 5000, '2022-02-19 13:00:00'),
(3, 2, 6000, '2022-02-19 13:00:00'),
(1, 3, 7000, '2022-01-29 13:00:00'),
(4, 3, 3000, '2022-02-19 13:00:00'),
(2, 1, 8000, '2022-01-26 13:00:00'),
(5, 1, 3000, '2022-03-10 13:00:00')
go

-- View: hien thi thong tin don hang -> ten sp, nsx, madein, num (orders), price (orders), order_date (orders)
create view vw_show_orders
as
select product.title, product.nsx, product.madein, orders.num, orders.price, orders.order_date
from product, orders
where product.id = orders.product_id
go

select * from vw_show_orders
go

-- Proc: hien thi thong tin don hang -> ten sp, nsx, madein, num (orders), price (orders), order_date (orders)
create proc pro_show_orders
	@madein nvarchar(30)
as
begin
	select product.title, product.nsx, product.madein, orders.num, orders.price, orders.order_date
	from product, orders
	where product.id = orders.product_id
		and product.madein = @madein
end

exec pro_show_orders 'VN'
exec pro_show_orders 'LAO'
exec pro_show_orders 'JP'

-- Proc
select product.id, product.title, product.nsx, product.madein, sum(orders.num * orders.price) 'So Tien'
from product, orders
where product.id = orders.product_id
group by product.id, product.title, product.nsx, product.madein
go

create proc pro_cal_money_by_product_id
	@productId int,
	@total int output
as
begin
	select product.id, product.title, product.nsx, product.madein, sum(orders.num * orders.price) 'So Tien'
	from product, orders
	where product.id = orders.product_id
		and product.id = @productId
	group by product.id, product.title, product.nsx, product.madein

	select @total = sum(orders.num * orders.price)
	from product, orders
	where product.id = orders.product_id
		and product.id = @productId
end

declare @total int
exec pro_cal_money_by_product_id 1, @total = @total output
print N'Tong tien ban duoc: ' + convert(nvarchar, @total)

----------------------------------------------------------------------
-- index
select * from product
select * from orders

--- insert | update | delete | select ->98%
--- Select -> column -> dc su dung trong cau tim kiem nhieu nhat -> index len column do -> tang toc tim kiem
--- 1) Tai sao no lai lam tang toc do tim kiem
--- 2) Danh index cho tat ca thi he thong se chay nhanh dung ko? -> KO
		-- Tao table index -> danh index -> lam cham system (insert, update, delete)
		-- update -> column duoc su dung danh index -> tao lai table index -> performence slow
		-- Tim giai danh index -> cho dung column
--- 3) Neu y 2 -> sai -> thi danh index nhu the nao cho hop ly
		-- column: su dung nhieu trong cau tim kiem (select)
		-- Han che su dung update cho column (TenQH, TenDuong -> hiem khi thay -> index)
		-- Ten san pham, updated_at (cap nhat du lieu -> update ban ghi) -> risk

-- create index ten_index on ten_ban(ten_column)
-- index: clustered index (1 bang chi tao 1 clustered index = primary key) & nonclustered index (Nhieu truong nay)
create nonclustered index IX_madein on product (madein)
go

create nonclustered index IX_nsx on product (nsx)
go

drop index IX_nsx on product
go

-- Trigger
insert into product (title, nsx, madein, buy_price, sell_price, created_at)
values
('San pham 6', 'nsx 6', 'VN', -3000, -1000, '2021-06-12')
go

---- Giai phap: check (buy_price > 0) & (sell_price > 0) -> Thiet ke tables
alter table product
add constraint ck_buy_pric check (buy_price > 0)
go

delete from product where id = 6

insert into product (title, nsx, madein, buy_price, sell_price, created_at)
values
('San pham 6', 'nsx 6', 'VN', 3000, -1000, '2021-06-12')
go

select * from product
select * from orders

-- insert: sell_price < 0 -> bao error -> huy lenh insert
-- khong cho thay doi gia tri cua madein
-- trigger = table + hanh dong cua ban do (insert | update | delete)
-- trigger = table + insert -> kich hoat 1 block code -> de kiem tra dieu kien nao do => success | failed
create trigger trigger_check_sell_price on product
for insert
as
begin
	-- block code check o day
	if (select count(*) from inserted where sell_price < 0) > 0
	begin
		print N'Khong dc phep chen sell_price < 0'
		rollback transaction -- Huy ko cho chay cau lenh do thanh cong nua.
	end
end

insert into product (title, nsx, madein, buy_price, sell_price, created_at)
values
('San pham 7', 'nsx 7', 'VN', 3000, -1000, '2021-06-12')
go

update product set sell_price = -5000 where id = 8

alter trigger trigger_check_sell_price on product
for insert
as
begin
	-- block code check o day
	if (select count(*) from inserted where sell_price < 0) > 0
	begin
		print N'~~~Khong dc phep chen sell_price < 0'
		rollback transaction -- Huy ko cho chay cau lenh do thanh cong nua.
	end
end

drop trigger trigger_check_sell_price

-- Yeu cau: khong cho phep thay doi sell_price < 0 (update)
-- trigger update: sell_price < 0
create trigger trigger_check_update on product
for update
as
begin
	if (select count(*) from inserted where sell_price < 0) > 0
	begin
		print N'~~~Khong dc phep chen sell_price < 0'
		rollback transaction -- Huy ko cho chay cau lenh do thanh cong nua.
	end
end

update product set sell_price = -5000 where id = 8

-- Viet trigger kiem tra -> neu madein bi thay doi du lieu -> huy lenh update
create trigger trigger_no_change_madein on product
for update -- hanh dong update xay ra
as
begin
	if update(madein) -- function: Kiem tra xem madein co bi thay doi du lieu khong
	begin
		print N'Khong dc phep thay doi gia tri madein'
		rollback transaction
	end
end

update product set madein = 'US' where madein = 'LAO' --Cau lenh cap nhat du lieu
go

select * from product

delete from product where id = 8

insert into product (title, nsx, madein, buy_price, sell_price, created_at)
values
('San pham 9', 'nsx 9', 'VN', 3000, 10000, '2021-06-12')
go

-- BT: ko cho phep xoa ban ghi id = 11
delete from product where id = 11

create trigger trigger_no_delete_11 on product
for delete
as
begin
	if (select count(*) from deleted where id = 11) > 0
	begin
		print N'Khong dc xoa ban ghi id = 11'
		rollback transaction
	end
end

---- trigger instead of delete
select * from product
select * from orders

delete from product where id = 1
delete from product where id = 12

delete from orders where product_id = 1


delete from product where id = 2

create trigger trigger_instead_of_delete_product on product
instead of delete
as
begin
	-- Xoa foreign key
	delete from orders where product_id in (select id from deleted)

	-- Xoa chi no
	delete from product where id in (select id from deleted)
end

delete from product where id = 2