-- TABLES
-- Table: Guest
CREATE TABLE Guest (
GuestID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(15),
Address TEXT,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Table: Room
CREATE TABLE Room (
RoomID INT PRIMARY KEY AUTO_INCREMENT,
RoomNumber VARCHAR(10) UNIQUE NOT NULL,
Type ENUM('Single', 'Double', 'Suite') NOT NULL,
PricePerNight DECIMAL(10, 2) NOT NULL CHECK (PricePerNight >= 0),
Status ENUM('Available', 'Booked', 'Maintenance') NOT NULL DEFAULT 'Available',
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Table: Booking
CREATE TABLE Booking (
BookingID INT PRIMARY KEY AUTO_INCREMENT,
GuestID INT,
RoomID INT,
CheckInDate DATE NOT NULL,
CheckOutDate DATE NOT NULL,
TotalAmount DECIMAL(10, 2) CHECK (TotalAmount >= 0),
PaymentStatus ENUM('Pending', 'Paid') NOT NULL DEFAULT 'Pending',
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (GuestID) REFERENCES Guest(GuestID) ON DELETE CASCADE,
FOREIGN KEY (RoomID) REFERENCES Room(RoomID) ON DELETE CASCADE,
CHECK (CheckOutDate > CheckInDate)
);
-- Table: Payment
CREATE TABLE Payment (
PaymentID INT PRIMARY KEY AUTO_INCREMENT,
BookingID INT,
PaymentDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Amount DECIMAL(10, 2) NOT NULL CHECK (Amount >= 0),
Method ENUM('Card', 'Cash', 'UPI') NOT NULL,
FOREIGN KEY (BookingID) REFERENCES Booking(BookingID) ON DELETE CASCADE
);
-- Table: Staff
CREATE TABLE Staff (
StaffID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Role VARCHAR(50) NOT NULL,
Contact VARCHAR(15),
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- SAMPLE DATA
-- Guests
INSERT INTO Guest (Name, Email, Phone, Address)
VALUES
('John Doe', 'john@example.com', '1234567890', '123 Main St'),
('Emma Watson', 'emma@example.com', '9876543210', '456 Elm St'),
('David Miller', 'david@example.com', '9123456789', '789 Oak St');
-- Rooms
INSERT INTO Room (RoomNumber, Type, PricePerNight, Status)
VALUES
('101', 'Single', 1500.00, 'Available'),
('102', 'Double', 2500.00, 'Booked'),
('201', 'Suite', 4000.00, 'Available'),
('202', 'Double', 2600.00, 'Maintenance');
-- Bookings
INSERT INTO Booking (GuestID, RoomID, CheckInDate, CheckOutDate, TotalAmount, PaymentStatus)
VALUES
(1, 1, '2025-04-20', '2025-04-22', 3000.00, 'Paid'),
(2, 2, '2025-04-21', '2025-04-23', 5000.00, 'Pending'),
(3, 3, '2025-04-25', '2025-04-27', 8000.00, 'Pending');
-- Payments
INSERT INTO Payment (BookingID, Amount, Method)
VALUES
(1, 3000.00, 'Card'),
(2, 5000.00, 'UPI');
-- Staff
INSERT INTO Staff (Name, Role, Contact)
VALUES
('Alice Smith', 'Manager', '9876543210'),
('Robert Brown', 'Receptionist', '9988776655'),
('Nina Patel', 'Housekeeping', '8877665544');
-- SAMPLE QUERIES
-- 1. View all bookings
SELECT * FROM Booking;
-- 2. View available rooms
SELECT RoomNumber, Type, PricePerNight FROM Room
WHERE Status = 'Available';
-- 3. Get full booking details with guest and room info
SELECT
b.BookingID,
g.Name AS GuestName,
r.RoomNumber,
r.Type,
b.CheckInDate,
b.CheckOutDate,
b.TotalAmount,
b.PaymentStatus
FROM Booking b
JOIN Guest g ON b.GuestID = g.GuestID
JOIN Room r ON b.RoomID = r.RoomID;
-- 4. Check pending payments
SELECT
g.Name,
b.BookingID,
b.TotalAmount - IFNULL(p.Amount, 0) AS RemainingAmount
FROM Booking b
JOIN Guest g ON b.GuestID = g.GuestID
LEFT JOIN Payment p ON b.BookingID = p.BookingID
WHERE b.PaymentStatus = 'Pending';
-- 5. List all staff and their roles
SELECT * FROM Staff;
-- 6. Get total revenue from all paid bookings
SELECT SUM(Amount) AS TotalRevenue FROM Payment;
-- 7. Show booking count per guest
SELECT g.Name, COUNT(b.BookingID) AS TotalBookings
FROM Guest g
LEFT JOIN Booking b ON g.GuestID = b.GuestID
GROUP BY g.GuestID;
-- 8. Show room utilization (how many times each room is booked)
SELECT r.RoomNumber, COUNT(b.BookingID) AS TimesBooked
FROM Room r
LEFT JOIN Booking b ON r.RoomID = b.RoomID
GROUP BY r.RoomID;
Telegram: DB developers channel