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

#Quản lý khách sạn

-- Tao CSDL BT1788
create database BT1788
go

-- Kich hoat CSDL
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),
	hotel_id 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,
	num int,
	price float
)
go

-- insert data
insert into hotel(name, address, area, owner_name)
values
('Hotel 01', 'Ha Noi', 20, 'TRAN VAN A'),
('Hotel 02', 'Ha Nam', 100, 'TRAN VAN B'),
('Hotel 03', 'Nam Dinh', 60, 'TRAN VAN C'),
('Hotel 04', 'Ninh Binh', 120, 'TRAN VAN D'),
('Hotel 05', 'Ha Noi', 20, 'TRAN VAN E')
go

select * from hotel

insert into room (hotel_id, area, floor, type)
values
(1, 20, 1, 'NORMAL'),
(1, 10, 2, 'VIP'),
(1, 50, 2, 'VIP'),
(2, 30, 1, 'NORMAL'),
(2, 60, 2, 'PRO'),
(3, 20, 2, 'DIAMON')
go

insert into book(room_no, checkin, checkout, num, price)
values
(1, '2021-06-12 08:00:00', '2021-06-15 12:00:00', 2, 5000000),
(2, '2021-08-12 08:00:00', '2021-08-13 12:00:00', 2, 2000000),
(3, '2021-07-12 08:00:00', '2021-07-18 12:00:00', 4, 8000000),
(4, '2021-09-12 08:00:00', '2021-09-14 12:00:00', 2, 4000000),
(5, '2022-02-12 08:00:00', '2022-02-15 12:00:00', 2, 5000000)
go

-- 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.area 'Dien Tich', room.floor 'Tang'
from hotel, room
where hotel.id = room.hotel_id
go

---- Chỉ những phòng có diện tích 30 m2 trở lên
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', room.room_no 'Ma Phong', room.type 'Loai Phong', room.area 'Dien Tich', room.floor 'Tang'
from hotel, room
where hotel.id = room.hotel_id
	and room.area >= 30
go

-- Thống kê theo dữ liệu : Tên KS, địa chỉ, số phòng
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

---- Số phòng > 5
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) >= 5
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) 'Phong Max'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
order by 'Phong Max' desc
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) 'Phong Min'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
order by 'Phong Min' desc
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) 'Tong Dien Tich'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
order by 'Tong Dien Tich' desc
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) 'Dien Tich Trung Binh'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
order by 'Dien Tich Trung Binh' desc
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

#Tìm hiểu View | Proc trong SQL

C/JS -> JS

function tenham(param1, param2, param 2, ...) {
	//trien khai viet code

	return tra ve du lieu | khong tra ve
}

create proc ten_proc
	@param1 datatype, --datatype: int, smallint, bigint, nvarchar text, ...
	@param2 datatype,
	...
	@paramN datatype output --datatype: int, smallint, bigint, nvarchar text, ...
as
begin
	//Viet cau truy van: insert, delete, update, select ...
	//Viet 1 -> N cau truy van vao day deu OK

end

---- Tac dung view -> save sql vao trong CSDL
------- Ung dung: su dung lai cho cac lan sau
------- Team work: (Project: BT1788 -> members A,B,C)
------- Trong phat trien du an: C/Java/C#/PHP/...
----------------------- Style code: nhung query: insert, update, delete, select -> code
----------------------------------  code -> goi view -> de su dung
---- View: chua 1 cau select -> hieu nhu table -> tao ra do cau select
---- Su dung no nhu table
select * from view_xem_thong_tin_ks

---- Luu y: create | alter | drop
alter view view_xem_thong_tin_ks
as
select hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
from hotel, room
where hotel.id = room.hotel_id
go

select TenKS, DiaChiKS
from view_xem_thong_tin_ks
where TenKS = 'Hotel 01'
go

drop view view_xem_thong_tin_ks
go

---- 2) Proc | Store -> hieu no nhu function (ham) trong ngon ngu lap trinh
create proc proc_view_hotel
as
begin
	select hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id

	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
end

exec proc_view_hotel

-- Tim hieu proc co tham so dau vao
select hotel.id, hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id

select hotel.id, hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id
		and hotel.id = 1

select hotel.id, hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id
		and hotel.id = 2

select hotel.id, hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id
		and hotel.id = 3

--- Ung dung
create proc proc_find_by_hotel
	@hotelId int
as
begin
	select hotel.id, hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id
		and hotel.id = @hotelId
end

exec proc_find_by_hotel 1
exec proc_find_by_hotel 2
exec proc_find_by_hotel 3

create proc proc_find_by_hotel_2
	@hotelId int,
	@type nvarchar(20)
as
begin
	select hotel.id, hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id
		and hotel.id = @hotelId
		and room.type = @type
end

exec proc_find_by_hotel_2 1, 'VIP'
exec proc_find_by_hotel_2 1, 'NORMAL'
exec proc_find_by_hotel_2 1, 'PRO'

-- THAY NOI DUNG TRONG PROC
alter proc proc_find_by_hotel_2
	@hotelId int,
	@type nvarchar(20)
as
begin
	select hotel.id, hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id
		and room.type = @type
		and hotel.id = @hotelId
end

-- XOA PRO
drop proc proc_find_by_hotel_2
go

---- PROC CO THAM SO TRA VE
alter proc proc_find_by_hotel_2
	@hotelId int,
	@type nvarchar(20),
	@count int output
as
begin
	select hotel.id, hotel.name 'TenKS', hotel.address 'DiaChiKS', room.room_no 'MaPhong', room.type 'LoaiPhong', room.area 'DienTich', room.floor 'Tang'
	from hotel, room
	where hotel.id = room.hotel_id
		and room.type = @type
		and hotel.id = @hotelId

	-- De so ban ghi trong cau lenh tren
	select @count = count(*)
	from hotel, room
	where hotel.id = room.hotel_id
		and room.type = @type
		and hotel.id = @hotelId
end

declare @count2 int
exec proc_find_by_hotel_2 1, 'VIP', @count = @count2 output
print @count2
print N'So ban ghi tim thay: ' + convert(nvarchar, @count2)


declare @count int
exec proc_find_by_hotel_2 1, 'VIP', @count = @count output
print @count
print N'So ban ghi tim thay: ' + convert(nvarchar, @count)