please use sql to answer the following question an erd has been provide Task 1:  Creating a Logon Procedure    The home page of the Brewbean’s Web site has an option for members to log on with their IDs  and passwords. Develop a procedure named MEMBER_CK_SP that accepts the user ID and password  as inputs, checks whether they match a valid logon, and returns the member first name+lastname  and cookie  value.     The name should be returned as a single text string containing the first and last name.  The head developer wants the number of parameters minimized so that the same  parameter is used to accept the password and return the name value. (passwd Paramater as IN OUT   use passwd as INPUT  firstname||lastname  as OUTPUT)     Also, if the user doesn’t enter a valid username and password, return the value INVALID in a parameter named  p_check.     Test the procedure using a valid logon first, with the username rat55 and password kile. Then try it with an invalid logon by changing the username to rat  and kile999  password.

please use sql to answer the following question an erd has been provide

Task 1:  Creating a Logon Procedure   

The home page of the Brewbean’s Web site has an option for members to log on with their IDs 

and passwords. Develop a procedure named MEMBER_CK_SP that accepts the user ID and password 

as inputs, checks whether they match a valid logon, and returns the member first name+lastname  and cookie 

value.  

The name should be returned as a single text string containing the first and last name. 

The head developer wants the number of parameters minimized so that the same 

parameter is used to accept the password and return the name value. (passwd Paramater as IN OUT   use passwd as INPUT  firstname||lastname  as OUTPUT) 

Also, if the user doesn’t enter a valid username and password, return the value INVALID in a parameter named 

p_check.  

Test the procedure using a valid logon first, with the username rat55 and password kile. Then try it with an invalid logon by changing the username to rat  and kile999  password. 

Hint:  

This procedure will have 4 parameters 

p_userid       IN 

p_passwd    IN OUT 

p_cookie     OUT 

p_check      OUT 

You will select and return two columns/ information from bb_shopper table in this procedure body. 

BEGIN 

   Hint for SELECT statement is like below 

Firstname ||lastname ,   cookie      INTO two local variables  (p_passwd , p_cookie) 

from bb_shoppper     

WHERE username=userid   and  password=passwd   

Assuming your SELECT statement will find record matching username and password  and returns  a records then right after SELECT statement you should have a statement like below 

p_check := ‘VALID USER’; 

Otherwise add Exception to give   ‘Invalid User’  message 

EXCEPTION 

  WHEN NO_DATA_FOUND THEN 

END program; 

Once you are done with developing procedure you can test like below 

Test Cases: 

Valid username scenario 

DECLARE 

  lv_pass_txt VARCHAR2(30):=’kile’; 

  lv_cook_num bb_shopper.cookie%TYPE; 

  lv_chk_txt VARCHAR2(7); 

BEGIN 

  member_ck_sp('rat55',lv_pass_txt,lv_cook_num,lv_chk_txt); 

    DBMS_OUTPUT.PUT_LINE(lv_pass_txt);     -- this will return Kenny Ratman 

    DBMS_OUTPUT.PUT_LINE(lv_cook_num);    -- this will return 0 

    DBMS_OUTPUT.PUT_LINE(lv_chk_txt);     -- this will return VALID USER 

END; 

Invalid username scenario  (username : rat   passwd :kile 

DECLARE 

  lv_pass_txt VARCHAR2(30):=’wrong password’; 

  lv_cook_num bb_shopper.cookie%TYPE; 

  lv_chk_txt VARCHAR2(7); 

BEGIN 

  member_ck_sp('rat',lv_pass_txt,lv_cook_num,lv_chk_txt); 

    DBMS_OUTPUT.PUT_LINE(lv_pass_txt); -- returns. wrong password (because first name and last name is not selected 

    DBMS_OUTPUT.PUT_LINE(lv_cook_num);    --NULL result will come 

    DBMS_OUTPUT.PUT_LINE(lv_chk_txt);     -- Invalid User  

END;