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

-- Tao CSDL BT2907
create database BT2907
go

-- Kich hoat CSDL
use BT2907
go

-- Tao tables
create table zones (
	id int primary key identity(1,1),
	name nvarchar(200)
)
go

create table gaming (
	id int primary key identity(1,1),
	name nvarchar(200),
	zone_id int references zones (id),
	price float,
	type int default 0,
	percents float
)
go

create table shop (
	id int primary key identity(1,1),
	name nvarchar(200),
	zone_id int references zones (id),
	company_name nvarchar(200)
)
go

create table product (
	id int primary key identity(1,1),
	name nvarchar(250),
	price float,
	shop_id int references shop (id)
)
go

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

create table tickets (
	id int primary key identity(1,1),
	price float,
	order_date datetime,
	game_id int references gaming (id)
)

-- insert data
insert into zones (name)
values
('Z01'),
('Z02'),
('Z03'),
('Z04'),
('Z05')
go

insert into gaming(name, zone_id, type, price, percents)
values
('Game 01', 1, 0, 30000, 0),
('Game 02', 1, 1, 50000, 0.3),
('Game 03', 2, 1, 50000, 0.3),
('Game 04', 3, 0, 30000, 0),
('Game 05', 3, 0, 60000, 0)
go

insert into shop (name, zone_id, company_name)
values
('Shop 01', 1, 'ABC'),
('Shop 02', 2, 'ABC'),
('Shop 03', 2, 'ABC'),
('Shop 04', 3, 'ABC'),
('Shop 05', 4, 'ABC')
go

insert into product (name, shop_id, price)
values
('San pham 1', 1, 10000),
('San pham 2', 1, 10000),
('San pham 3', 1, 20000),
('San pham 4', 2, 10000),
('San pham 5', 2, 20000),
('San pham 6', 3, 30000)
go

insert into tickets(game_id, price, order_date)
values
(1, 200000, '2022-01-21'),
(1, 200000, '2022-02-12'),
(2, 100000, '2022-02-16'),
(2, 100000, '2022-02-21'),
(2, 100000, '2022-03-18'),
(3, 300000, '2022-03-21'),
(4, 250000, '2022-01-21')
go

insert into orders (product_id, price, num, order_date)
values
(1, 10000, 2, '2022-02-16'),
(1, 10000, 1, '2022-02-16'),
(2, 20000, 3, '2022-02-16'),
(3, 20000, 1, '2022-02-16'),
(4, 25000, 1, '2022-02-16')
go

--------------------------------------------------------------
-- Xem thông tin trò chơi: tên zone (zones), tên trò chơi (gaming), giá (gaming), type (gaming), percent (gaming)
select zones.name 'zone name', gaming.name 'game name', gaming.price, gaming.type, gaming.percents
from zones, gaming
where zones.id = gaming.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 * from tickets
---- Trong tickets -> cai nao la cua CityKids, cai cua doi tac
---- gaming name (gaming), type (gaming), percents (gaming), price (tickets), order_date (tickets)
select gaming.name, gaming.type, gaming.percents, tickets.price, tickets.order_date
from gaming, tickets
where gaming.id = tickets.game_id
go

-- type: 0 -> CityKids, 1 -> Doi tac
---- CityKids: price - price * (1 - percents) * type
---- Doi Tac: price * (1 - percents) * type
select gaming.name, gaming.type, gaming.percents, tickets.price, tickets.price - tickets.price * (1 - gaming.percents) * gaming.type 'CityKids Price', tickets.price * (1 - gaming.percents) * gaming.type 'Doi Tac Price'
from gaming, tickets
where gaming.id = tickets.game_id
go

select sum(tickets.price - tickets.price * (1 - gaming.percents) * gaming.type) 'CityKids Price', sum(tickets.price * (1 - gaming.percents) * gaming.type) 'Doi Tac Price'
from gaming, tickets
where gaming.id = tickets.game_id
go

select sum(tickets.price - tickets.price * (1 - gaming.percents) * gaming.type) 'CityKids Price', sum(tickets.price * (1 - gaming.percents) * gaming.type) 'Doi Tac Price'
from gaming, tickets
where gaming.id = tickets.game_id
	and tickets.order_date = '2022-03-21'
go

-- Tính doanh thu đc của từng của hàng dịch vụ
select shop.name 'shop name', product.name 'product name', orders.price, orders.num, orders.price * orders.num 'Tong Tien'
from shop, product, orders
where shop.id = product.shop_id
	and product.id = orders.product_id
go

select shop.name 'shop name', sum(orders.price * orders.num) 'Tong Tien'
from shop, product, orders
where shop.id = product.shop_id
	and product.id = orders.product_id
group by shop.name
go

-- Tạo trigger cho phép xóa thông tin 1 đồ uống
select * from product
select * from orders

delete from product where id = 1

create trigger TG_instead_of_delete_product on product
instead of delete
as
begin
	delete from orders where product_id in (select id from deleted)

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

delete from product where id = 1

-- Tạo trigger không cho sửa thông tin giá vé trong bản ticket
create trigger TG_no_update_price_tickets on tickets
for update
as
begin
	if update (price)
	begin
		print 'Khong dc phep update price trng bang tickets'
		rollback transaction
	end
end

update tickets set price = 1