Bài tập - Viết nghiệp vụ cho quản lý khách sạn - Khóa học SQL Server

-- Tao CSDL
create database BT2785
go

-- Kich hoat CSDL
use BT2785
go

-- Tao Tables
create table Roles (
	id int primary key identity(1,1),
	rolename nvarchar(50)
)
go

create table Users (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	gender nvarchar(20),
	email nvarchar(150),
	phone_number nvarchar(20),
	address nvarchar(200),
	role_id int references Roles (id)
)
go

create table Room (
	id int primary key identity(1,1),
	room_no nvarchar(20) not null,
	type nvarchar(20),
	max_num int,
	price float
)
go

create table Booking (
	id int primary key identity(1,1),
	staff_id int references Users (id),
	customer_id int references Users (id),
	checkin datetime,
	checkout datetime
)
go

create table BookingDetail (
	booking_id int references Booking (id),
	room_id int references Room (id),
	price float,
	unit float,
	primary key (booking_id, room_id)
)
go

create table UserDetail (
	booking_id int references Booking (id),
	room_id int references Room (id),
	customer_id int references Users (id),
	primary key (booking_id, room_id, customer_id)
)

create table Category (
	id int primary key identity(1,1),
	name nvarchar(50)
)
go

create table Product (
	id int primary key identity(1,1),
	category_id int references Category (id),
	title nvarchar(150),
	thumbnail nvarchar(500),
	description ntext,
	price float,
	amount int
)
go

create table Services (
	id int primary key identity(1,1),
	booking_id int references Booking (id),
	customer_id int references Users (id),
	product_id int references Product (id),
	price float,
	amount int,
	buy_date datetime
)
go

-- Insert Data
insert into Category (name)
values
('Cafe'),
('Nuoc Giai Khat')
go

insert into Product (category_id, title, price)
values
(1, 'Cafe Nong', 25000),
(1, 'Cafe Da', 25000),
(1, 'Cafe Sua', 30000),
(2, 'Redbull', 15000),
(2, 'Sting', 10000)
go

insert into Roles(rolename)
values
('Administrator'),
('Staff'),
('User')
go

insert into Users(fullname, phone_number, role_id)
values
('Admin', '222222222', 1),
('TRAN VAN A', '333333333', 3),
('TRAN VAN B', '444444444', 3),
('TRAN VAN C', '555555555', 3),
('TRAN VAN D', '666666666', 3)
go

insert into Room(room_no, type, price, max_num)
values
('R001', 'NORMAL', 1000000, 2),
('R002', 'NORMAL', 1000000, 2),
('R003', 'NORMAL', 1000000, 2),
('R004', 'VIP', 5000000, 4),
('R005', 'DIAMOND', 10000000, 4),
('R006', 'NORMAL', 1000000, 2)
go

insert into Booking (staff_id, customer_id, checkin, checkout)
values
(1, 2, '2021-02-16', '2021-02-22'),
(1, 3, '2021-03-18', '2021-03-22')
go

insert into UserDetail (booking_id, customer_id, room_id)
values
(1, 2, 1),
(1, 3, 1),
(1, 4, 2),
(1, 5, 2),
(2, 3, 3),
(2, 5, 3)
go

insert into BookingDetail (booking_id, room_id, price, unit)
values
(1, 1, 1000000, 6),
(1, 2, 1000000, 6),
(2, 3, 1000000, 4)
go

insert into Services (booking_id, customer_id, product_id, price, amount, buy_date)
values
(1, 2, 1, 25000, 1, '2021-02-16'),
(1, 3, 3, 30000, 2, '2021-02-18'),
(2, 3, 2, 25000, 2, '2021-03-20')
go

-- Query
-- Xem thông tin khách hàng đã tới khách sạn gồm: tên (Users), sđt (Users), ngày checkin (Booking), checkout (Booking), mã phòng (Room) tìm theo customer_id -> Sử dụng proc
create proc proc_find_customer
	@customerId int
as
begin
	select Users.fullname, Users.phone_number, Booking.checkin, Booking.checkout, Room.room_no
	from Users, Booking, Room, UserDetail
	where Users.id = UserDetail.customer_id
		and Booking.id = UserDetail.booking_id
		and Room.id = UserDetail.room_id
		and Users.id = @customerId
end

exec proc_find_customer 2
exec proc_find_customer 3

-- Xem danh sách khách hàng tới khách sạn: tên (Users), sđt (Users), ngày checkin (Booking), checkout (Booking), mã phòng (Room) tìm theo booking_id -> sử dụng proc
create proc proc_find_booking
	@bookingId int
as
begin
	select Users.fullname, Users.phone_number, Booking.checkin, Booking.checkout, Room.room_no
	from Users, Booking, Room, UserDetail
	where Users.id = UserDetail.customer_id
		and Booking.id = UserDetail.booking_id
		and Room.id = UserDetail.room_id
		and Booking.id = @bookingId
end

exec proc_find_booking 1
exec proc_find_booking 2

-- Tính tổng tiền sử dụng đặt phòng theo booking_id -> Proc
---- booking_id, staff_name, customer_name, checkin, checkout, total
create proc proc_calculate_money
	@bookingId int
as
begin
	select Booking.id 'booking_id', Booking.checkin, Booking.checkout, sum(BookingDetail.price * BookingDetail.unit) Total
	from Booking, BookingDetail
	where Booking.id = BookingDetail.booking_id
		and Booking.id = @bookingId
	group by Booking.id, Booking.checkin, Booking.checkout
end

exec proc_calculate_money 1
exec proc_calculate_money 2

alter proc proc_calculate_money
	@bookingId int
as
begin
	select Booking.id 'booking_id', Staff.fullname 'Staff Name', Customer.fullname 'Customer Name', Booking.checkin, Booking.checkout, sum(BookingDetail.price * BookingDetail.unit) Total
	from Booking, BookingDetail, Users Staff, Users Customer
	where Booking.id = BookingDetail.booking_id
		and Booking.staff_id = Staff.id
		and Booking.customer_id = Customer.id
		and Booking.id = @bookingId
	group by Booking.id, Booking.checkin, Booking.checkout, Staff.fullname, Customer.fullname
end

exec proc_calculate_money 1
exec proc_calculate_money 2

-- Tính tổng tiền sử dụng dịch vụ theo booking_id
---- booking_id, total
create proc proc_calculate_money_services
	@bookingId int
as
begin
	select Booking.id 'booking_id', sum(Services.price * Services.amount) Total
	from Booking, Services
	where Booking.id = Services.booking_id
		and Booking.id = @bookingId
	group by Booking.id
end

exec proc_calculate_money_services 1
exec proc_calculate_money_services 2

-- Tinh tong tien dich vu BookingDetail + Services
alter proc proc_calculate_money_booking
	@bookingId int,
	@bookingMoney int output,
	@servicesMoney int output
as
begin
	select @bookingMoney = sum(BookingDetail.price * BookingDetail.unit)
	from Booking, BookingDetail
	where Booking.id = BookingDetail.booking_id
		and Booking.id = @bookingId

	select @servicesMoney = sum(Services.price * Services.amount)
	from Booking, Services
	where Booking.id = Services.booking_id
		and Booking.id = @bookingId
end

declare @bookingMoney int
declare @servicesMoney int
declare @total int
exec proc_calculate_money_booking 1, @bookingMoney = @bookingMoney output, @servicesMoney = @servicesMoney output
set @total = @bookingMoney + @servicesMoney
print @total

declare @bookingMoney int
declare @servicesMoney int
declare @total int
exec proc_calculate_money_booking 2, @bookingMoney = @bookingMoney output, @servicesMoney = @servicesMoney output
set @total = @bookingMoney + @servicesMoney
print @total

-- Tính tổng chi phí sử dụng của từng khác hàng: thông tin hiển thị gồm tên, sđt, tổng tiền
select Users.fullname, Users.phone_number, sum(Services.price * Services.amount) Total
from Users, Services
where Users.id = Services.customer_id
group by Users.fullname, Users.phone_number

select * from Services