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).