Assignment Task -
Write PL/SQL procedures for the following:
1. Show available rooms by type: Given a hotel ID, display the count of all available rooms by room type.
2. Room Checkout Report: Input: ReservationID Output:
- Guest name
- Room number, rate per day and possibly multiple rooms (if someone reserved several rooms)
- Services rendered per date, type, and amount
- Discounts applied (if any)
- Total amount to be paid
3. Income By State Report: Input is state. Print total income from all sources of all hotels by room type and service type in the given state. Include discounts.
Requirements
If a hotel is booked in the months of May-August, the charge is $300. Otherwise, it is $200. All room types are charged the same rate. The rate is based on the first date of check-in, and is the same throughout the guests stay.
If the guest reserves a room 2 months in advance or more, they get a 10% discount
Guests can check in without a reservation
The services that are offered by all hotels are the same.
They include:
a. Restaurant services (assume $20 per person per meal)
b. Pay-per-view movies (assume $5 per movie)
c. Laundry services (assume $10 per time - regardless of number of items).