Complete the code using HINTs in comments 1. USE ForestGlenInn; DROP FUNCTION IF
ID: 3713994 • Letter: C
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 ;