SunCrest Hotels & Resorts Database Project: ER Diagram, Normalisation and MySQL Sample

SELECT h.HotelName,
       SUM(p.Amount) AS TotalRevenue
FROM HOTEL h
JOIN ROOM r          ON r.HotelID = h.HotelID
JOIN RESERVATION res ON res.RoomID = r.RoomID
JOIN PAYMENT p       ON p.ReservationID = res.ReservationID
GROUP BY h.HotelID, h.HotelName
ORDER BY TotalRevenue DESC;

SELECT CONCAT(g.FirstName, ' ', g.LastName) AS FullName,
       g.Email
FROM GUEST g
WHERE NOT EXISTS (
  SELECT 1
  FROM RESERVATION r
  WHERE r.GuestID = g.GuestID
);

CREATE VIEW vw_hotel_revenue_summary AS
SELECT h.HotelID,
       h.HotelName,
       COUNT(DISTINCT res.ReservationID) AS TotalReservations,
       COALESCE(SUM(p.Amount), 0)        AS TotalRevenue,
       ROUND(AVG(p.Amount), 2)           AS AvgPaymentAmount
FROM HOTEL h
JOIN ROOM r          ON r.HotelID = h.HotelID
JOIN RESERVATION res ON res.RoomID = r.RoomID
LEFT JOIN PAYMENT p  ON p.ReservationID = res.ReservationID
GROUP BY h.HotelID, h.HotelName;

SELECT HotelName, TotalReservations, TotalRevenue
FROM vw_hotel_revenue_summary
WHERE TotalRevenue > (
  SELECT AVG(TotalRevenue)
  FROM vw_hotel_revenue_summary
)
ORDER BY TotalRevenue DESC;

SELECT CONCAT(g.FirstName, ' ', g.LastName) AS GuestName,
       g.State,
       SUM(p.Amount) AS TotalSpending
FROM GUEST g
JOIN RESERVATION r ON r.GuestID = g.GuestID
JOIN PAYMENT p     ON p.ReservationID = r.ReservationID
GROUP BY g.GuestID, g.FirstName, g.LastName, g.State
HAVING SUM(p.Amount) > (
  -- correlated: average spending of paying guests in the SAME state
  SELECT SUM(p2.Amount) / COUNT(DISTINCT g2.GuestID)
  FROM GUEST g2
  JOIN RESERVATION r2 ON r2.GuestID = g2.GuestID
  JOIN PAYMENT p2     ON p2.ReservationID = r2.ReservationID
  WHERE g2.State = g.State
)
ORDER BY g.State, TotalSpending DESC;

DELIMITER $$
CREATE FUNCTION fn_nights_stayed(p_reservation_id INT)
RETURNS INT
READS SQL DATA
BEGIN
  DECLARE v_nights INT;

  SELECT DATEDIFF(CheckOutDate, CheckInDate)
    INTO v_nights
  FROM RESERVATION
  WHERE ReservationID = p_reservation_id;

  RETURN v_nights;
END$$
DELIMITER ;

SELECT r.ReservationID,
       fn_nights_stayed(r.ReservationID) AS NightsStayed
FROM RESERVATION r
JOIN ROOM rm ON rm.RoomID = r.RoomID
WHERE r.Status = 'Checked-Out'
  AND rm.HotelID = 2;

DELIMITER $$
CREATE PROCEDURE sp_apply_loyalty_upgrades(IN p_threshold DECIMAL(10,2))
BEGIN
  DECLARE v_done      INT DEFAULT 0;
  DECLARE v_guest_id  INT;
  DECLARE v_tier      VARCHAR(10);
  DECLARE v_spending  DECIMAL(12,2);
  DECLARE v_next_tier VARCHAR(10);

  -- (1) cursor: each guest, current tier and lifetime spending
  DECLARE cur_guests CURSOR FOR
    SELECT g.GuestID, g.LoyaltyTier, COALESCE(SUM(p.Amount), 0)
    FROM GUEST g
    LEFT JOIN RESERVATION r ON r.GuestID = g.GuestID
    LEFT JOIN PAYMENT p     ON p.ReservationID = r.ReservationID
    GROUP BY g.GuestID, g.LoyaltyTier;

  -- (2) handler: set the done flag when the cursor runs out of rows
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

  OPEN cur_guests;

  -- (3) loop through every guest
  guest_loop: LOOP
    FETCH cur_guests INTO v_guest_id, v_tier, v_spending;
    IF v_done = 1 THEN
      LEAVE guest_loop;
    END IF;

    -- (4) upgrade exactly one level, never downgrade, skip Platinum
    IF v_spending >= p_threshold AND v_tier <> 'Platinum' THEN
      SET v_next_tier = CASE v_tier
                          WHEN 'None'   THEN 'Silver'
                          WHEN 'Silver' THEN 'Gold'
                          WHEN 'Gold'   THEN 'Platinum'
                        END;

      UPDATE GUEST
      SET LoyaltyTier = v_next_tier
      WHERE GuestID = v_guest_id;
    END IF;
  END LOOP guest_loop;

  -- (5) close the cursor
  CLOSE cur_guests;
END$$
DELIMITER ;

CALL sp_apply_loyalty_upgrades(1500.00);

CREATE TABLE AUDIT_LOG (
  LogID         INT AUTO_INCREMENT PRIMARY KEY,
  PaymentID     INT,
  ReservationID INT,
  Amount        DECIMAL(10,2),
  LoggedAt      DATETIME
);

CREATE TRIGGER trg_payment_audit
AFTER INSERT ON PAYMENT
FOR EACH ROW
  INSERT INTO AUDIT_LOG (PaymentID, ReservationID, Amount, LoggedAt)
  VALUES (NEW.PaymentID, NEW.ReservationID, NEW.Amount, NOW());

-- Test: add a payment against a cancelled reservation (it has no payment yet)
INSERT INTO PAYMENT (PaymentID, ReservationID, PaymentDate, Amount, PaymentMethod)
SELECT 9001, ReservationID, CURDATE(), 150.00, 'Credit Card'
FROM RESERVATION
WHERE Status = 'Cancelled'
ORDER BY ReservationID
LIMIT 1;

SELECT * FROM AUDIT_LOG;