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

-- Tao database
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),
	manufacturer nvarchar(100),
	madein nvarchar(20),
	price float,
	buy_price float,
	created_at date
)
go

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

-- Insert data
insert into product(title, price, buy_price, madein, manufacturer, created_at)
values
('San pham 1', 1000, 500, 'VN', 'M01', '2020-02-18'),
('San pham 2', 2000, 1000, 'JP', 'M02', '2021-02-18'),
('San pham 3', 5000, 2000, 'LAO', 'M03', '2019-02-18'),
('San pham 4', 3500, 1500, 'VN', 'M04', '2022-02-18'),
('San pham 5', 2200, 1500, 'JP', 'M05', '2020-02-18')
go

insert into orders(product_id, num, price, order_date, note)
values
(1, 2, 1000, '2022-03-12 09:00:00', 'okok'),
(1, 1, 1200, '2021-06-12 16:00:00', 'okok'),
(2, 3, 2000, '2020-04-12 17:00:00', 'okok'),
(2, 2, 2000, '2022-03-12 09:00:00', 'okok'),
(3, 1, 5000, '2021-08-12 15:00:00', 'okok'),
(4, 2, 3000, '2022-03-12 13:00:00', 'okok')
go

-- Thực hiện liệt kê tất cả các đơn hàng đã được bán ra -> Dùng view để thiết kế
---- Columns: title (product), madein (product), price, num, order_date, note (orders)
select product.title, product.madein, orders.price, orders.num, orders.order_date, orders.note
from product join orders on product.id = orders.product_id

create view view_order_list
as
select product.title, product.madein, orders.price, orders.num, orders.order_date, orders.note
from product join orders on product.id = orders.product_id
go

select * from view_order_list

-- Liệt kê các đơn hàng được bán ra có xuất xứ -> yêu cầu viết procedure có tham số truyền vào là xuất xứ
create proc proc_find_orders_by_madein
	@madein nvarchar(20)
as
begin
	select product.title, product.madein, orders.price, orders.num, orders.order_date, orders.note
	from product join orders on product.id = orders.product_id
	where product.madein = @madein
end

exec proc_find_orders_by_madein 'VN'
exec proc_find_orders_by_madein 'JP'

-- Thống kê tổng giá bán được cho từng mặt hàng. \
----- -> viết procedure có tham số truyền vào là mặt hàng và tham số đấu ra là total
select product.title, product.madein, sum(orders.price * orders.num) 'Total'
from product join orders on product.id = orders.product_id
group by product.title, product.madein
go

create proc proc_sum_price_for_order
	@productId int
as
begin
	select product.title, product.madein, sum(orders.price * orders.num) 'Total'
	from product join orders on product.id = orders.product_id
	where product.id = @productId
	group by product.title, product.madein
end

exec proc_sum_price_for_order 1
exec proc_sum_price_for_order 2

alter proc proc_sum_price_for_order
	@productId int,
	@total int output
as
begin
	select product.title, product.madein, sum(orders.price * orders.num) 'Total'
	from product join orders on product.id = orders.product_id
	where product.id = @productId
	group by product.title, product.madein

	select @total = sum(orders.price * orders.num)
	from product join orders on product.id = orders.product_id
	where product.id = @productId
	group by product.title, product.madein
end

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

-- Tao database
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),
	manufacturer nvarchar(100),
	madein nvarchar(20),
	price float,
	buy_price float,
	created_at date
)
go

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

-- Insert data
insert into product(title, price, buy_price, madein, manufacturer, created_at)
values
--inserted
('San pham 1', 1000, 500, 'VN', 'M01', '2020-02-18'),
('San pham 2', 2000, 1000, 'JP', 'M02', '2021-02-18'),
('San pham 3', 5000, 2000, 'LAO', 'M03', '2019-02-18'),
('San pham 4', 3500, 1500, 'VN', 'M04', '2022-02-18'),
('San pham 5', 2200, 1500, 'JP', 'M05', '2020-02-18')
go

insert into orders(product_id, num, price, order_date, note)
values
(1, 2, 1000, '2022-03-12 09:00:00', 'okok'),
(1, 1, 1200, '2021-06-12 16:00:00', 'okok'),
(2, 3, 2000, '2020-04-12 17:00:00', 'okok'),
(2, 2, 2000, '2022-03-12 09:00:00', 'okok'),
(3, 1, 5000, '2021-08-12 15:00:00', 'okok'),
(4, 2, 3000, '2022-03-12 13:00:00', 'okok')
go

-- Thực hiện liệt kê tất cả các đơn hàng đã được bán ra -> Dùng view để thiết kế
---- Columns: title (product), madein (product), price, num, order_date, note (orders)
select product.title, product.madein, orders.price, orders.num, orders.order_date, orders.note
from product join orders on product.id = orders.product_id

create view view_order_list
as
select product.title, product.madein, orders.price, orders.num, orders.order_date, orders.note
from product join orders on product.id = orders.product_id
go

select * from view_order_list

-- Liệt kê các đơn hàng được bán ra có xuất xứ -> yêu cầu viết procedure có tham số truyền vào là xuất xứ
create proc proc_find_orders_by_madein
	@madein nvarchar(20)
as
begin
	select product.title, product.madein, orders.price, orders.num, orders.order_date, orders.note
	from product join orders on product.id = orders.product_id
	where product.madein = @madein
end

exec proc_find_orders_by_madein 'VN'
exec proc_find_orders_by_madein 'JP'

-- Thống kê tổng giá bán được cho từng mặt hàng. \
----- -> viết procedure có tham số truyền vào là mặt hàng và tham số đấu ra là total
select product.title, product.madein, sum(orders.price * orders.num) 'Total'
from product join orders on product.id = orders.product_id
group by product.title, product.madein
go

create proc proc_sum_price_for_order
	@productId int
as
begin
	select product.title, product.madein, sum(orders.price * orders.num) 'Total'
	from product join orders on product.id = orders.product_id
	where product.id = @productId
	group by product.title, product.madein
end

exec proc_sum_price_for_order 1
exec proc_sum_price_for_order 2

alter proc proc_sum_price_for_order
	@productId int, -- Tham so dau -> du lieu dc truyen tu ngoai vao
	@total int output -- Tham so chua them output -> sau khi chay proc xong -> gan 1 gia tri vao bien nay (du lieu tra ve)
as
begin
	select product.title, product.madein, sum(orders.price * orders.num) 'Total'
	from product join orders on product.id = orders.product_id
	where product.id = @productId
	group by product.title, product.madein

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

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

--------------- NOI DUNG KIEN THUC MOI ------------------------
----------------- INDEX & TRIGGER -----------------------------
-- 1) INDEX -> records: 1000, 100000, 1 Trieu, 5 trieu -> ...
---- SQL: insert, update, delete, select
select * from product
where title = 'San pham 1'
---- Index: clustered index (primary key) -> moi bang chi co 1 & nonclustered index -> nhieu trong 1 bang
create index index_title on product (title)
go

create clustered index index_title on product (title)
go

create nonclustered index index_title on product (title)
go

-- Trong thuc te: index
----                  column dc su dung nhieu trong cau select
----                  column do it bi thay doi du lieu (update) title:san pham 1 -> san pham 1 new -> san pham 11

drop index index_title on product
go

-- 2) TRIGGER: gan vs 1 bang + 1 hanh dong (insert | update | delete)
---- insert | update | delete -> script (code) -> huy cau lenh do di
insert into product(title, price, buy_price, madein, manufacturer, created_at)
values
--inserted
('San pham 6', -1000, -500, 'VN', 'M06', '2020-02-18')
go

select * from product

delete from product where id = 6
-- Thay doi lai thiet ke tables: check price >= 0 & buy_price >= 0
alter table product
add constraint check_price check (price >= 0)
go

alter table product
add constraint check_buy_price check (buy_price >= 0)
go

alter table product
drop constraint check_price
alter table product
drop constraint check_buy_price

-- Xet 1 so tinh huong sau: data co nhieu -> chua kip sua data do -> Cac du lieu lan sau insert dung
-- Cai trigger: insert -> trigger -> huy lenh di
create trigger trigger_check_price_for_insert on product
for insert
as
begin
	--inserted: Du lieu trong insert into
	if(select count(*) from inserted where price < 0 or buy_price < 0) > 0
	begin
		print N'Khong dc insert price & buy_price < 0'
		rollback transaction -- Huy cau lenh insert vua chay di
	end
end

select * from product

delete from product where id > 4

update product set price = -900 where id = 5

create trigger trigger_check_price_for_update on product
for update
as
begin
	--inserted: Du lieu trong insert into
	if(select count(*) from inserted where price < 0 or buy_price < 0) > 0
	begin
		print N'Khong dc update price & buy_price < 0'
		rollback transaction -- Huy cau lenh insert vua chay di
	end
end

update product set price = -900 where id in (5, 6, 2)

-- trigger update -> TH khong cho thay doi du lieu tren column: madein
update product set madein = 'US' where id > 3

create trigger trigger_no_update_madein on product
for update
as
begin
	if update(madein)
	begin
		print N'Khong dc thay doi noi dung madein'
		rollback transaction
	end
end

-- Ko cho nguoi dung xoa 2 bang ghi id = 10, 20
delete from product where id > 6

alter trigger trigger_check_delete on product
for delete
as
begin
	if(select count(*) from deleted where id in (10,20)) > 0
	begin
		print N'Khong dc xoa 1 trong 2 ban ghi co id = 10, 20'
		rollback transaction
	end
end

insert into product(title, price, buy_price, madein, manufacturer, created_at)
values
--inserted
('San pham 6', 1000, 500, 'VN', 'M06', '2020-02-18')
go

-- Xoa trigger
drop trigger trigger_check_delete

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

delete from product where id = 2 --> chay thanh cong

--B1) Xoa tat ca cac foreign key
delete from orders where product_id = 2
--B2) Xoa du lieu
delete from product where id = 2

delete from product where id = 1 --> chay thanh cong

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

	--B2) Xoa primary key
	delete from product where id in (select id from deleted)
end

delete from product where id = 1 --> chay thanh cong