Complete the code using HINTs in comments

1.

USE ForestGlenInn;

DROP FUNCTION IF EXISTS CalcRoomPrice;

DELIMITER //

/* Calculate the room price using the room type and the length of stay: - Determine the base price using the room type. - If the length of stay is greater than 3 days, apply a 10% discount to the base price. - Return a value that represents the price for the full reservation: multiply the (possibly discounted) base price by the length of stay. */ CREATE FUNCTION CalcRoomPrice ( /* two input parameters for room type_code, number of days */ room_type_code_param VARCHAR(10), length_of_stay_param INT ) RETURNS DECIMAL(5,2) BEGIN DECLARE base_price_var DECIMAL(5,2); DECLARE price_var INT; SET price_var = 0; /* query the database to get the base price of the room type */ /* HINT: write a SELECT statement which queries the room_type table using the room type parameter. Use the INTO keyword to store the price into the local variable base_price_var declared above */ /* Calculate the full price of the person's reservation using the base price and the length of stay */ /* HINT: first SET the price_var to base price, then use an IF statement to conditionally apply the discount. Then multiply by the number of days (length of stay).*/ /* Don't forget to return the value! */ END//

DELIMITER ;

2.

USE ForestGlenInn;

DROP PROCEDURE IF EXISTS Set_VIP_status;

DELIMITER // /* ForestGlenInn has a VIP progam. Set the status for a given guest, based on number of reservations - If the guest has stayed more than 3 times, they get GOLD status - If the guest has stayed 3 times, they get SILVER status - If the guest has stayed twice, they get a BRONZE status - If they have not stayed or have only stayed once, they are not a VIP */ CREATE PROCEDURE Set_VIP_status ( /* one input parameters for guest ID */ guest_id_param INT ) BEGIN DECLARE sql_error INT DEFAULT FALSE; DECLARE num_stays_var INT; DECLARE VIP_status_var VARCHAR(10); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error = TRUE; /* Default to 'Not a VIP' */ SET VIP_status_var = 'Not a VIP'; /* calculate the number of reservations */ /* HINT: write a SELECT statement which queries the reservations table using the guest_id parameter. Use the INTO keyword to store the number of stays into the local variable num_stays_var declared above */ /* calculate the VIP status */ /* HINT: use a sequence of IF/ELSEIF statements to assign the VIP_status_var based on num_stays */

START TRANSACTION;

/* update the VIP status for this guest */ /* HINT: write an UPDATE statement against the guests table using VIP_status_var and guest_id_param */ IF sql_error = FALSE THEN COMMIT; ELSE ROLLBACK; END IF; END//

DELIMITER ;