Reference no: EM132385496
IS480 - Advanced Database Management
Fall 2019 Term - Session 01 (#1791) - Individual Assignment
California State University, Long Beach - USA
Notes: For each procedure, turn in the following:
- Turn spool on, then run the procedure with several values. Turn spool off.
- Turn in a copy of the code
- Turn in a copy/results of the spool file (showing the output).
Briefly answer the following:
1. Your local supermarket is having a "buy 3 get 1 free" promotion. That is, for every three units you buy, you get the fourth for free. Write a procedure with one input parameter indicating the number of units in your cart. Output is the number of units you need to pay for.
Hint: For every 4 units in your cart, you pay for 3. If you have items in your cart that do not form a group of 4, you pay for each item (e.g. 1, 2, or 3 units).
2. Your local supermarket is having a "buy x get 1 free" promotion. Write a procedure with two input parameters: "buy x" (e.g. p_x) - The "x" in the promotion and the number of items in your cart (p_noitems). Output is the number of units you need to pay for.
3. Your local supermarket is having a "buy x get y free" promotion. Write a procedure with two input parameters: "buy x" (e.g. p_x) - the "x" in the promotion, "get y" (e.g. p_y) - the "y" in the promotion, and the number of items in your cart (p_noitems). Output is the number of units you need to pay for.
4. Write a procedure to split a string at the first occurrence of a character. The procedure will accept two input parameters: a string and a character. Declare two variables v_before and v_after. Assign v_before the portion of the string before given character, and v_after the portion of the string after given character (including the character). Assume the user enters a valid single character for the split. Display v_before and v_after.
5. Write a procedure that will remove the first occurrence of a character in a string. Input two parameters: string and character. Output the string without the character.
6. Enhance the above to remove multiple characters (contiguous).
7. Write a procedure to replace the first occurrence of a character in a string with a new character. Input three parameters: string, original character, and replacement character. Output the new string with the character replaced.
Note: Do not use the Oracle Replace function.
8. Enhance the above to replace multiple characters (contiguous).
9. Review the following program
BEGIN
IF ConditionA THEN
actionA1;
actionA2;
ELSE
action X;
IF ConditionB THEN
actionB1;
ELSE
actionB2;
END IF;
actionY;
END IF;
END;
/
a. If condition A is true and condition B is true, which action(s) would be executed?
b. If condition A is not true and condition B is true, which action(s) would be executed?
c. If condition A is not true and condition B is not true, which action(s) would be executed?
10. Review the following code
x := 5;
IF x >= 5 THEN
x := x * 2;
ELSIF x >= 10 THEN
x := x * 3;
ELSE
x := x * 4;
END IF;
a. What is the value of x at the end of the program?
11. Review the following code
x := 5;
IF x >= 5 THEN
x := x * 2;
IF x >= 10 THEN
x := x * 3;
END IF;
ELSE
x := x * 4;
END IF;
a. What is the value of x at the end of the program?
12. Review the following code
x := 5;
IF x >= 5 THEN
x := x * 2;
IF x > 10 THEN
x := x * 3;
END IF;
ELSE
x := x * 4;
END IF;
a. What is the value of x at the end of the program?
13. Review the following code
x := 5;
IF x > 5 THEN
x := x * 2;
IF x > 2 THEN
x := x * 3;
END IF;
ELSE
x := x * 4;
END IF;
a. What is the value of x at the end of the program?
14. Create a procedure to determine a student's status: StudentStatus(p_UnitsCompleted). The input is the number of units completed. Output is student status based on the following:
0 <= units <= 30 → Freshmen
30 < units <= 60 → Sophomore
60 < units <= 90 → Junior
90 < units → Senior
If a negative number is entered, output an invalid entry message.
15. Create a procedure to calculate change with the fewest number of coins. The input is a purchase price of a dollar or less. Assume that you are paid with a dollar bill. Output the amount of change due. Display the change using the fewest number of pennies, nickels, dimes, and quarters.
16. What is the output of the following program?
j := 0;
FOR i IN 1..10 LOOP
IF i >= 3 THEN
EXIT;
DBMS_OUTPUT.PUT_LINE(j);
END IF;
j := i;
END LOOP;
17. What is the output of the following program?
j := 0;
FOR i IN 1..10 LOOP
IF i >= 3 THEN
DBMS_OUTPUT.PUT_LINE(j);
EXIT;
END IF;
j := i;
END LOOP;
18. What is the output of the following program?
j := 0;
FOR i IN 1..10 LOOP
IF i >= 3 THEN
DBMS_OUTPUT.PUT_LINE(j);
EXIT;
END IF;
j := i + j;
END LOOP;
19. Write a procedure to produce a multiplication table to a given base: PrintTable(p_base). Input is a base number and output is the multiplication table for the base times the integers from 1 to 10. See example in attached file.
20. Write a procedure to print the entire multiplication table per the below (100 rows).
Note: DO NOT turn in the output of this program, code only.
1 x 1 = 1
1 x 2 = 2
1 x 3 = 3
1 x 4 = 4
1 x 5 = 5
1 x 6 = 6
1 x 7 = 7
1 x 8 = 8
1 x 9 = 9
1 x 10 = 10
2 x 1 = 2
2 x 2 = 4
2 x 3 = 6
...
...
10 x 8 = 80
10 x 9 = 90
10 x 10 = 100
21. Enhance the RemoveChar procedure created earlier. Create a RemoveCharAll procedure that will remove all occurrences of a character in a string. Input two parameters: string and character. Output the string with the character removed.
22. Enhance the ReplaceChar procedure created earlier. Create a ReplaceCharAll procedure that will replace all occurrences of a character in a string with a new character. Input three parameters: string, original character, and replacement character. Output the new string with the character replaced.
Note: Do not use the Oracle Replace function.
Attachment:- Advanced Database Management Assignment File.rar