Hello Guys,

I am working on hotel management project in SQL.

Change a reservationDate: Input the reservation ID and change reservation start and end date, if there is availability in the same room type for the new date interval

here is my code for this part:

Create or Replace procedure ChangeReservationDate (r_id number, r_sdate varchar2 , r_edate varchar2 , h_id number) as cursor c1 is select res_check_in, res_check_out, room_type from reservation where hotel_id = h_id; c1_row c1%rowtype; r_roomtype varchar2(100); r_count number; begin r_count:=0; select room_type into r_roomtype from reservation where res_id = r_id; open c1; loop fetch c1 into c1_row; exit when c1%notfound; if c1_row.Room_type = r_roomtype and r_sdate between c1_row.res_check_in and c1_row.res_check_out and r_edate between c1_row.res_check_in and c1_row.res_check_out then r_count :=r_count+ 1; end if; end loop; close c1; if r_count >= 5 then dbms_output.put_line ('All rooms reserved'); else update reservation set res_check_in = r_sdate , res_check_out = r_edate; dbms_output.put_line ('New start date ' || r_sdate || ' ' || 'New end date ' || r_edate); end if; end;

I needed some help with the rest of these. Any help is welcomed, I am stuck thanks!

Change a reservationRoomType: Input the reservation ID and change reservation room type if there is availability for that room type during the reservations date interval

Show single hotel reservations: Given a hotel ID, show all reservations for that hotel

Total Monthly Income Report: Calculate and display total income from all sources of all hotels. Totals must be printed by month, and for each month by room type, service type. Include discounts.

IS420 TEAM2.RESERVATION IS420 TEAM2.ROOM P RES_ID F CUST ID F ROOM NUMBER NUMBER,0) NUMBER,0) NUMBER C,0) NUMBER,0) NUMBER (1) VARCHAR2 (255 BYTE) P ROOM_ NUMBER NUMBER C,0) F HOTEL_ID NUMBER,0) VARCHAR2 (255 BYTE) ROOM_TYPE MS ID IS_CANCELLED ROOM TYPE DATE OF RESERVATION DATE RES_CHECK_IN RES_CHECKOUT NUMBER_OF_GUESTS HOTEL ID ROOM STATUS VARCHAR2 (255 BYTE) ROOM NUMBER_PK (ROOM NUMBER) ROOM-NUMBER-PK (ROOM-NUMBER) ? DATE DATE NUMBER C,0) NUMBER RES_ID_PK (RES_ID) IS420 TEAM2.HOTE ? RES-D-PK (RES-ID) P HOTEL ID NUMBER,0) VARCHAR2 (255 BYTE) HOTEL_NAME HOTEL ADDRESS VARCHAR2 (255 BYTE) NUM_OF_ROOMS NUMBER,0) IS SOLD HOTEL STATE NUMBER (1) VARCHAR2 (2 BYTE) IS420 TEAM2.CUSTOMER HOTEL_ID_PK (HOTEL_ID) NUMBER 0) VARCHAR2 (255 BYTE) VARCHAR2 (255 BYTE) VARCHAR2 (255 BYTE) VARCHAR2 (255 BYTE) P CUST_ID ? HOTEL-ID-PK (HOTEL-ID) CUST_FIRST_NAME CUST_LAST_NAME CUST_CREDIT_CARD CUST_PHONE_NUM CUST_ID_PK (CUST_ID) CUST-D-PK (CUST-ID) ? IS420 TEAM2.MISC SERVICE IS420 TEAM2.PAYMENT NUMBER 0) NUMBER,0) VARCHAR2 (70 BYTE) VARCHAR2 (1000 BYTE) NUMBER (7.2) DATE NUMBER 0) NUMBER,0) NUMBER (7.2) DATE P MS ID F CUST ID P PAYMENT ID F CUST ID MS TYPE MS DESC MS_PRICE SERVICE_DATE PAYMENT_AMOUNT PAYMENT DATE PAYMENT_DISCOUNT NUMBER (7.2) PAYMENT_ID_PK (PAYMENT_ID) PAYMENT-ID-PK (PAYMENT-ID) MS_ID_PK (MS_ID) ? ? MS-ID-PK (MS-ID)