[Examination] Thiết kế CSDL khu vui chơi CityKids

-- Tao CSDL
create database CityKids
go

-- Kich hoat CSDL
use CityKids
go

-- Tao tables
create table Zone (
	id int primary key identity(1,1),
	zone_name nvarchar(50)
)
go

create table Game (
	id int primary key identity(1,1),
	game_name nvarchar(50),
	zone_id int references Zone (id),
	price float,
	type tinyint default 0,
	percents float default 0
)
go

create table Stores (
	id int primary key identity(1,1),
	name nvarchar(50),
	zone_id int references Zone (id),
	company_name nvarchar(50)
)
go

create table Products (
	id int primary key identity(1,1),
	title nvarchar(150),
	price float,
	store_id int references Stores (id)
)
go

create table Tickets (
	id int primary key identity(1,1),
	price float,
	buy_date date,
	game_id int references Game (id)
)
go

create table Orders (
	id int primary key identity(1,1),
	product_id int references Products (id),
	buy_date date,
	price float,
	num int default 0
)
go

-- Insert data
insert into Zone (zone_name)
values
('Zone A'),
('Zone B'),
('Zone C')
go

insert into Game (game_name, zone_id, type, price, percents)
values
('Game 1', 1, 0, 200000, 0),
('Game 2', 1, 1, 200000, 0.3),
('Game 3', 1, 1, 300000, 0.4),
('Game 4', 2, 0, 200000, 0),
('Game 5', 2, 0, 500000, 0)
go

insert into Stores (zone_id, name, company_name)
values
(1, 'Store 1', 'ABC'),
(1, 'Store 2', 'ABC'),
(2, 'Store 3', 'BBB'),
(2, 'Store 4', 'ABC'),
(3, 'Store 5', 'CCC')
go

insert into Products (title, store_id, price)
values
('San pham 1', 1, 200000),
('San pham 2', 1, 100000),
('San pham 3', 1, 300000),
('San pham 4', 2, 500000),
('San pham 5', 2, 600000)
go

insert into Tickets (game_id, buy_date, price)
values
(null, '2022-12-22', 300000),
(null, '2022-02-22', 300000),
(1, '2023-02-22', 100000),
(1, '2023-03-22', 50000),
(2, '2023-04-22', 500000)
go

insert into Orders (product_id, price, num, buy_date)
values
(1, 100000, 2, '2022-02-12'),
(1, 100000, 1, '2022-03-12'),
(2, 300000, 5, '2022-04-12'),
(2, 300000, 6, '2022-05-12'),
(2, 300000, 5, '2022-06-12'),
(3, 600000, 10, '2022-06-16')
go

-- Query
---- Xem thông tin trờ chơi: tên zone (Zone), tên trờ chơi (Game), giá (Game), type (Game), percent (Game)
select Zone.zone_name, Game.game_name, Game.price, Game.type, Game.percents
from Zone, Game
where Zone.id = Game.zone_id
go

---- Tinh tiền thu được theo từng trờ chơi theo ngày cụ thể, tiền thu được của đối tác và của CityKids
select Zone.zone_name, Game.game_name, sum(Tickets.price)
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
where Tickets.buy_date = '2022-02-22'
group by Zone.zone_name, Game.game_name
go

select Zone.zone_name, Game.game_name, Game.type, Game.percents, sum(Tickets.price) 'Tong Tien'
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
group by Zone.zone_name, Game.game_name, Game.type, Game.percents
go

select * from Tickets
go

-- Tinh tong tien cua doi tac
---- Cach 1
select sum(Tickets.price * (1 - Game.percents)) 'Tong Tien Doi Tac'
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
where Game.type = 1
go

---- Cach 2
select sum(Tickets.price * Game.type * (1 - Game.percents)) 'Tong Tien Doi Tac'
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
where Game.type = 1
go

---- Cach 3
select sum(Tickets.price * Game.type * (1 - Game.percents)) 'Tong Tien Doi Tac'
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
go

-- Cach 1
select sum(A.CityKids) 'Tong Tien CityKids'
from
(
select sum(Tickets.price * (1 - Game.type) + Tickets.price * Game.type * Game.percents) 'CityKids'
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
where Tickets.game_id is not null
union
select sum(Tickets.price) 'CityKids'
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
where Tickets.game_id is null
) as A
go

-- Cach 2
select sum(Tickets.price - Tickets.price * ISNULL(Game.type, 0) * (1 - ISNULL(Game.percents, 0))) 'Tong Tien CityKids'
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
go

-- Ket qua chung
select sum(Tickets.price) 'Tong Doanh Thu',
	sum(Tickets.price - Tickets.price * ISNULL(Game.type, 0) * (1 - ISNULL(Game.percents, 0))) 'Tong Tien CityKids',
	sum(Tickets.price * Game.type * (1 - Game.percents)) 'Tong Tien Doi Tac'
from Tickets left join Game on Tickets.game_id = Game.id
	left join Zone on Zone.id = Game.zone_id
go

-- Tính doanh thu đc của từng của hàng dịch vụ
select Stores.name 'Store Name', sum(Orders.num * Orders.price) 'Doanh Thu'
from Stores, Orders, Products
where Stores.id = Products.store_id
	and Orders.product_id = Products.id
group by Stores.name
go
-----------------------------------------------------------------------------------------------
select * from Orders

-- Khi thuc hien 1 trong cac lenh: insert, update, delete tren 1 bang.
---- Nghiep vu bai: Cai trigger -> insert -> Kiem tra du lieu num < 0 hoac num > 20 -> Lenh insert sai => ko cho add vao
insert into Orders (product_id, buy_date, price, num)
values
(1, '2022-03-12', 200000, -10)
go

insert into Orders (product_id, buy_date, price, num)
values
(1, '2022-03-12', 200000, 12)
go

create trigger trigger_insert_orders_check_num on Orders
for insert
as
begin
	-- Khoi code kiem tra
	if (select count(*) from inserted where num <= 0 or num > 20) > 0
	begin
		print N'Yeu cau num > 0 & num <= 20'
		rollback transaction
	end
end

update Orders set num = -12
where id = 9
go

create trigger trigger_update_orders_check_num on Orders
for update
as
begin
	-- Khoi code kiem tra
	if (select count(*) from inserted where num <= 0 or num > 20) > 0
	begin
		print N'Yeu cau num > 0 & num <= 20'
		rollback transaction
	end
end

drop trigger trigger_update_orders_check_num

create trigger trigger_no_update_product on Orders
for update
as
begin
	-- rollback transaction
	if update(product_id)
	begin
		rollback transaction
	end
end

update Orders set num = 11
where id = 6

update Orders set product_id = 1
where id = 6