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

-- Tao CSDL
create database BT1788_New
go

-- Kich hoat CSDL
use BT1788_New
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),
	hotel_id int references hotel (id),
	area float,
	type nvarchar(12),
	floor int
)
go

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

-- Insert Data
insert into hotel (name, area, address, owner_name)
values
('KS A', 100, 'Ha Noi', 'Tran Van A'),
('KS B', 200, 'Ha Nam', 'Tran Van B'),
('KS C', 150, 'Ninh Binh', 'Tran Van C'),
('KS D', 200, 'Nam Dinh', 'Tran Van D'),
('KS E', 200, 'Ha Noi', 'Tran Van E')
go

insert into room (hotel_id, floor, type, area)
values
(1, 1, 'VIP', 10),
(1, 1, 'VIP', 30),
(2, 2, 'Diamond', 40),
(2, 2, 'Diamond', 50),
(3, 2, 'VIP', 100)
go

insert into book (room_no, price, num, checkin, checkout)
values
(1, 1000, 2, '2022-03-22 08:00:00', '2022-03-25 12:00:00'),
(1, 1000, 2, '2022-03-26 08:00:00', '2022-03-28 12:00:00'),
(2, 2000, 4, '2022-03-22 08:00:00', '2022-03-24 12:00:00'),
(2, 2000, 4, '2022-03-28 08:00:00', '2022-03-30 12:00:00'),
(3, 3000, 5, '2022-03-22 08:00:00', '2022-03-25 12:00:00')
go

-- Hiển thị thông  tin khách sạn gồm các trường : Tên KS (hotel), địa chỉ (hotel), mã phòng (room), loại phòng (room), tầng (room)
select hotel.name, hotel.address, room.room_no, room.type, room.floor, room.area
from hotel inner join room on hotel.id = room.hotel_id
go

-- Chỉ những phòng có diện tích 30 m2 trở lên
select hotel.name, hotel.address, room.room_no, room.type, room.floor, room.area
from hotel inner join room on hotel.id = room.hotel_id
	and room.area > 30
go

select hotel.name, hotel.address, room.room_no, room.type, room.floor, room.area
from hotel inner join room on hotel.id = room.hotel_id
where room.area > 30
go

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

select hotel.name, hotel.address, count(room.room_no) 'So Phong'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
order by 'So Phong' desc
go

-- Hien thi khach san co so phong >= 2
select hotel.name, hotel.address, count(room.room_no) 'So Phong'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
having count(room.room_no) >= 2
order by 'So Phong' desc
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, hotel.address, max(room.area) 'Max Area'
from hotel join room on hotel.id = room.hotel_id
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, hotel.address, min(room.area) 'Min Area'
from hotel join room on hotel.id = room.hotel_id
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, hotel.address, sum(room.area) 'Sum Area'
from hotel join room on hotel.id = room.hotel_id
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, hotel.address, avg(room.area) 'Avg Area'
from hotel join room on hotel.id = room.hotel_id
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, hotel.address, count(room.room_no) 'So Phong'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
having count(room.room_no) = 0
go

-- Tao CSDL
create database BT1788_New
go

-- Kich hoat CSDL
use BT1788_New
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),
	hotel_id int references hotel (id),
	area float,
	type nvarchar(12),
	floor int
)
go

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

-- Insert Data
insert into hotel (name, area, address, owner_name)
values
('KS A', 100, 'Ha Noi', 'Tran Van A'),
('KS B', 200, 'Ha Nam', 'Tran Van B'),
('KS C', 150, 'Ninh Binh', 'Tran Van C'),
('KS D', 200, 'Nam Dinh', 'Tran Van D'),
('KS E', 200, 'Ha Noi', 'Tran Van E')
go

insert into room (hotel_id, floor, type, area)
values
(1, 1, 'VIP', 10),
(1, 1, 'VIP', 30),
(2, 2, 'Diamond', 40),
(2, 2, 'Diamond', 50),
(3, 2, 'VIP', 100)
go

insert into book (room_no, price, num, checkin, checkout)
values
(1, 1000, 2, '2022-03-22 08:00:00', '2022-03-25 12:00:00'),
(1, 1000, 2, '2022-03-26 08:00:00', '2022-03-28 12:00:00'),
(2, 2000, 4, '2022-03-22 08:00:00', '2022-03-24 12:00:00'),
(2, 2000, 4, '2022-03-28 08:00:00', '2022-03-30 12:00:00'),
(3, 3000, 5, '2022-03-22 08:00:00', '2022-03-25 12:00:00')
go

-- Hiển thị thông  tin khách sạn gồm các trường : Tên KS (hotel), địa chỉ (hotel), mã phòng (room), loại phòng (room), tầng (room)
select hotel.name, hotel.address, room.room_no, room.type, room.floor, room.area
from hotel inner join room on hotel.id = room.hotel_id
go

-- Chỉ những phòng có diện tích 30 m2 trở lên
select hotel.name, hotel.address, room.room_no, room.type, room.floor, room.area
from hotel inner join room on hotel.id = room.hotel_id
	and room.area > 30
go

select hotel.name, hotel.address, room.room_no, room.type, room.floor, room.area
from hotel inner join room on hotel.id = room.hotel_id
where room.area > 30
go

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

select hotel.name, hotel.address, count(room.room_no) 'So Phong'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
order by 'So Phong' desc
go

-- Hien thi khach san co so phong >= 2
select hotel.name, hotel.address, count(room.room_no) 'So Phong'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
having count(room.room_no) >= 2
order by 'So Phong' desc
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, hotel.address, max(room.area) 'Max Area'
from hotel join room on hotel.id = room.hotel_id
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, hotel.address, min(room.area) 'Min Area'
from hotel join room on hotel.id = room.hotel_id
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, hotel.address, sum(room.area) 'Sum Area'
from hotel join room on hotel.id = room.hotel_id
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, hotel.address, avg(room.area) 'Avg Area'
from hotel join room on hotel.id = room.hotel_id
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, hotel.address, count(room.room_no) 'So Phong'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
having count(room.room_no) = 0
go


---- NOI DUNG KIEN THUC
---- Proc du lieu dau ra & index
---- Tao proc co tham so dau vao -> Hien thi ket qua so phong cua 1 khach san -> @hotelId
create proc proc_count_room_by_hotel_id
	@hotelId int
as
begin
	select hotel.id, hotel.name, hotel.address, count(room.room_no) 'So Phong'
	from hotel left join room on hotel.id = room.hotel_id
	where hotel.id = @hotelId
	group by hotel.id, hotel.name, hotel.address
end

exec proc_count_room_by_hotel_id 1
exec proc_count_room_by_hotel_id 2
exec proc_count_room_by_hotel_id 3
exec proc_count_room_by_hotel_id 4
exec proc_count_room_by_hotel_id 5

create proc proc_count_room_by_hotel_id2
	@hotelId int,
	@count int output
as
begin
	select hotel.id, hotel.name, hotel.address, count(room.room_no) 'So Phong'
	from hotel left join room on hotel.id = room.hotel_id
	where hotel.id = @hotelId
	group by hotel.id, hotel.name, hotel.address

	-- Select chi tra ve 1 record
	-- Trong select -> chi chua phep gan
	select @count = count(room.room_no)
	from hotel left join room on hotel.id = room.hotel_id
	where hotel.id = @hotelId
end

declare @a int
exec proc_count_room_by_hotel_id2 1, @count = @a output
print @a

declare @a int
exec proc_count_room_by_hotel_id2 1, @count = @a output
print N'So phong cua khach san 1 la ' + convert(nvarchar, @a)
go


declare @count int
exec proc_count_room_by_hotel_id2 1, @count = @count output
print N'So phong cua khach san 1 la ' + convert(nvarchar, @count)
go


declare @hotelId int
set @hotelId = 1
-- select @hotelId = 1
declare @count int
exec proc_count_room_by_hotel_id2 @hotelId, @count = @count output
print N'So phong cua khach san ' + convert(nvarchar, @hotelId) + ' la ' + convert(nvarchar, @count)
go

------------------------------------------------------------------------
select * from hotel
select * from room

-- records: 1000 records, 100K records, 500K records, 1 trieu ban ghi, 10 trieu, 100 trieu
---- index, partition, pivot -> tang toc cau truy van select
---- create
---- insert, update, delete, select
	-- Chon ra column -> dc su dung trong cau select nhieu nhat -> index (where)
	-- column -> dc chon danh index

select * from hotel where name = 'KH D'
	-- Ky thuat chon column danh index
		-- Duoc su dung trong select nhieu
		-- Column han che duoc su dung trong cau update table set ...

-- Index co 2 loai
	-- clustered index -> primary key -> table chi co duy nhat 1
	-- nonclustered index -> co nhieu index nay

create nonclustered index IX_name on hotel (name)
go

-- thay nonclustered index	-> clustered index | index
drop index IX_name on hotel
go
-----------------------------------------------------------------