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

-- Tao database BT1801
create database BT1801
go

-- Kich hoat database BT1801
use BT1801
go

-- Tao tables trong database
create table product (
	id int primary key identity(1,1),
	title nvarchar(250) not null,
	manufacturer nvarchar(50),
	madein nvarchar(50),
	buy_price float,
	sell_price float,
	created_at date
)
go

create table orders (
	id int primary key identity(1,1),
	id_product int references product (id),
	note nvarchar(300),
	order_date date,
	amount int,
	price float
)
go

-- insert data
insert into product(title, manufacturer, madein, buy_price, sell_price, created_at)
values
('San Pham A', 'AB', 'Viet Nam', 1000, 1800, '2022-01-02'),
('San Pham B', 'AB', 'Viet Nam', 1200, 1900, '2022-01-22'),
('San Pham C', 'AB', 'Viet Nam', 1500, 2000, '2022-01-12'),
('San Pham D', 'BC', 'Japan', 3000, 4800, '2022-01-11'),
('San Pham E', 'BC', 'Japan', 3000, 5800, '2022-01-09'),
('San Pham F', 'BC', 'Japan', 5000, 7800, '2022-01-01'),
('San Pham G', 'DD', 'US', 10000, 18000, '2022-01-05'),
('San Pham H', 'EE', 'UK', 9000, 1300, '2022-01-06')
go

insert into orders (id_product, amount, price, order_date, note)
values
(1, 1, 2000, '2022-01-05', 'ABC'),
(1, 3, 1800, '2022-01-02', 'ABC'),
(2, 5, 2000, '2022-01-06', 'ABC'),
(2, 2, 2100, '2022-01-11', 'ABC'),
(3, 7, 1800, '2022-01-22', 'ABC')
go

-- TEST
select * from product
select * from orders

-- 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ế
---- Du lieu can xem: ten san pham, nha sx, xuat xu, so luong, gia ban, ngay ban, ghi chu
select product.title, product.manufacturer, product.madein, orders.amount, orders.price, orders.order_date, orders.note
from orders left join product on orders.id_product = product.id
order by product.title asc

---- Lay 3 ban ghi dau tien trong cau truy van
select top(3) product.title, product.manufacturer, product.madein, orders.amount, orders.price, orders.order_date, orders.note
from orders left join product on orders.id_product = product.id
order by product.title asc
go

---- Lay 3 ban ghi cuoi cung thi lam the nao -> dua ra giai phap -> order by nguoc lai asc -> desc, desc -> asc -> su dung top de lay
select top(3) product.title, product.manufacturer, product.madein, orders.amount, orders.price, orders.order_date, orders.note
from orders left join product on orders.id_product = product.id
order by product.title desc
go

create view view_all_orders
as
select product.title, product.manufacturer, product.madein, orders.amount, orders.price, orders.order_date, orders.note
from orders left join product on orders.id_product = product.id
go

select * from view_all_orders
go

-- 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_order_by_madein
	@madein nvarchar(50)
as
begin
	select product.title, product.manufacturer, product.madein, orders.amount, orders.price, orders.order_date, orders.note
	from orders left join product on orders.id_product = product.id
	where product.madein = @madein
	order by product.title asc
end

exec proc_order_by_madein 'Viet Nam'
go

exec proc_order_by_madein 'Japan'
go

-- 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 già là total
select product.title, product.manufacturer, product.madein, sum(orders.amount) total
from orders left join product on orders.id_product = product.id
group by product.title, product.manufacturer, product.madein
order by product.title asc

create proc proc_sum_orders_by_product
	@productId int,
	@total int output
as
begin
	select product.title, product.manufacturer, product.madein, sum(orders.amount) total
	from orders left join product on orders.id_product = product.id
	where product.id = @productId
	group by product.title, product.manufacturer, product.madein
	order by product.title asc

	select @total = sum(orders.amount)
	from orders left join product on orders.id_product = product.id
	where product.id = @productId
end

declare @total int
exec proc_sum_orders_by_product 1, @total = @total output
print N'Tong so don hang ban duoc = ' + convert(nvarchar, @total)
go

-- View data
select * from product
select * from orders

-- Gia su -> xoa 1 ban ghi trong product voi id = 3
delete from product where id = 3

-- De xoa dc ban ghi tren product (id = 3) -> Yeu cau la nhu sau:
delete from orders where id_product = 3

-- Gia su -> xoa 1 ban ghi trong product voi id = 3
delete from product where id = 3

-- Bai toan: co cach nao -> khi xoa 1 san pham trong product -> se tu dong xoa foreign key -> sau do quay lai xoa san pham di khong
delete from product where id = 2

---- Giai phap -> su dung 1 trigger -> instead of -> code 1 chuc nang: xoa foreign key -> xoa chinh no
create trigger trigger_insteadof_product on product
instead of delete
as
begin
	-- Xoa du lieu lien quan toi foreign key
	-- deleted: danh sach ban ghi se bi xoa trong bang product
	---- (select id from deleted): chua danh sach id se bi xoa (item1)
	---- delete from orders where id_product in (select id from deleted): xoa di cac ban ghi trong orders vs id_product trong (item1)
	delete from orders where id_product in (select id from deleted)

	delete from product where id in (select id from deleted)
end

delete from product where id = 2
go

delete from product where id = 1
go

Vidu: xoa ban ghi trong bang DoiBong

Viet instead of delete
- Xoa du lieu trong bang LichSu
- Xoa du lieu trong bang ChiTietDoiBong
- Qua sang xoa du lieu tren bang DoiBong

Thanh Cong:

delete from DoiBong where id = 1

create trigger trigger_insteadof_doibong on DoiBong
as
begin
	-- Xoa du lieu trong bang A
	delete from A where id_ls in (
		select id from LichSU where id_club_1 in (select id from deleted) or id_club_2 in (select id from deleted)
	)

	-- Xoa du lieu trong bang LichSu
	delete from LichSu where id_club_1 in (select id from deleted) or id_club_2 in (select id from deleted)

	-- Xoa du lieu trong bang ChiTietDoiBong
	delete from ChiTietDoiBong where id_club in (select id from deleted)

	-- Xoa du lieu DoiBong
	delete from DoiBong where id in (select id from deleted)
end