By GokiSoft.com| 21:10 20/12/2023|
SQL Server/MySQL

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

Tạo bang tên là hotel gồm các thuộc tính

- id : trường khóa chính tự tăng

- tên khách sạn

- địa chỉ khách sạn

- diện tích

- chủ sở hữu khách sạn

Tạo bảng Room gồm các thuộc tính

- room_no : trường khóa chính

- id_hotel khóa ngoài liên két vs bảng hotel

- diện tích

- loại phòng

- floor : phòng thuộc tầng mấy của KH

Tạo bảng book gồm các thuốc tính

- id : khóa chính tự tăng

- room_no : khóa ngoài liên kết vs bảng Room

- ngày đặt phòng

- ngày trả phòng

- số lượng người ở

- Giá tiền

Yêu cầu thiết kế

1) Tạo CSDL dữ liệu và các bảng trên

2) 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

    2.1) Tất cả các dữ liệu

    2.2) Chỉ những phòng có diện tích 30 m2 trở lên

3) Thống kê theo dữ liệu : Tên KS, địa chỉ, số phòng

    - Tất cả

    - Số phòng > 5

4) Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích phòng lớn nhất

5) Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích phòng nhỏ nhất

6) 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

7) 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

8) Thông kê theo dữ liệu : Tên KS, địa chỉ, khách sạn ko có phòng nào.

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

https://gokisoft.com/1788

Bình luận

avatar
TRẦN VĂN ĐIỆP [Teacher]
2021-03-09 07:29:29



-- Tao database
create database hotel_db

-- Active database
use hotel_db

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

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

create table book (
	id int primary key identity(1,1),
	room_no nvarchar(20) references room (room_no),
	checkin datetime,
	checkout datetime,
	amount int
)

-- Insert database
insert into hotel (name, address, area, owner_name)
values
('A1', '54 Le Thanh Nghi', '200', 'A'),
('A2', '285 Doi Can', '300', 'A'),
('A3', '12 Tran Duy Hung', '50', 'B')

insert into room(room_no, hotel_id, area, type, floor)
values
('R001', 1, 20, 'NORMAL', 1),
('R002', 1, 15, 'VIP', 2),
('R003', 1, 30, 'VIP', 2),
('K001', 2, 20, 'NORMAL', 1),
('K002', 2, 20, 'VIP', 2)

insert into book (room_no, checkin, checkout, amount)
values
('R001', '2021-01-15 10:00:00', '2021-01-16 18:00:00', 2),
('R001', '2021-03-02 10:00:00', '2021-03-04 18:00:00', 2),
('R002', '2021-02-05 10:00:00', '2021-02-06 18:00:00', 4),
('R002', '2021-01-15 10:00:00', '2021-01-16 18:00:00', 2),
('K001', '2021-02-15 10:00:00', '2021-02-16 18:00:00', 3),
('K002', '2021-02-15 10:00:00', '2021-02-16 18:00:00', 6),
('R003', '2021-02-15 10:00:00', '2021-02-16 18:00:00', 4)

-- Query: Thong tin phong cua KS
select hotel.name, hotel.address, room.room_no, room.type, room.floor, room.area
from hotel, room
where hotel.id = room.hotel_id

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

select hotel.name, hotel.address, room.room_no, room.type, room.floor, room.area
from hotel, room
where hotel.id = room.hotel_id
	and room.area >= 30

-- Thong ke: so luong phong tung khach san
select hotel.name, hotel.address, count(room.room_no) 'Room Count'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
order by 'Room Count' desc

select hotel.name, hotel.address, count(room.room_no) 'Room Count'
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 'Room Count' desc

select hotel.name, hotel.address, max(room.area) 'Room Max Area'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address

select hotel.name, hotel.address, min(room.area) 'Room Min Area'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address

select hotel.name, hotel.address, sum(room.area) 'Total Area'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address

select hotel.name, hotel.address, avg(room.area) 'Avg Area'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address

select hotel.name, hotel.address, count(room.room_no) 'Room Count'
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 'Room Count' desc

-- Create view count room
create view view_room_count
as
select hotel.name, hotel.address, count(room.room_no) 'Room Count'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address

select * from view_room_count
order by 'Room Count' desc

-- output: Gay kho khan chut.


avatar
Lê Trọng Nghĩa [community,C2009I]
2021-03-06 09:47:46



create database KhachSan

use KhachSan

create table QuanLyKhachSan1(
	id int primary key identity(1,1),
	ten_khach_san nvarchar(50),
	dia_chi_khach_san nvarchar(50),
	dien_tich float,
	chu_su_huu nvarchar(50)
)

create table Room1(
	room_no int primary key,
	id_hotel int,
	dien_tich float,
	loai_phong nvarchar(50),
	floor int
)
create table Book1(
	id int primary key identity(1,1),
	room_no int,
	ngay_dat date,
	ngay_tra date,
	so_nguoi int
)
insert into QuanLyKhachSan1(ten_khach_san ,dia_chi_khach_san ,dien_tich ,chu_su_huu)
values
('KS1','Dia Chi 1','200','Nguyen Van A'),
('KS2','Dia Chi 2','200','Nguyen Van B'),
('KS3','Dia Chi 3','200','Nguyen Van C'),
('KS4','Dia Chi 4','200','Nguyen Van D'),
('KS5','Dia Chi 5','200','Nguyen Van E')


insert into Room1(room_no ,id_hotel ,dien_tich ,loai_phong ,floor)
values
(101 ,'1' ,'50' ,'Don' ,1),
(102 ,'2' ,'50' ,'Doi' ,2),
(103 ,'3' ,'50' ,'Villa' ,3),
(104 ,'4' ,'50' ,'Villa' ,4),
(105 ,'5' ,'50' ,'Villa' ,5)


insert into Book1(room_no ,ngay_dat ,ngay_tra , so_nguoi)
values
(101,'2020-12-04','2020-12-09',5),
(102,'2020-09-04','2020-09-09',5),
(103,'2020-12-05','2020-12-26',5),
(104,'2020-11-09','2020-11-25',5),
(105,'2020-06-04','2020-06-10',5)

select *from QuanLyKhachSan1
select *from Room1
select *from Book1

select QuanLyKhachSan1.ten_khach_san, QuanLyKhachSan1.dia_chi_khach_san, Room1.loai_phong, Room1.floor
from QuanLyKhachSan1, Room1 
where QuanLyKhachSan1.id = Room1.id_hotel
	and Room1.dien_tich > 30 

select QuanLyKhachSan1.ten_khach_san, QuanLyKhachSan1.dia_chi_khach_san, count(Room1.room_no)as 'so phong'
from QuanLyKhachSan1, Room1
where QuanLyKhachSan1.id = Room1.id_hotel
group by QuanLyKhachSan1.ten_khach_san, QuanLyKhachSan1.dia_chi_khach_san

select QuanLyKhachSan1.ten_khach_san, QuanLyKhachSan1.dia_chi_khach_san, Room1.dien_tich
from QuanLyKhachSan1, Room1
where QuanLyKhachSan1.id = Room1.id_hotel
order by Room1.dien_tich desc

select QuanLyKhachSan1.ten_khach_san, QuanLyKhachSan1.dia_chi_khach_san, Room1.dien_tich
from QuanLyKhachSan1, Room1
where QuanLyKhachSan1.id = Room1.id_hotel
order by Room1.dien_tich asc

select QuanLyKhachSan1.ten_khach_san, QuanLyKhachSan1.dia_chi_khach_san ,sum(Room1.dien_tich) 'tong dien tich'
from QuanLyKhachSan1, Room1
where QuanLyKhachSan1.id = Room1.id_hotel
group by QuanLyKhachSan1.ten_khach_san, QuanLyKhachSan1.dia_chi_khach_san


avatar
Trinh Huy Hung [community,C2009I]
2021-03-06 08:48:20



create database ManagementHotel

use ManagementHotel

create table Hotel(
	Id int identity(1, 1) primary key,
	HotelName nvarchar(50),
	Address nvarchar(100),
	Area float,
	HotelOwner nvarchar(50)
)

create table Room(
	Room_no int primary key,
	Id_hotel int,
	Area float,
	Type nvarchar(50),
	floor int
)

create table Book(
	Id int identity(1, 1),
	Room_no int ,
	CheckIn datetime,
	CheckOut datetime,
	People int
)
alter table Book 
add primary key (Id)

alter table Room
add foreign key (Id_hotel) references Hotel(Id)

alter table Book 
add foreign key (Room_no) references Room(Room_no)

insert into Hotel(HotelName, Address, Area, HotelOwner)
values 
('Da', 'D1', 100, 'Davic'),
('Ad', 'A1', 150, 'Adam'),
('Bu', 'B1', 200, 'Budha'),
('Ci', 'C1', 300, 'Cikita')

insert into Room(Room_no, Id_hotel, Area, Type, floor)
values
(3, 1, 20, 'Small', 5),
(4, 2, 25, 'Small', 5),
(5, 1, 60, 'Vip', 12),
(6, 2, 70, 'Vip', 15),
(7, 3, 90, 'Vip', 20),
(8, 1, 30, 'Normal', 5),
(9, 2, 35, 'Normal', 6),
(10, 3, 45, 'Normal', 7)

insert into Book(Room_no, CheckIn, CheckOut, People)
values 
(6, '2021-03-02', '2021-03-06', 4),
(7, '2021-03-01', '2021-03-04', 5),
(3, '2021-03-04', '2021-03-06', 1),
(9, '2021-03-02', '2021-03-03', 2)

--2
select Hotel.HotelName, Hotel.Address, Room.Room_no, Room.Type, Room.floor
from Hotel, Room
where Hotel.Id=Room.Id_hotel
order by Hotel.HotelName

select Hotel.HotelName, Hotel.Address, Room.Room_no, Room.Type, Room.floor, Room.Area
from Hotel, Room
where Hotel.Id=Room.Id_hotel and Room.Area>=30
order by Hotel.HotelName

--3

select Hotel.HotelName, Hotel.Address, Count(Room.Room_no)
from Hotel left join Room on Hotel.Id=Room.Id_hotel
group by  Hotel.HotelName, Hotel.Address

select Hotel.HotelName, Hotel.Address, Count(Room.Room_no) as Rooms 
from Hotel left join Room on Hotel.Id=Room.Id_hotel
group by  Hotel.HotelName, Hotel.Address
having Count(Room.Room_no)>=3

--4
select Hotel.HotelName, Hotel.Address, Max(Room.Area) as MaxArea 
from Hotel, Room
where Hotel.Id=Room.Id_hotel
group by Hotel.HotelName, Hotel.Address

--5
select Hotel.HotelName, Hotel.Address, Min(Room.Area) as MinArea 
from Hotel, Room
where Hotel.Id=Room.Id_hotel
group by Hotel.HotelName, Hotel.Address

--6
select Hotel.HotelName, Hotel.Address, Sum(Room.Area) as TotalArea 
from Hotel, Room
where Hotel.Id=Room.Id_hotel
group by Hotel.HotelName, Hotel.Address

--7
select Hotel.HotelName, Hotel.Address, Avg(Room.Area) as AvgArea 
from Hotel, Room
where Hotel.Id=Room.Id_hotel
group by Hotel.HotelName, Hotel.Address

--8
select Hotel.HotelName, Hotel.Address, Count(Room.Room_no) as Rooms 
from Hotel left join Room on Hotel.Id=Room.Id_hotel
group by  Hotel.HotelName, Hotel.Address
having Count(Room.Room_no)=0


avatar
Vũ Trung Kiên [C2009I]
2021-03-06 08:43:01


#1788.sql


create database QuanLyKS

use QuanLyKS

create table Hotel (
	id int identity,
	name nvarchar(50) not null,
	address nvarchar(200) not null,
	DienTich int not null,
	ChuSH nvarchar(50) not null
)

create table Room (
	RoomNo int  not null,
	id_hotel int  not null,
	DienTich int not null,
	Loai nvarchar(20) not null,
	floor int not null
)

create table Book (
	id int identity,
	RoomNo int  not null,
	DateIn date  not null,
	DateOut date not null,
	SoNguoi int  not null
)

alter table Hotel
add constraint Hotel_PK primary key (id)

alter table Room
add constraint Room_PK primary key (RoomNo)

alter table Book
add constraint Book_PK primary key (id)

alter table Book
add constraint Book_FK foreign key (RoomNo) references Room (RoomNo)

alter table Room
add constraint Room_Hotel_FK foreign key (id_hotel) references Hotel (id)

insert into Hotel(address, name, DienTich, ChuSH)
values
('HN', 'HT1', '1100', 'Tran Van A'),
('HN', 'HT2','2100', 'Tran Van B'),
('HCM', 'HT3','1500', 'Tran Van C'),
('DN', 'HT4','1000', 'Tran Van D'),
('HN', 'HT5','1900', 'Tran Van E')

insert into Room(RoomNo,id_hotel, DienTich, Loai, floor)
values
(101, 2, 30, 'vip', 1),
(151, 3, 29, 'thuong', 1),
(102, 1, 30, 'vip', 2),
(611, 3, 100, 'vip', 6),
(401, 1, 35, 'thuong', 4),
(111, 3, 30, 'vip', 1),
(141, 3, 29, 'thuong', 1),
(103, 1, 30, 'vip', 2),
(651, 3, 100, 'vip', 6),
(421, 3, 35, 'thuong', 4)

insert into Book(RoomNo, DateIn, DateOut, SoNguoi)
values
('101', '2021-02-01', '2021-02-03', 6),
('611', '2021-01-01', '2021-02-03', 23),
('151', '2021-01-11', '2021-01-12', 1),
('401', '2021-01-21', '2021-02-01', 5),
('101', '2021-02-01', '2021-02-05', 3)

select Hotel.name, Hotel.address, Room.RoomNo, Room.Loai, Room.floor 
	from Hotel left join Room on Hotel.id = Room.id_hotel
	order by Hotel.name

select Hotel.name, Hotel.address, Room.RoomNo, Room.Loai, Room.floor 
	from Hotel left join Room on Hotel.id = Room.id_hotel
	where Room.DienTich >= 30
	order by Hotel.name 

select Hotel.name, Hotel.address, count(Room.id_hotel) as SoPhong
	from Hotel left join Room on hotel.id = Room.id_hotel
	group by Hotel.name, Hotel.address
	order by SoPhong desc

select Hotel.name, Hotel.address, count(Room.id_hotel) as SoPhong
	from Hotel, Room
	where hotel.id = Room.id_hotel
	group by Hotel.name, Hotel.address
	having count(Room.id_hotel) >= 5

select Hotel.name, Hotel.address, max(Room.DienTich) as DienTichMax
	from Hotel, Room
	where hotel.id = Room.id_hotel
	group by Hotel.name, Hotel.address
	order by Hotel.name

select Hotel.name, Hotel.address, min(Room.DienTich) as DienTichMin
	from Hotel, Room
	where hotel.id = Room.id_hotel
	group by Hotel.name, Hotel.address
	order by Hotel.name

select Hotel.name, Hotel.address, sum(Room.DienTich) as TongDienTich
	from Hotel, Room
	where hotel.id = Room.id_hotel
	group by Hotel.name, Hotel.address
	order by Hotel.name

select Hotel.name, Hotel.address, avg(Room.DienTich) as DienTichTB
	from Hotel, Room
	where hotel.id = Room.id_hotel
	group by Hotel.name, Hotel.address
	order by Hotel.name

select Hotel.name, Hotel.address, count(Room.id_hotel) as SoPhong
	from Hotel left join Room on hotel.id = Room.id_hotel
	group by Hotel.name, Hotel.address
	having count(Room.id_hotel) = 0

select * from Hotel
select * from Room
select * from Book


avatar
Nguyễn Hữu Hiếu [T2008A]
2020-12-16 03:27:07



create database quanLyKhachSan1788
use quanLyKhachSan1788

--Create table
create table hotel(
	id int primary key identity(1,1),
	namehotel nvarchar(100),
	addresshotel nvarchar(200),
	area nvarchar(10),
	boss nvarchar(100),
	number_room int
)

create table room (
	id int primary key identity(1,1),
	id_hotel int references hotel(id),
	area_room nvarchar(10),
	type nvarchar(25),
	floor_room int
)

create table book (
	id int primary key identity(1,1),
	id_book int references room(id),
	date_in date,
	date_out date,
	number_person int
)

insert into hotel (namehotel, addresshotel, area, boss,number_room)
values
('A25','Ha Noi','200 m2', 'Nguyen A', 10),
('Hoa Binh','Ha Nam','300 m2', 'Nguyen B', 20),
('Rosse','Phu Quoc','20 m2', 'Nguyen C', 15),
('Solbeach','Quy Nhon','40 m2', 'Nguyen D',3),
('Hoa Hong','TP HCM','25 m2', 'Nguyen E', 100)

insert into room (id_hotel,area_room,type,floor_room)
values
(2,'25 m2','VIP',3),
(1,'35 m2','VIP 1',3),
(3,'55 m2','VIP 1',2),
(4,'15 m2','VIP 2',4),
(5,'25 m2','VIP 2',2),
(1,'35 m2','VIP 3',2),
(2,'25 m2','VIP 3',4),
(3,'45 m2','VIP 4',5),
(2,'35 m2','VIP 3',1)


insert into book(id_book,date_in,date_out,number_person)
values
(1,'2020-09-20','2020-09-22',3),
(2,'2020-09-21','2020-09-23',2),
(2,'2020-09-21','2020-09-23',1),
(3,'2020-09-22','2020-09-25',5),
(4,'2020-09-22','2020-09-25',1),
(5,'2020-09-23','2020-09-25',4),
(2,'2020-09-23','2020-09-28',3),
(3,'2020-09-24','2020-09-28',3)

--Hien thi
select * from book
select * from hotel
select * from room
drop table hotel

-- Tên KS, địa chỉ, mã phòng, loại phòng, tầng -- phòng có diện tích 30 m2 trở lên
select hotel.namehotel, hotel.addresshotel, room.id, room.type, room.floor_room, room.area_room
from hotel, room
where hotel.id = room.id_hotel and room.area_room > '30 m2'

--Tên KS, địa chỉ, so phong --    - Số phòng > 5
select hotel.namehotel, hotel.addresshotel, hotel.number_room
from hotel
where hotel.number_room > 10

-- : Tên KS, địa chỉ, diện tích phòng lớn nhất, nho nhat
create view view_max1 as
	select hotel.namehotel, hotel.addresshotel, room.area_room
	from hotel, room
	where hotel.id = room.id_hotel
select * from view_max1
order by view_max1.area_room asc

select * from view_max1
order by view_max1.area_room desc
--De kieu du lieu la nvarchar ko tinh tong vaf TB duowc, fai chuyen sang float thi hop ly hon
select namehotel, addresshotel, sum(area_room)
from view_max1
group by namehotel, addresshotel






avatar
Do Trung Duc [T2008A]
2020-12-14 06:45:09



create database QuanlyKhachSan_11_12_2020
use QuanlyKhachSan_11_12_2020 

create table Hotel(
HotelID int identity(1,1),
HotelName nvarchar(200),
HotelAddress nvarchar(200),
HotelArea float,
HotelOwner nvarchar(200),
constraint PK_Hotel primary key (HotelID) 
)

create table Room(
Room_no nvarchar(100),
HotelID int,
RoomArea float,
RoomType nvarchar(200),
RoomFloor int,
constraint PK_Room primary key (Room_no) ,
constraint FK_Room_HotelID foreign key (HotelID) references Hotel(HotelID)
)

create table Book(
BookID int identity(1,1),
Room_no nvarchar(100),
TimeCheckin datetime,
TimeCheckout datetime,
TotalGuest int
constraint PK_Book primary key (BookID),
constraint FK_Book_Room_no foreign key (Room_no) references Room(Room_no)
)

---Add ban ghi
select * from Hotel
select * from Room
select * from Book

insert into Hotel(HotelName,HotelAddress,HotelArea,HotelOwner)
values 
('Daweo','Kim Ma street','5000','Tran Van Diep'),
('Paradise','CauGiay street','4000','Do Trung Duc'),
('Metropolist','NguyenDu street','3000','Nguyen Tien Dat'),
('Angel','Xuan Thuy street','2000','Nguyen Bich Nhung'),
('Cloudy','Cau Giay street','1000','Tran Thu Trang')

insert into Room(Room_no,HotelID,RoomArea,RoomType,RoomFloor)
values 
('Daweo101','1','50','Singer',1),
('Daweo102','1','50','Singer',1),
('Daweo103','1','50','Singer',1),
('Daweo104','1','70','Couple',1),
('Daweo105','1','70','Couple',1),

('Paradise101','2','35','Singer',1),
('Paradise102','2','35','Singer',1),
('Paradise103','2','35','Singer',1),
('Paradise104','2','45','Coupler',1),
('Paradise105','2','45','Couple',1),

('Metropolist101','3','30','Singer',1),
('Metropolist102','3','30','Singer',1),
('Metropolist103','3','30','Singer',1),
('Metropolist104','3','40','Couple',1),
('Metropolist105','3','40','Couple',1),

('Angel101','4','25','Singer',1),
('Angel102','4','25','Singer',1),
('Angel103','4','25','Singer',1),
('Angel104','4','35','Couple',1),
('Angel105','4','35','Couple',1),

('Cloudy101','5','15','Singer',1),
('Cloudy102','5','15','Singer',1),
('Cloudy103','5','15','Singer',1),
('Cloudy104','5','30','Couple',1),
('Cloudy105','5','30','Couple',1)

insert into Book(Room_no,TimeCheckin,TimeCheckout,TotalGuest)
values 
('Daweo101','2020/10/10','2020/12/11',1),
('Daweo102','2020/10/10','2020/12/11',1),
('Daweo104','2020/10/10','2020/12/11',2),

('Paradise101','2020/10/10','2020/12/11',1),
('Paradise102','2020/10/10','2020/12/11',1),
('Paradise104','2020/10/10','2020/12/11',2),
('Paradise105','2020/10/10','2020/12/11',2),

('Metropolist101','2020/10/10','2020/12/11',1),
('Metropolist102','2020/10/10','2020/12/11',1),
('Paradise104','2020/10/10','2020/12/11',2),

('Angel104','2020/10/10','2020/12/11',2),
('Angel105','2020/10/10','2020/12/11',2)

select * from Hotel
select * from Room
select * from Book

 --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

 create view Hienthithontin_tatcacackhachsan
 as
 select Hotel.HotelName, Hotel.HotelAddress, Room.Room_no, Room.RoomType, Room.RoomFloor
 From Hotel left join Room on Hotel.HotelID = Room.HotelID

 select * from Hienthithontin_tatcacackhachsan where HotelName = 'Paradise'

 --Hiển thị nhung phong co dien tich 30m2 tro len
 create view Hienthiphongtren30m2
 as
 select Hotel.HotelName, Hotel.HotelAddress, Room.Room_no, Room.RoomType, Room.RoomFloor, Room.RoomArea
 From Hotel left join Room on Hotel.HotelID = Room.HotelID where Room.RoomArea >=30

 select * from Hienthiphongtren30m2

 --3) Thống kê theo dữ liệu : Tên KS, địa chỉ, số phòng khach san co so phong > 5
insert into Room(Room_no,HotelID,RoomArea,RoomType,RoomFloor)
values 
('Daweo201','1','50','Singer',1),
('Daweo202','1','50','Singer',1),
('Daweo203','1','50','Singer',1),
('Daweo204','1','70','Couple',2),
('Daweo205','1','70','Couple',2)

select Hotel.HotelName, Hotel.HotelAddress, count(Room.Room_no) as Tongsophong
From Hotel inner join Room on Hotel.HotelID = Room.HotelID 
group by Hotel.HotelName, Hotel.HotelAddress
having count(Room.Room_no) > 5

--Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích phòng lớn nhất
select Hotel.HotelName, Hotel.HotelAddress, Room.RoomType, Room.RoomArea
From Hotel inner join Room on Hotel.HotelID = Room.HotelID 
where Room.RoomArea >=70
order by Room.RoomArea desc 

--Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích phòng nho nhất
select Hotel.HotelName, Hotel.HotelAddress, Room.RoomType, Room.RoomArea
From Hotel inner join Room on Hotel.HotelID = Room.HotelID 
where Room.RoomArea =15
order by Room.RoomArea asc 

--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.HotelName, Hotel.HotelAddress, sum(Room.RoomArea) as Tongdientichcacphong
From Hotel inner join Room on Hotel.HotelID = Room.HotelID 
group by Hotel.HotelName, Hotel.HotelAddress

--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.HotelName, Hotel.HotelAddress,Room.RoomType, avg(Room.RoomArea) as dentichtrungbinhtungloaiphong
From Hotel inner join Room on Hotel.HotelID = Room.HotelID 
group by Hotel.HotelName, Hotel.HotelAddress, Room.RoomType

--Thông kê theo dữ liệu : Tên KS, địa chỉ, khách sạn , nhung phong chua duoc dat

select Hotel.HotelName, Hotel.HotelAddress, Room.Room_no,Book.BookID
From Hotel inner join Room on Hotel.HotelID = Room.HotelID left join Book on Room.Room_no = Book.Room_no
where Book.BookID is null

select Hotel.HotelName, Hotel.HotelAddress, Book.BookID, Book.Room_no
From Hotel left join Room on Hotel.HotelID = Room.HotelID left join Book on Room.Room_no = Book.Room_no
where Book.TimeCheckin >  '2020-9-10' and  Book.TimeCheckin < getdate()



avatar
Trần Văn Lâm [T2008A]
2020-12-13 11:05:17



create database Quan_li_khach_san
use Quan_li_khach_san
create table Hotel(
	id int primary key identity(1,1),
	name nvarchar(100),
	address nvarchar(100),
	acreage float,
	boss_hotel nvarchar(50)
)
create table Room(
	room_no nvarchar(20) primary key,
	id_hotel int references Hotel(id),
	acreage float,
	kind_of_room nvarchar(50),
	floors int,
	num_room int
)
create table Books(
	id int primary key identity(1,1),
	room_no nvarchar(20) references Room(room_no),
	bookroom_at date,
	payroom_at date,
	num_people int
)
select * from Hotel
insert into Hotel(name,address,acreage,boss_hotel)
values
('Royal Hotel','My Dinh-Ha Noi',500,'Tran Van Lam'),
('Aptech Hotel','Ton That Thuyet-Ha Noi',700,'Tran Van Diep'),
('Barcelona Hotel','Hoang Mai-Ha Noi',650,'Lee Tran'),
('Chien Thang Hotel','Nguyen Hoang-Ha Noi',550,'Martin Tran'),
('Vin Hotel','Thanh Xuan-Ha Noi',750,'Leo Tran')
select * from Room
insert into Room(room_no,id_hotel,acreage,kind_of_room,floors,num_room)
values
('T01',1,45,'Vip',7,10),
('T02',3,55,'Tong Thong',9,4),
('T03',2,35,'Cao Cap',10,6),
('T04',2,25,'Rieng Tu',6,8),
('T05',4,40,'Tinh Yeu',5,7)
select * from Books
insert into Books(room_no,bookroom_at,payroom_at,num_people)
values
('T01','2020-12-01','2002-12-10',3),
('T02','2020-12-01','2002-12-10',2),
('T03','2020-12-01','2002-12-10',1),
('T04','2020-12-01','2002-12-10',4),
('T05','2020-12-01','2002-12-10',2)
select Hotel.name,Hotel.address,Room.room_no,Room.kind_of_room,Room.floors
from Hotel,Room
where Hotel.id = Room.id_hotel 

select Hotel.name,Hotel.address,Room.room_no,Room.kind_of_room,Room.floors
from Hotel,Room
where Hotel.id = Room.id_hotel
	and Room.acreage > 30

select Hotel.name,Hotel.address,Room.num_room
from Hotel,Room
where Hotel.id = Room.id_hotel

select Hotel.name,Hotel.address,Room.num_room
from Hotel,Room
where Hotel.id = Room.id_hotel
	and Room.num_room > 5

select Hotel.name,Hotel.address,max(Room.acreage) as 'Dien tich phong lon nhat'
from Hotel,Room
where Hotel.id = Room.id_hotel
group by Hotel.name,Hotel.address,Room.acreage

select Hotel.name,Hotel.address,min(Room.acreage) as 'Dien tich phong nho nhat'
from Hotel,Room
where Hotel.id = Room.id_hotel
group by Hotel.name,Hotel.address,Room.acreage

select Hotel.name,Hotel.address,sum(Room.acreage) as 'Tong dien tich cac phong'
from Hotel,Room
where Hotel.id = Room.id_hotel
group by Hotel.name,Hotel.address

select Hotel.name,Hotel.address,avg(Room.acreage) as 'Dien tich tb cac phong'
from Hotel,Room
where Hotel.id = Room.id_hotel
group by Hotel.name,Hotel.address

select Hotel.name,Hotel.address,Room.num_room
from Hotel,Room
where Hotel.id = Room.id_hotel
	and Room.num_room = 0


avatar
vuong huu phu [T2008A]
2020-12-11 13:53:40



create database Quanlykhachsan
use Quanlykhachsan

--Tạo bang tên là hotel
create table hotel (
id int identity primary key,
Ten_khach_san nvarchar(200),
Dia_chi_khach_san nvarchar(200),
Dien_tich float,
Chu_so_huu nvarchar(100)
)

---Tạo bảng Room 
create table Room (
room_no int primary key,
id_hotel  int,
Dien_tich float,
Loai_phong nvarchar(100),
tang int
)

------Tạo bảng book
create table book (
id int identity primary key,
room_no int,
Ngay_dat_phong datetime,
Ngay_tra_phong datetime,
So_nguoi int
)
alter table book
add constraint FK_room_no
foreign key (room_no)
references Room(room_no)

alter table Room
add constraint FK_id_hotel
foreign key (id_hotel)
references hotel(id)

insert into hotel(Ten_khach_san,Dia_chi_khach_san,Dien_tich,Chu_so_huu)
values
('Khach san P','Ha Noi','1093','Vuong Huu Phu'),
('Khach san H','Ha Noi','4793','Vuong Huu Phu'),
('Khach san U','Ha Noi','1993','Vuong Huu Phu'),
('Khach san D','Ha Noi','3093','Vuong Huu Phu'),
('Khach san E','Ha Noi','2907','Vuong Huu Phu'),
('Khach san P','Ha Noi','7093','Vuong Huu Phu'),
('Khach san T','Ha Noi','2093','Vuong Huu Phu'),
('Khach san R','Ha Noi','9463','Vuong Huu Phu'),
('Khach san A','Ha Noi','6849','Vuong Huu Phu'),
('Khach san i','Ha Noi','1859','Vuong Huu Phu')

insert into Room(room_no,id_hotel,Dien_tich,Loai_phong,tang)
values
('001','2','100','1 giuong ngu','1'),
('002','2','40','2 giuong ngu','4'),
('003','6','10','1 giuong ngu','1'),
('004','6','100','2 giuong ngu','2'),
('005','7','100','2 giuong ngu','1'),
('006','5','10','2 giuong ngu','1'),
('007','4','100','1 giuong ngu','2'),
('008','8','100','2 giuong ngu','4'),
('009','10','10','1 giuong ngu','3'),
('010','9','100','2 giuong ngu','2')

insert into book (room_no,Ngay_dat_phong,Ngay_tra_phong,So_nguoi)
values 
('001','2020-01-02','2020-01-16','2'),
('002','2020-01-02','2020-01-16','2'),
('003','2020-01-02','2020-01-16','2'),
('004','2020-01-02','2020-01-16','2'),
('005','2020-01-02','2020-01-16','1'),
('006','2020-01-02','2020-01-16','1'),
('007','2020-01-02','2020-01-16','2'),
('008','2020-01-02','2020-01-16','2'),
('009','2020-01-02','2020-01-16','2'),
('010','2020-01-02','2020-01-16','2')


---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.Ten_khach_san,hotel.Dia_chi_khach_san,Room.room_no,Room.Loai_phong,Room.tang
from hotel,Room
where Room.id_hotel = hotel.id

-----Chỉ những phòng có diện tích 30 m2 trở lên
select hotel.Ten_khach_san,hotel.Dia_chi_khach_san,Room.room_no,Room.Loai_phong,Room.tang
from hotel,Room
where Room.id_hotel = hotel.id and Room.Dien_tich >= 30

---Thống kê theo dữ liệu : Tên KS, địa chỉ, số phòng
select hotel.Ten_khach_san,hotel.Dia_chi_khach_san,Room.room_no
from hotel,Room
where Room.id_hotel = hotel.id

----- - Số phòng > 5
select hotel.Ten_khach_san,hotel.Dia_chi_khach_san,Room.room_no
from hotel,Room
where Room.id_hotel = hotel.id and Room.room_no > 5

----Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích phòng lớn nhất
select top 1 hotel.Ten_khach_san,hotel.Dia_chi_khach_san,Room.room_no,Room.Dien_tich
from hotel,Room
where Room.id_hotel = hotel.id
order by (Room.Dien_tich) desc

----Thông kê theo dữ liệu : Tên KS, địa chỉ, diện tích phòng nhỏ nhất
select top 1 hotel.Ten_khach_san,hotel.Dia_chi_khach_san,Room.room_no,Room.Dien_tich
from hotel,Room
where Room.id_hotel = hotel.id
order by (Room.Dien_tich) asc

-----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.Ten_khach_san,hotel.Dia_chi_khach_san,sum(Room.Dien_tich) as tong_dien_tich
from hotel,Room
where Room.id_hotel = hotel.id
group by  hotel.Ten_khach_san,hotel.Dia_chi_khach_san

----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.Ten_khach_san,hotel.Dia_chi_khach_san,avg(Room.Dien_tich) as dien_tich_tring_binh
from hotel,Room
where Room.id_hotel = hotel.id
group by  hotel.Ten_khach_san,hotel.Dia_chi_khach_san

----Thông kê theo dữ liệu : Tên KS, địa chỉ, khách sạn ko có phòng nào.
select hotel.Ten_khach_san,hotel.Dia_chi_khach_san
from hotel,Room,book
where Room.id_hotel = hotel.id and book.room_no = Room.room_no
and book.room_no is null and book.room_no = ''