Complete the code using HINTs in comments 1. USE ForestGlenInn; DROP FUNCTION IF

Question

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 */
   <your code goes here>
  
   /* 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).*/
   <your code goes here>
  
   /* Don't forget to return the value! */
<your code goes here>
  
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 */
   <your code goes here>
  
/* calculate the VIP status */
/* HINT: use a sequence of IF/ELSEIF statements to assign the VIP_status_var based on num_stays */
   <your code goes here>

   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 */
   <your code goes here>
  
   IF sql_error = FALSE THEN
       COMMIT;
   ELSE
       ROLLBACK;
   END IF;
END//

DELIMITER ;

Explanation / Answer

// Completed Code

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 */

SELECT price FROM room_type into base_price_var WHERE room_type_code = room_type_code_param;

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

price_var = base_price_var;

/*If the length of stay is greater than 3 days, apply a 10% discount to the base price. */

IF length_of_stay_param >=3 THEN

price_var = price_var - ( base_price_var * 10 / 100 );

ENDIF;

/* Don't forget to return the value! */

/*Return a value that represents the price for the full reservation: multiply the (possibly discounted) base price by the length of stay.*/

price_var = price_var * length_of_stay_param;

RETURN price_var;

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 */

SELECT num_stays INTO num_stays_var FROM reservations WHERE guest_id=guest_id_param;

/* calculate the VIP status */

/* HINT: use a sequence of IF/ELSEIF statements to assign the VIP_status_var based on num_stays */

IF num_stays_var > 3 THEN

VIP_status_var := 'GOLD';

ELSIF num_stays_var == 3 THEN

VIP_status_var := 'SILVER';

ELSIF num_stays_var == 2 THEN

VIP_status_var := 'BRONZE';

/*Default is 'Not a VIP', is assigned already*/

END IF;   

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 */

UPDATE guests SET VIP_status = VIP_status_var WHERE guest_id = guest_id_param;

IF sql_error = FALSE THEN

COMMIT;

ELSE

ROLLBACK;

END IF;

END//

DELIMITER ;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.