IMG-LOGO
×

Giáo Trình Môn Học

Khoá học lập trình SQL

Kiến Thức Core SQL Server

[Video] Hướng dẫn cách xác định kiểu dữ liệu, primary key, foreign key trong SQL - My SQL - SQL Server [Video] Hướng dẫn thao tác nhiểu bảng - select - multi table - join, inner join trong SQL Server [Video] Hướng dẫn phân tích CSDL - phân tích bảng, column trong bản - Học lập trình SQL, SQL Server [Video] Hướng dẫn tìm hiểu create, alter , insert table và bản chất primary key, foreign key - SQL Server [Video] Hướng dẫn tìm hiểu select, and, in, like, not in,group by .. having, order by asc, desc - SQL Server [Video] Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

View SQL Server

[Video] Hướng dẫn tìm hiểu view trong SQL Server Bài Giảng: Hướng dẫn tìm hiểu view + proc trong SQL - Lập trình SQL Server

Store & Procedure

[Video] Hướng dẫn tìm hiểu proc procedure trong SQL Server - Lập trình SQL Server, MySQL Bài Giảng: Xây dự hệ thống đưa đón học sinh bằng xe bus của Trường Học - Lập trình SQL Server [Video] Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

Trigger

[Video] Học Trigger qua ví dụ - quản lý nhà trên phố - Lập trình SQL Server [Video] Hướng dẫn học trigger qua bài quản lý điểm danh Aptech - Lập trình SQL Server [Video] Bài tập - Quản lý nhân khẩu - Lập trình SQL Server - MySQL




Trang Chủ SQL Server/MySQL Quản lý khách sạn - nâng cao - Lập trình Sql Server

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

by GokiSoft.com - 10:56 05/04/2021 8,190 Lượt Xem

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 ở

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.

Bình luận



Chia sẻ từ lớp học

TRẦN VĂN ĐIỆP [Teacher]

Ngày viết: 14:29 09/03/2021



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


Lê Trọng Nghĩa [community,C2009I]

Ngày viết: 16:47 06/03/2021



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


Trinh Huy Hung [community,C2009I]

Ngày viết: 15:48 06/03/2021



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


Vũ Trung Kiên [C2009I]

Ngày viết: 15:43 06/03/2021


#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


Nguyễn Hữu Hiếu [T2008A]

Ngày viết: 10:27 16/12/2020



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






Đã sao chép!!!