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 database
use BT1788
go

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

create table room (
	room_no nvarchar(20) primary key,
	hotel_id int,
	area float,
	type nvarchar(20),
	floor int default 0
)
go

create table booking (
	id int primary key identity(1,1),
	room_no nvarchar(20),
	checkin datetime,
	checkout datetime,
	num int default 0,
	price float
)
go

-- add constraint
alter table room
add constraint fk_room_hotel foreign key (hotel_id) references hotel (id)
go

alter table booking
add constraint fk_booking_room foreign key (room_no) references room (room_no)
go

-- Insert data
insert into hotel (name, address, area, fullname)
values
('KS A', 'Ha Noi', 20, 'TRAN VAN A'),
('KS B', 'Ha Noi', 50, 'TRAN VAN B'),
('KS C', 'Ha Noi', 100, 'TRAN VAN C')
go

insert into room (room_no, hotel_id, type, floor, area)
values
('R001', 1, 'VIP', 2, 20),
('R002', 1, 'NORMAL', 3, 20),
('R003', 2, 'VIP', 1, 20),
('R004', 2, 'VIP', 2, 25),
('R005', 2, 'VIP', 2, 25)
go

insert into booking (room_no, checkin, checkout, num, price)
values
('R001', '2022-01-12', '2022-01-16', 2, 5000000),
('R001', '2022-01-17', '2022-01-22', 5, 120000000),
('R002', '2022-01-10', '2022-01-20', 2, 200000000)
go

-- Query
-- Tên KS (hotel), địa chỉ (hotel), mã phòng (room), loại phòng (room), tầng (room)
select hotel.name 'Hotel Name', hotel.address, room.room_no, room.type, room.floor
from hotel left join room on hotel.id = room.hotel_id
go

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

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

-- Số phòng > 2
select hotel.name '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
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 Name', hotel.address, max(room.area) 'Dien Tich MAX'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
go

select hotel.name 'Hotel Name', hotel.address, min(room.area) 'Dien Tich MIN'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
go

-- View
create view vw_hotel_list
as
select hotel.name 'Hotel Name', hotel.address, room.room_no, room.type, room.floor
from hotel left join room on hotel.id = room.hotel_id
go

alter view vw_hotel_list
as
select hotel.name hotel_name, hotel.address, room.room_no, room.type, room.floor
from hotel left join room on hotel.id = room.hotel_id
go

select * from vw_hotel_list

drop view vw_hotel_list
go

-- proc khong tham, ko tra du lieu ve
create proc proc_hotel
as
begin
	select hotel.name '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

	select hotel.name hotel_name, hotel.address, room.room_no, room.type, room.floor
	from hotel left join room on hotel.id = room.hotel_id
end

alter proc proc_hotel
as
begin
	-- code o day
end

drop proc proc_hotel
go

exec proc_hotel
go

-- proc co tham so
select hotel.name '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) > 1

select hotel.name '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

select hotel.name '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) > 3

create proc proc_hotel_count_room
	@limit int
as
begin
	select hotel.name '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) > @limit
end

exec proc_hotel_count_room 1
exec proc_hotel_count_room 2
exec proc_hotel_count_room 3

create proc proc_hotel_count_room2
	@limit int,
	@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
	having count(room.room_no) > @limit
end

exec proc_hotel_count_room2 1, 1

-- proc co tham so, co du lieu tra ve
-- hien thi so khach san
create proc proc_hotel_count
	@count int output
as
begin
	select * from hotel

	select @count = count(*) from hotel
end

declare @count2 int
exec proc_hotel_count @count = @count2 output
print N'So khach san tim thay: ' + convert(nvarchar, @count2)

declare @count int
exec proc_hotel_count @count = @count output
print N'So khach san tim thay: ' + convert(nvarchar, @count)