SQL Queries:
CREATE DATABASE IF NOT EXISTS HotelOcDB;
USE HotelOcDB;
DROP TABLE IF EXISTS HotelOccupancy;
DROP TABLE IF EXISTS Time;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Room;
DROP TABLE IF EXISTS Hotel;
DROP TABLE IF EXISTS City;
CREATE TABLE City (
CityID INT PRIMARY KEY,
Region VARCHAR(50),
State VARCHAR(50),
Country VARCHAR(50),
Zipcode VARCHAR(10)
);
CREATE TABLE Hotel (
HotelID INT PRIMARY KEY,
HotelName VARCHAR(100),
Rooms INT,
StarRating INT,
Region VARCHAR(50),
State VARCHAR(50),
Country VARCHAR(50),
CityID INT,
FOREIGN KEY (CityID) REFERENCES City(CityID)
);
CREATE TABLE Room (
RoomID INT PRIMARY KEY,
RoomType VARCHAR(50),
Max_Occupant INT,
No_of_beds INT,
Room_side VARCHAR(20),
A_C CHAR(1), -- Y/N
Renovation_year INT
);
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
Address VARCHAR(200),
Type_of_stay VARCHAR(50),
Check_in DATE,
Check_out DATE,
Amount_paid DECIMAL(10,2)
);
CREATE TABLE Time (
Date DATE PRIMARY KEY,
Day_of_week VARCHAR(15),
Day_of_month INT,
Week INT,
Month INT,
Year INT,
Holiday CHAR(1) -- Y/N
);
CREATE TABLE HotelOccupancy (
HotelID INT,
RoomID INT,
CustomerID INT,
Date DATE,
No_of_occupied_rooms INT,
No_of_vacant_rooms INT,
Revenue DECIMAL(10,2),
FOREIGN KEY (HotelID) REFERENCES Hotel(HotelID),
FOREIGN KEY (RoomID) REFERENCES Room(RoomID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (Date) REFERENCES Time(Date)
);
INSERT INTO City VALUES (1, 'West', 'Maharashtra', 'India', '400708');
INSERT INTO City VALUES (2, 'South', 'Karnataka', 'India', '560001');
INSERT INTO Hotel VALUES (1, 'Sea View Hotel', 100, 5, 'Mumbai', 'Maharashtra',
'India', 1);
INSERT INTO Hotel VALUES (2, 'Hill Top Resort', 50, 4, 'Bangalore', 'Karnataka',
'India', 2);
INSERT INTO Room VALUES (101, 'Deluxe', 2, 1, 'Sea Facing', 'Y', 2019);
INSERT INTO Room VALUES (102, 'Standard', 4, 2, 'Garden Side', 'N', 2017);
INSERT INTO Customer VALUES (1, 'John Doe', '123 Marine Drive, Mumbai',
'Vacation', '2025-07-20', '2025-07-25', 25000.00);
INSERT INTO Customer VALUES (2, 'Priya Sharma', 'MG Road, Bangalore',
'Business', '2025-07-19', '2025-07-22', 18000.00);
INSERT INTO Time VALUES ('2025-07-20', 'Sunday', 20, 29, 7, 2025, 'N');
INSERT INTO Time VALUES ('2025-07-21', 'Monday', 21, 30, 7, 2025, 'Y');
INSERT INTO HotelOccupancy VALUES (1, 101, 1, '2025-07-20', 80, 20, 500000.00);
INSERT INTO HotelOccupancy VALUES (2, 102, 2, '2025-07-21', 40, 10, 200000.00);
SELECT * FROM City;
SELECT * FROM Hotel;
SELECT * FROM Room;
SELECT * FROM Customer;
SELECT * FROM Time;
SELECT * FROM HotelOccupancy;
OLAP OPERATIONS:
1) SLICE: Get all occupancy details for a specific date
SELECT * FROM HotelOccupancy
WHERE Date = '2025-07-20';
2) DICE: Get occupancy for hotels with StarRating >= 4 and region 'Mumbai'
SELECT HO.*, H.HotelName, H.StarRating
FROM HotelOccupancy HO
JOIN Hotel H ON HO.HotelID = H.HotelID
WHERE H.StarRating >= 4 AND H.Region = 'Mumbai';
3) ROLL UP: Total Revenue by Month
SELECT T.Year, T.Month, SUM(HO.Revenue) AS Total_Revenue
FROM HotelOccupancy HO
JOIN Time T ON HO.Date = T.Date
GROUP BY T.Year, T.Month
ORDER BY T.Year, T.Month;
4) DRILL DOWN: Revenue by Year then drill down to Month
SELECT T.Year, SUM(HO.Revenue) AS Total_Revenue
FROM HotelOccupancy HO
JOIN Time T ON HO.Date = T.Date
GROUP BY T.Year;
SELECT T.Year, T.Month, SUM(HO.Revenue) AS Total_Revenue
FROM HotelOccupancy HO
JOIN Time T ON HO.Date = T.Date
GROUP BY T.Year, T.Month;
5) PIVOT: Revenue by Hotel for each Month
SELECT H.HotelName,
SUM(CASE WHEN T.Month = 7 THEN HO.Revenue ELSE 0 END) AS July_Revenue
FROM HotelOccupancy HO
JOIN Hotel H ON HO.HotelID = H.HotelID
JOIN Time T ON HO.Date = T.Date
GROUP BY H.HotelName;
