[Solved]Need Help Cal Room Price Function Calculate Room Price Using Room Type Check Date Check Da Q37151920

I need help with cal room price function.

/* Calculate the room price using the room type, check-in date,and check-out date:
   – Determine the base price using the room type.
   – If the length of stay is greater than 5 days, applya 15% discount to the base price.
– 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 thefull reservation: multiply the (possibly discounted) base price bythe length of stay.
*/

USE ForestGlenInn;
DROP FUNCTION IF EXISTS CalcRoomPrice;

DELIMITER //

CREATE FUNCTION CalcRoomPrice(
       /* three input parameters for roomtype_code, check-in date, and check-out date */
       room_type_code_paramVARCHAR(10),
       checkin_date_param DATE,
       checkout_date_param DATE
   )
RETURNS DECIMAL(5,2)

BEGIN
   DECLARE base_price_var DECIMAL(6,2);
   DECLARE price_var INT;
  
   SET price_var = 0;
  
   /* create a variable that contains the calculatedvalue of the length_of_stay */
SELECT DATEDIFF(checkin_date_param, checkout_date_param)
INTO length_of_stay;
  
   /* query the database to get the base price of theroom type */
/* HINT: write a SELECT statement which queries the room_type tableusing the room type parameter.
       Use the INTO keyword to store theprice into the local variable base_price_var declared above*/
   SELECT base_price FROM room_types INTObase_price_var
  
   /* Calculate the full price of the person’sreservation using the base price and the calculated length of stay*/
/* HINT: first SET the price_var to base price, then use an IFstatement to conditionally apply the discount. Then multiply by thelength of stay.*/
SET price_var= base_price
IF length_of_stay>5 THEN price_var=(price_var-(price_var *(15/100))* length_of_stay
  
ELSEIF length_of_stay>3 THEN price_var=(price_var-(price_var *(10/100)) * length_of_stay
  
  
   /* Don’t forget to return the value! */
RETURN(price_var);
END

DELIMITER ;

MANAGEMENT 1A4-Q3 partial-code* x createFunction A4-Q4 partial-code x Server Status Client Connections Users and Privileges Status and System Variables Caleu a enne ebase pricea einhthe5days apply a 10% discounttoth the room price using the room type, n date eck-out date Determine the base price using the room type If the length of stay is greater than 5 days, apply a 15% discount to the base price. 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. base ossibly discounted, base price by 4. 5 6 Data Export Data Import/Restore 8USE ForestGleninn 9· DROP FUNCTION IF EXISTS CalcRoomPrice: INSTANCE B Startup / Shutdown DELIMITER Server Logs options File 12 13 CREATE FUNCTION CalcRoomPrice three input parameters for room type_ code, check-in date, and check-out date room type code_param VARCHAR(10) checkin date param DATE checkout date param DATE PERFORMANCE RETUANS DECIMAL6.2) Performance Reports Performance Schema Setup BEGIN DECLARE base price var DECIMAL(6,2); DECLARE price var INT: SET price var = 0; SCHEMAS 23 24 Q Filter objects 26 27 28 create a variable that contains the calculated value of the length_of stay SELECT DATEDIFF(checkin_date param, checkout date param) INTO length of_stay Indexes Foreign Keys Triggers 30 the database to get the base price of the room type 32 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 base price FROM room types INTO base price var 34 35 36 37 rooms Calculate the full price of the HINT: first SET the price var to base price, then use an IF statement to conditionally apply the discount. Then multiply by the length of stay. s reservation using the base price and the calculated length of stay SET price_var base_price IF length of_stay>5 THEN price var-(price_var-(price var(15/100) length of stay room-id ◆ room_number ELSEIF length of stay>3 THEN price_var-(price_var-(price_var (10/100)) length of stay room type id Indexes Foreign Keys Triggers 42 Don’t forget to return the value! RETURN(price_var); 46 47 END 1002DELIMITER: room types 3-18 Columns Action Output room type_id Action room_type_code room type_name room type deso 39 22:41:29 SELECT CONCAT(first-name,’ last-name) AS Guest-Name, DATE-FORMAT (check-in-… 18 row(s) returned 40 22:42:44 SELECT CONCAT (first name,’ “last name) AS Guest Name, DATE FORMAT(check in18 row(s) returned 41 22:43:42 SELECT CONCAT(first-name,’ last-name) AS Guest-Name, DATE-FORMAT(check-in-… 8 row(s) returned 4223:10:20 USE ForestGleninn 0 row(s) affected base-price ◆ Show transcribed image text MANAGEMENT 1A4-Q3 partial-code* x createFunction A4-Q4 partial-code x Server Status Client Connections Users and Privileges Status and System Variables Caleu a enne ebase pricea einhthe5days apply a 10% discounttoth the room price using the room type, n date eck-out date Determine the base price using the room type If the length of stay is greater than 5 days, apply a 15% discount to the base price. 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. base ossibly discounted, base price by 4. 5 6 Data Export Data Import/Restore 8USE ForestGleninn 9· DROP FUNCTION IF EXISTS CalcRoomPrice: INSTANCE B Startup / Shutdown DELIMITER Server Logs options File 12 13 CREATE FUNCTION CalcRoomPrice three input parameters for room type_ code, check-in date, and check-out date room type code_param VARCHAR(10) checkin date param DATE checkout date param DATE PERFORMANCE RETUANS DECIMAL6.2) Performance Reports Performance Schema Setup BEGIN DECLARE base price var DECIMAL(6,2); DECLARE price var INT: SET price var = 0; SCHEMAS 23 24 Q Filter objects 26 27 28 create a variable that contains the calculated value of the length_of stay SELECT DATEDIFF(checkin_date param, checkout date param) INTO length of_stay Indexes Foreign Keys Triggers 30 the database to get the base price of the room type 32 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 base price FROM room types INTO base price var 34 35 36 37 rooms Calculate the full price of the HINT: first SET the price var to base price, then use an IF statement to conditionally apply the discount. Then multiply by the length of stay. s reservation using the base price and the calculated length of stay SET price_var base_price IF length of_stay>5 THEN price var-(price_var-(price var(15/100) length of stay room-id ◆ room_number ELSEIF length of stay>3 THEN price_var-(price_var-(price_var (10/100)) length of stay room type id Indexes Foreign Keys Triggers 42 Don’t forget to return the value! RETURN(price_var); 46 47 END 1002DELIMITER: room types 3-18 Columns Action Output room type_id Action room_type_code room type_name room type deso 39 22:41:29 SELECT CONCAT(first-name,’ last-name) AS Guest-Name, DATE-FORMAT (check-in-… 18 row(s) returned 40 22:42:44 SELECT CONCAT (first name,’ “last name) AS Guest Name, DATE FORMAT(check in18 row(s) returned 41 22:43:42 SELECT CONCAT(first-name,’ last-name) AS Guest-Name, DATE-FORMAT(check-in-… 8 row(s) returned 4223:10:20 USE ForestGleninn 0 row(s) affected base-price ◆

Expert Answer

---

Answer to I need help with cal room price function. /* Calculate the room price using the room type, check-in date, and check-out … . . .