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