Sử dụng nội dung kiến thức trong bài sau để tìm hiểu

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

-- 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),
	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,
	num int,
	price float
)
go

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

insert into Room (id_hotel, type, floor, area)
values
(1, 'PRO', 1, 30),
(1, 'VIP', 2, 60),
(1, 'VIP', 2, 60),
(2, 'PRO', 1, 30),
(2, 'NORMAL', 2, 20)
go

insert into book (room_no, num, price, checkin, checkout)
values
(1, 2, 2000000, '2022-01-02 08:00:00', '2022-01-05 12:00:00'),
(2, 4, 16000000, '2022-01-01 08:00:00', '2022-01-15 12:00:00'),
(3, 4, 20000000, '2022-01-02 08:00:00', '2022-01-10 12:00:00')
go

-- Tên KS (hotel), địa chỉ (hotel), mã phòng (Room), loại phòng (Room), tầng (Room)
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 Phong'
from hotel left join Room on hotel.id = Room.id_hotel
order by Room.area asc
go

---- Filter: Room.area > 30
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 Phong'
from hotel left join Room on hotel.id = Room.id_hotel
where Room.area > 30
order by Room.area asc
go

-- Tên KS (hotel), địa chỉ (hotel), số phòng (count -> Room)
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', count(Room.room_no) 'So Phong'
from hotel left join Room on hotel.id = Room.id_hotel
group by hotel.name, hotel.address
go

---- Filter: so phong > 5
select hotel.name 'Ten KS', hotel.address 'Dia Chi KS', count(Room.room_no) '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) 'Phong Dien Tich MAX'
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', min(Room.area) 'Phong Dien Tich MIN'
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', sum(Room.area) 'Tong Dien Tich 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', avg(Room.area) 'Dien Tich Phong Trung Binh'
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) '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

---- NOI DUNG OVERVIEW ----
select * from hotel
select * from Room
select * from book

---- TH du lieu thay doi: KS B -> id: 2 (chi 1 ban ghi bi thay doi) -> address: 285 Doi Can, Ba Dinh, HN
update hotel set address = '285 Doi Can, Ba Dinh, HN' where id = 2
go

-- View | Store (proc, procedure, function)
---- Tao, sua, xoa view | proc

--1) View la gi?
---- Vi du:
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 Phong'
from hotel left join Room on hotel.id = Room.id_hotel
order by Room.area asc
go

---- Y nghia view: chi chua 1 cau truy van select -> Hieu la 1 table duoc sinh ra tam thoi tu truy van
---- Phat trien du an: 2 phong cach code & CSDL
---- Phong cach 1: Toan cau truy van -> de trong code (C, Java, C#, ...)
---- Phong cach 2: Luu toan bo truy van select, insert, ... -> view | proc -> code -> goi thong quan view | proc
create view view_show_all_hotel
as
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 Phong'
from hotel left join Room on hotel.id = Room.id_hotel
go

-- Coi view -> nhu la table -> su dung no nhu table
select * from view_show_all_hotel
go

select * from view_show_all_hotel
where 'Ten KS' = 'KS A'
go

alter view view_show_all_hotel
as
select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
from hotel left join Room on hotel.id = Room.id_hotel
go

select * from view_show_all_hotel
where TenKS like '%A'
go

drop view view_show_all_hotel
go

---- Test order by trong view
create view view_show_all_hotel
as
select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
from hotel left join Room on hotel.id = Room.id_hotel
order by DienTichPhong desc --Khong su dung dc order by
go

create view view_show_all_hotel
as
select top(5) hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
from hotel left join Room on hotel.id = Room.id_hotel
order by DienTichPhong desc --TH nay su dung dc order by -> co top
go

select * from view_show_all_hotel
go

--2) Proc
---- Proc -> nhu la function | method -> trong ngon ngu lap trinh
---- Proc khong tham so dau vao, co tham so dau vao, co du lieu tra ve (int, nvarchar, float, ...)
---- Proc gom nhieu select, insert, update, delete, ...

----2.1) Proc khong tham so dau vao
create proc proc_test
as
begin
	select * from hotel
	select * from Room
	select * from book
end

---- Cach su dung proc
exec proc_test
go

alter proc proc_test
as
begin
	select * from hotel
	select * from Room
	select * from book

	insert into hotel (name, area, owner_name, address)
	values
	('KS PROC', 100, 'PROC', 'Ha Noi')
end

exec proc_test
go

drop proc proc_test
go

---- Viet proc ko tham so da
create proc proc_view
as
begin
	select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
	from hotel left join Room on hotel.id = Room.id_hotel
	order by DienTichPhong desc
end

---- Xem thong tin tren cua KS: hotel_id = 1 | 2 | 3
select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
from hotel left join Room on hotel.id = Room.id_hotel
where hotel.id = 1
order by DienTichPhong desc

select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
from hotel left join Room on hotel.id = Room.id_hotel
where hotel.id = 2
order by DienTichPhong desc

select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
from hotel left join Room on hotel.id = Room.id_hotel
where hotel.id = 3
order by DienTichPhong desc

---- Ung dung proc trong bai toan tren -> tai su dung truy van
---- Proc co tham so dau vao la @hotel_id -> datatype: int
alter proc proc_view
	@hotel_id int
as
begin
	select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
	from hotel left join Room on hotel.id = Room.id_hotel
	where hotel.id = @hotel_id
	order by DienTichPhong desc
end

exec proc_view 1
exec proc_view 2
exec proc_view 3

create proc proc_view_multi_param
	@hotel_id int,
	@dientich_min float
as
begin
	select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
	from hotel left join Room on hotel.id = Room.id_hotel
	where hotel.id = @hotel_id
		and Room.area >= @dientich_min
	order by DienTichPhong desc
end

exec proc_view_multi_param 1, 60

-- Proc co tham so dau vao + du lieu tra ve (int, float, nvarchar)
create proc proc_view3
	@hotel_id int,
	@dientich_min float,
	@count int output
as
begin
	select hotel.name 'TenKS', hotel.address 'DiaChiKS', Room.room_no 'MaPhong', Room.type 'LoaiPhong', Room.floor 'Tang', Room.area 'DienTichPhong'
	from hotel left join Room on hotel.id = Room.id_hotel
	where hotel.id = @hotel_id
		and Room.area >= @dientich_min
	order by DienTichPhong desc
	
	select @count = count(*)
	from hotel left join Room on hotel.id = Room.id_hotel
	where hotel.id = @hotel_id
		and Room.area >= @dientich_min
end

declare @count2 int
exec proc_view3 1, 20, @count = @count2 output
print @count2

declare @count2 int
exec proc_view3 1, 20, @count = @count2 output
print N'So ban ghi tim duoc ' + convert(nvarchar, @count2)

declare @count int
exec proc_view3 1, 20, @count = @count output
print N'So ban ghi tim duoc ' + convert(nvarchar, @count)