Quản lý khách sạn - nâng cao - Lập trình Sql Server

-- 1) Tạo CSDL dữ liệu và các bảng trên
-- Tao database
create database BT1788
go

-- Kich hoat database
use BT1788
go

-- Tao tables
create table hotel (
	id int primary key identity(1,1),
	name nvarchar(50),
	address nvarchar(200),
	area float,
	owner_name nvarchar(50)
)
go

create table room (
	room_no int primary key identity(1,1),
	id_hotel int references hotel(id),
	area float,
	type nvarchar(20),
	floor int
)
go

create table book (
	id int primary key identity(1,1),
	room_no int references room(room_no),
	checkin datetime,
	checkout datetime,
	number int
)
go

-- Them du lieu
insert into hotel(name, address, area, owner_name)
values
('A', 'Ha Noi', 100, 'Tran Van A'),
('B', 'Ha Noi', 200, 'Tran Van B')
go

insert into room(id_hotel, floor, area, type)
values
(1, 1, 50, 'VIP'),
(1, 1, 100, 'VIP'),
(1, 2, 150, 'VIP'),
(1, 2, 250, 'VIP'),
(1, 3, 100, 'VIP'),
(2, 1, 50, 'VIP'),
(2, 2, 150, 'VIP'),
(2, 2, 350, 'VIP')
go

select * from hotel
select * from room
select * from book

insert into book(room_no, number, checkin, checkout)
values
(1, 2, '2022-01-02 08:00:00', '2022-01-04 12:00:00'),
(2, 5, '2022-01-02 12:00:00', '2022-01-03 12:00:00'),
(8, 5, '2022-01-02 15:00:00', '2022-01-03 12:00:00')
go

-- 2) Hiển thị thông  tin khách sạn gồm các trường : Tên KS, địa chỉ, mã phòng, loại phòng, tầng
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
from hotel left join room on hotel.id = room.id_hotel
go

select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.floor 'Tang', room.area 'Dien Tich'
from hotel left join room on hotel.id = room.id_hotel
where room.area >= 100
go

-- 3) Thống kê theo dữ liệu : Tên KS, địa chỉ, số phòng
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', count(room.room_no) 'Tong So Phong'
from hotel left join room on hotel.id = room.id_hotel
group by hotel.name, hotel.address
go

select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', count(room.room_no) 'Tong So Phong'
from hotel left join room on hotel.id = room.id_hotel
group by hotel.name, hotel.address
having count(room.room_no) >= 5
go

-- Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích phòng lớn nhất
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', max(room.area) 'Dien Tich Phong Max'
from hotel left join room on hotel.id = room.id_hotel
group by hotel.name, hotel.address
go

-- Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích phòng nhỏ nhất
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', min(room.area) 'Dien Tich Phong Min'
from hotel left join room on hotel.id = room.id_hotel
group by hotel.name, hotel.address
go

-- Thông kê theo dữ liệu : Tên KS, địa chỉ, tổng diện tích của tất cả các phòng
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', sum(room.area) 'Tong Dien Tich'
from hotel left join room on hotel.id = room.id_hotel
group by hotel.name, hotel.address
go

-- Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích trung bình của từng phòng
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', avg(room.area) 'Dien Tich Trung Binh'
from hotel left join room on hotel.id = room.id_hotel
group by hotel.name, hotel.address
go

-- Thông kê theo dữ liệu : Tên KS, địa chỉ, khách sạn ko có phòng nào.
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', count(room.room_no) 'Tong So Phong'
from hotel left join room on hotel.id = room.id_hotel
group by hotel.name, hotel.address
having count(room.room_no) = 0
go

-- Nang cao: Thong ke doanh thu
---- Bo sung column total_money trong bang book
alter table book
add total_money int default 0
go

select * from book

update book set total_money = 5000000 where id = 1
update book set total_money = 3000000 where id = 2
update book set total_money = 2000000 where id = 3

---- Chuc nang moi:
------ Tinh tong doanh thu cua tung KS
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', sum(book.total_money) 'Tong Doanh Thu'
from hotel left join room on hotel.id = room.id_hotel
	left join book on room.room_no = book.room_no
group by hotel.name, hotel.address
go

------ Tinh tong doanh thu cua tung KS theo 1 thang
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', sum(book.total_money) 'Tong Doanh Thu'
from hotel left join room on hotel.id = room.id_hotel
	left join book on room.room_no = book.room_no
where book.checkout >= '2021-12-01' and book.checkout <= '2022-01-01'
group by hotel.name, hotel.address
go

------ Tim phong trong theo 1 ngay checkin va checkout duoc nhap vao
-- checkin: 2022-01-03 08:00:00, checkout: 2022-01-09 12:00:00
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no, room.floor
from hotel left join room on hotel.id = room.id_hotel
	left join book on room.room_no = book.room_no
where room.room_no not in 
	(
		select book.room_no from book
		where book.room_no not in
		(
			select book.room_no from book
			where book.checkin >= '2022-01-09 12:00:00' or book.checkout <= '2022-01-03 08:00:00'
		)
	)
go

-- Tim phong co the dat trong book
select book.room_no from book
where book.checkin >= '2022-01-06 12:00:00' or book.checkout <= '2022-01-04 08:00:00'

-- Tim nhung phong ko de book trong bang book
-- C1:
select book.room_no from book
where (book.checkin <= '2022-01-06 12:00:00' and '2022-01-06 12:00:00' <= book.checkout)
	or (book.checkin <= '2022-01-04 08:00:00' and '2022-01-04 08:00:00' <= book.checkout)
	or (book.checkin >= '2022-01-06 12:00:00' and book.checkin <= '2022-01-04 08:00:00')
	or (book.checkout >= '2022-01-06 12:00:00' and book.checkout <= '2022-01-04 08:00:00')

-- C2:
select book.room_no from book
where book.room_no not in (select book.room_no from book
where book.checkin >= '2022-01-06 12:00:00' or book.checkout <= '2022-01-04 08:00:00')


select * from book