Program: Hotel Management System Using SQL
CODE:
/* 1. CREATE DATABASE */
CREATE DATABASE hotel_management;
USE hotel_management;
/* 2. GUESTS TABLE : Stores customer details */
CREATE TABLE guests (
guest_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique guest ID
first_name VARCHAR(50) NOT NULL, -- Guest first name
last_name VARCHAR(50) NOT NULL, -- Guest last name
phone VARCHAR(15), -- Contact number
email VARCHAR(100), -- Email address
id_proof VARCHAR(50) -- ID proof (Passport, Aadhaar, etc.)
);
/* 3. ROOMS TABLE: Stores room information */
CREATE TABLE rooms (
room_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique room ID
room_number VARCHAR(10) UNIQUE NOT NULL, -- Room number
room_type VARCHAR(30), -- Standard, Deluxe, Suite
price_per_night DECIMAL(10,2), -- Cost per night
status VARCHAR(20) DEFAULT 'Available' -- Available / Occupied /
Maintenance
);
/* 4. RESERVATIONS TABLE: Links guests with rooms */
CREATE TABLE reservations (
reservation_id INT PRIMARY KEY AUTO_INCREMENT,
guest_id INT, -- Reference to guest
room_id INT, -- Reference to room
check_in DATE, -- Check-in date
check_out DATE, -- Check-out date
status VARCHAR(20), -- Booked / Checked-in /
Checked-out
FOREIGN KEY (guest_id) REFERENCES guests(guest_id),
FOREIGN KEY (room_id) REFERENCES rooms(room_id)
);
/* 5. PAYMENTS TABLE: Stores payment details */
CREATE TABLE payments (
payment_id INT PRIMARY KEY AUTO_INCREMENT,
reservation_id INT, -- Reference to reservation
amount DECIMAL(10,2), -- Payment amount
payment_date DATE, -- Date of payment
payment_method VARCHAR(20), -- Cash / Card / UPI
FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id)
);
/* 6. INSERT SAMPLE DATA */
/* Insert guests */
INSERT INTO guests (first_name, last_name, phone, email, id_proof)
VALUES
('John', 'Doe', '9876543210', 'john@[Link]', 'Passport'),
('Jane', 'Smith', '9123456780', 'jane@[Link]', 'Driving License');
/* Insert rooms */
INSERT INTO rooms (room_number, room_type, price_per_night, status)
VALUES
('101', 'Deluxe', 3500, 'Available'),
('102', 'Standard', 2500, 'Available'),
('201', 'Suite', 6000, 'Available');
/* 7. MAKE A RESERVATION */
INSERT INTO reservations (guest_id, room_id, check_in, check_out, status)
VALUES (1, 1, '2026-02-01', '2026-02-05', 'Booked');
/* 8. UPDATE ROOM STATUS: When guest checks in */
UPDATE rooms
SET status = 'Occupied'
WHERE room_id = 1;
/* 9. VIEW AVAILABLE ROOMS */
SELECT *
FROM rooms
WHERE status = 'Available';
/* 10. VIEW CURRENT CHECKED-IN GUESTS */
SELECT
g.first_name,
g.last_name,
r.room_number,
rs.check_in,
rs.check_out
FROM guests g
JOIN reservations rs ON g.guest_id = rs.guest_id
JOIN rooms r ON rs.room_id = r.room_id
WHERE [Link] = 'Checked-in';
/* 11. CALCULATE TOTAL BILL */
SELECT
rs.reservation_id,
DATEDIFF(rs.check_out, rs.check_in) AS total_days,
r.price_per_night,
(DATEDIFF(rs.check_out, rs.check_in) * r.price_per_night) AS total_bill
FROM reservations rs
JOIN rooms r ON rs.room_id = r.room_id
WHERE rs.reservation_id = 1;
/* 12. RECORD PAYMENT */
INSERT INTO payments (reservation_id, amount, payment_date, payment_method)
VALUES (1, 14000, CURDATE(), 'Card');
/* 13. MONTHLY REVENUE REPORT */
SELECT
MONTH(payment_date) AS month,
YEAR(payment_date) AS year,
SUM(amount) AS total_revenue
FROM payments
GROUP BY YEAR(payment_date), MONTH(payment_date);
/* 14. GUESTS WITH PENDING PAYMENTS */
SELECT
g.first_name,
g.last_name
FROM guests g
JOIN reservations r ON g.guest_id = r.guest_id
LEFT JOIN payments p ON r.reservation_id = p.reservation_id
WHERE p.payment_id IS NULL;
Outputs:
1. Available Rooms
2. Current Checked-in Guests
3. Total Bill Calculation
4. Payments Table
5. Monthly Revenue Report
6. Guests With Pending Payments
7. Guests Table
8. Reservations Table