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.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
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;
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
