By GokiSoft.com| 19:44 08/04/2021|
SQL Server/MySQL

[Share Code] Hướng dẫn thiết kế database (CSDL) Quản lý khách sạn - nâng cao - Lập trình Sql Server BT2242

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


-- Create database: DB1788
create database DB1788

-- Active database
use DB1788




-- Create tables
create table hotel (
	id int primary key identity(1,1),
	name nvarchar(50) not null,
	address nvarchar(200),
	area float,
	owner_name nvarchar(50)
)

create table room (
	room_no int primary key identity(1,1),
	hotel_id int references hotel(id),
	area float,
	type nvarchar(20),
	floor int
)

-- date: 2021-04-08
-- datetime: 2021-04-08 19:01:30
create table book (
	id int primary key identity(1,1),
	room_no int references room(room_no),
	checkin datetime,
	checkout datetime,
	num int
)




-- Insert data
insert into hotel (name, area, address, owner_name)
values
('54 Le Thanh Nghi Hotel', 500, '54 Le Thanh Nghi', 'TRAN VAN DIEP'),
('285 Doi Can', 800, '285 Doi Can', 'GokiSoft')

insert into hotel (name, area, address, owner_name)
values
('Hoa Lu', 200, 'Ninh Binh', 'ABC')

insert into room (hotel_id, area, floor, type)
values
(1, 30, 2, 'PRO'),
(1, 40, 2, 'PRO'),
(1, 50, 3, 'VIP'),
(1, 30, 3, 'VIP'),
(1, 50, 4, 'GOLD')

insert into room (hotel_id, area, floor, type)
values
(2, 30, 4, 'PRO'),
(2, 40, 4, 'GOLD')

insert into book(room_no, num, checkin, checkout)
values
(1, 25, '2021-02-12 18:00:00', '2021-02-12 22:00:00'),
(2, 28, '2021-06-20 18:00:00', '2021-07-02 22:00:00'),
(3, 15, '2021-07-16 18:00:00', '2021-07-18 22:00:00'),
(4, 18, '2021-08-13 18:00:00', '2021-08-16 22:00:00'),
(5, 22, '2021-01-15 18:00:00', '2021-01-19 22:00:00'),
(1, 20, '2021-09-19 18:00:00', '2021-09-22 22:00:00')



-- TEST
select * from hotel
select * from room
select * from book




-- Query
---- 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
------ Tất cả các dữ liệu
select hotel.name 'Hotel Name', hotel.address, room.room_no, room.area, room.type, room.floor
from hotel, room
where hotel.id = room.hotel_id



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




---- Thống kê theo dữ liệu : Tên KS, địa chỉ, số phòng
------ Tat ca
select hotel.name 'Hotel Name', hotel.address, count(room.room_no) 'So Phong'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

------ Số phòng >= 3
select hotel.name 'Hotel Name', hotel.address, count(room.room_no) 'So Phong'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address
having count(room.room_no) >= 3

---- 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) 'Room Area (Max)'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

---- 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 Name', hotel.address, min(room.area) 'Room Area (Min)'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

---- 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 Name', hotel.address, sum(room.area) 'Room Area (SUM)'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

---- 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 Name', hotel.address, avg(room.area) 'Room Area (AVG)'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

---- 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 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
order by 'So Phong' asc




---- Kiem tra phong da duoc book: Ten KS, dia chi, room no, ngay book, ngay tra, so luong
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel, room, book
where hotel.id = room.hotel_id
	and room.room_no = book.room_no

---- Tuong duong: inner join (join)
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel join room on hotel.id = room.hotel_id
	join book on room.room_no = book.room_no




---- Hien thi tat ca khach san -> ko co book
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel left join room on hotel.id = room.hotel_id
	left join book on room.room_no = book.room_no



---- Hien thi tat ca khach san -> ko co book
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel join room on hotel.id = room.hotel_id
	left join book on room.room_no = book.room_no




---- Hien thi thong tin book voi dieu: ngay checkin -> '2021-06-06' toi '2021-08-10'
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel left join room on hotel.id = room.hotel_id
	left join book on room.room_no = book.room_no
where book.checkin between '2021-06-06' and '2021-08-10'

---- Hien thi thong tin book voi dieu: ngay checkin -> '2021-06-06 02:02:02' toi '2021-08-10 22:22:22'
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel left join room on hotel.id = room.hotel_id
	left join book on room.room_no = book.room_no
where book.checkin between '2021-06-06 02:02:02' and '2021-08-10 22:22:22'




Tags:

Liên kết rút gọn:

https://gokisoft.com/2242

Bình luận

avatar
TRẦN VĂN ĐIỆP [Teacher]
2021-04-08 12:44:50



-- Create database: DB1788
create database DB1788

-- Active database
use DB1788

-- Create tables
create table hotel (
	id int primary key identity(1,1),
	name nvarchar(50) not null,
	address nvarchar(200),
	area float,
	owner_name nvarchar(50)
)

create table room (
	room_no int primary key identity(1,1),
	hotel_id int references hotel(id),
	area float,
	type nvarchar(20),
	floor int
)

-- date: 2021-04-08
-- datetime: 2021-04-08 19:01:30
create table book (
	id int primary key identity(1,1),
	room_no int references room(room_no),
	checkin datetime,
	checkout datetime,
	num int
)

-- Insert data
insert into hotel (name, area, address, owner_name)
values
('54 Le Thanh Nghi Hotel', 500, '54 Le Thanh Nghi', 'TRAN VAN DIEP'),
('285 Doi Can', 800, '285 Doi Can', 'GokiSoft')

insert into hotel (name, area, address, owner_name)
values
('Hoa Lu', 200, 'Ninh Binh', 'ABC')

insert into room (hotel_id, area, floor, type)
values
(1, 30, 2, 'PRO'),
(1, 40, 2, 'PRO'),
(1, 50, 3, 'VIP'),
(1, 30, 3, 'VIP'),
(1, 50, 4, 'GOLD')

insert into room (hotel_id, area, floor, type)
values
(2, 30, 4, 'PRO'),
(2, 40, 4, 'GOLD')

insert into book(room_no, num, checkin, checkout)
values
(1, 25, '2021-02-12 18:00:00', '2021-02-12 22:00:00'),
(2, 28, '2021-06-20 18:00:00', '2021-07-02 22:00:00'),
(3, 15, '2021-07-16 18:00:00', '2021-07-18 22:00:00'),
(4, 18, '2021-08-13 18:00:00', '2021-08-16 22:00:00'),
(5, 22, '2021-01-15 18:00:00', '2021-01-19 22:00:00'),
(1, 20, '2021-09-19 18:00:00', '2021-09-22 22:00:00')

-- TEST
select * from hotel
select * from room
select * from book

-- Query
---- 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
------ Tất cả các dữ liệu
select hotel.name 'Hotel Name', hotel.address, room.room_no, room.area, room.type, room.floor
from hotel, room
where hotel.id = room.hotel_id

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

---- Thống kê theo dữ liệu : Tên KS, địa chỉ, số phòng
------ Tat ca
select hotel.name 'Hotel Name', hotel.address, count(room.room_no) 'So Phong'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

------ Số phòng >= 3
select hotel.name 'Hotel Name', hotel.address, count(room.room_no) 'So Phong'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address
having count(room.room_no) >= 3

---- 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) 'Room Area (Max)'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

---- 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 Name', hotel.address, min(room.area) 'Room Area (Min)'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

---- 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 Name', hotel.address, sum(room.area) 'Room Area (SUM)'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

---- 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 Name', hotel.address, avg(room.area) 'Room Area (AVG)'
from hotel, room
where hotel.id = room.hotel_id
group by hotel.name , hotel.address

---- 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 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
order by 'So Phong' asc

---- Kiem tra phong da duoc book: Ten KS, dia chi, room no, ngay book, ngay tra, so luong
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel, room, book
where hotel.id = room.hotel_id
	and room.room_no = book.room_no

---- Tuong duong: inner join (join)
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel join room on hotel.id = room.hotel_id
	join book on room.room_no = book.room_no

---- Hien thi tat ca khach san -> ko co book
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel left join room on hotel.id = room.hotel_id
	left join book on room.room_no = book.room_no


---- Hien thi tat ca khach san -> ko co book
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel join room on hotel.id = room.hotel_id
	left join book on room.room_no = book.room_no

---- Hien thi thong tin book voi dieu: ngay checkin -> '2021-06-06' toi '2021-08-10'
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel left join room on hotel.id = room.hotel_id
	left join book on room.room_no = book.room_no
where book.checkin between '2021-06-06' and '2021-08-10'

---- Hien thi thong tin book voi dieu: ngay checkin -> '2021-06-06 02:02:02' toi '2021-08-10 22:22:22'
select hotel.name, hotel.address, room.room_no, book.checkin, book.checkout, book.num
from hotel left join room on hotel.id = room.hotel_id
	left join book on room.room_no = book.room_no
where book.checkin between '2021-06-06 02:02:02' and '2021-08-10 22:22:22'