[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 zone (
	id int primary key identity(1,1),
	name nvarchar(50)
)
go

create table game (
	id int primary key identity(1,1),
	name nvarchar(50),
	zone_id int references zone (id),
	price float,
	type tinyint default 0,
	percents float
)
go

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

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

create table ticket (
	id int primary key identity(1,1),
	price float,
	buy_date datetime,
	game_id int references game (id)
)
go

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

-- Insert Data
insert into zone (name)
values
('Z001'),
('Z002'),
('Z003'),
('Z004'),
('Z005')
go

insert into game (zone_id, name, price, type, percents)
values
(1, 'G01', 1000, 0, 0),
(1, 'G02', 5000, 0, 0),
(2, 'G03', 3000, 1, 0.35),
(3, 'G04', 2000, 1, 0.35),
(4, 'G05', 8000, 0, 0),
(5, 'G06', 6000, 0, 0)
go

insert into shop (zone_id, name, company_name)
values
(1, 'S01', 'ABC'),
(2, 'S02', 'ABC'),
(3, 'S03', 'KKK'),
(4, 'S04', 'ABC'),
(5, 'S05', 'KKK')
go

insert into product (shop_id, title, price)
values
(1, 'P01', 2000),
(1, 'P02', 6000),
(2, 'P03', 3000),
(2, 'P04', 8000),
(2, 'P05', 3000),
(3, 'P06', 6000)
go

insert into orders (product_id, price, num, order_date)
values
(1, 2000, 2, '2022-03-26'),
(1, 2000, 3, '2022-03-26'),
(2, 6000, 1, '2022-03-27'),
(3, 3000, 5, '2022-03-27'),
(3, 3000, 2, '2022-03-25')
go

insert into ticket (game_id, price, buy_date)
values
(1, 1000, '2022-003-26'),
(1, 1000, '2022-003-26'),
(1, 1000, '2022-003-26'),
(2, 3000, '2022-003-27'),
(2, 3000, '2022-003-27')
go

insert into ticket (game_id, price, buy_date)
values
(3, 5000, '2022-003-26'),
(4, 6000, '2022-003-26')
go

-- Query
select zone.name 'Zone Name', game.name '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ể
---- Xem tat ca cac ticket dc ban ra
select game.name 'Game Name', ticket.price, game.type, game.percents, ticket.buy_date
from game, ticket
where game.id = ticket.game_id
go

---- Xem tat ca cac ticket dc ban ra trong ngay 2022-03-26
------ Tan dung gia type  = 0: CityKids, 1: Doi Tac -> Neu la gia tri bat ky: rat kho xu ly
select game.name 'Game Name', ticket.price, game.type, game.percents, ticket.price * game.type * (1 - game.percents) '$ Doi Tac', ticket.price * (1 - game.type * (1 - game.percents)) '$ CityKids', ticket.buy_date
from game, ticket
where game.id = ticket.game_id
	and ticket.buy_date = '2022-03-26'
go

---- Tinh doanh thu cua doi tac
select sum(ticket.price * game.type * (1 - game.percents)) '$ Doi Tac', 
	   sum(ticket.price * (1 - game.type * (1 - game.percents))) '$ CityKids'
from game, ticket
where game.id = ticket.game_id
	and ticket.buy_date = '2022-03-26'
go

---- Cach 2 tham khao them
select sum(ticket.price * (1 - game.percents)) 'Tien Thu Cua Doi Tac'
from game, ticket
where game.id = ticket.game_id
	and game.type = 1
	and ticket.buy_date = '2022-03-26'

select sum(T.price) 'Tong Tien Thu Duoc Cua CityKids'
from (
	select sum(ticket.price) price
	from game, ticket
	where game.id = ticket.game_id
		and game.type = 0
		and ticket.buy_date = '2022-03-26'
	union
	select sum(ticket.price * game.percents) price
	from game, ticket
	where game.id = ticket.game_id
		and game.type = 1
		and ticket.buy_date = '2022-03-26'
) T
go

-- Query
select shop.id, shop.name 'Shop Name', product.title 'Product Name', orders.price, orders.num, orders.order_date
from shop, product, orders
where shop.id = product.shop_id
	and product.id = orders.product_id
go

select shop.id, shop.name 'Shop Name', product.title 'Product Name', orders.price, orders.num, orders.price * orders.num 'Tong Tien', orders.order_date
from shop, product, orders
where shop.id = product.shop_id
	and product.id = orders.product_id
go

select shop.id, 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.id, shop.name
go

-- Trigger cho phep xoa thong tin do uong
select * from product
select * from orders

delete from product where id = 6
delete from product where id = 1

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 primary key
	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
select * from ticket

create trigger trigger_no_update_price_ticket on ticket
for update
as
begin
	if update (price)
	begin
		print N'Khong duoc thay doi gia trong bang ticket'
		rollback transaction
	end
end

update ticket set price = 100