SECTION A

Using Scalar Variables

Create a PL/SQL block containing the following variables:

NAME DATA TYPE OPTION INITIAL VALUE

lv_test_date DATE December 10, 2012

lv_test_num NUMBER (3) CONSTANT 10

lv_test_txt VARCHAR2 (10)

Assign your last name as the value of the text variable in the executable section of the block. Include statements in the block to display each variable’s value onscreen.

CREATING A FLOWCHART

The brewbean’s application needs a block that determines whether a customer is rated high, mid, or low based on his or her total purchases. The block needs to determine the rating and then display the results on screen. The code rates the customer high if total purchase are greater than $200, mid if greater than $100, and low if $100 or lower. Develop a flowchart to outline the conditional processing steps needed for this block

USING IF STATEMENTS

Create a block using an IF statement to perform the actions described in (2) above. Use a scalar variable for the total purchase amount, and initialize this variable to different values to test your block.

USING CASE STATEMENTS

Create a block using a CASE statement to perform the actions described in (3) above. Use a scalar variable for the total purchase amount, and initialize this variable to different values to test your block.

USING A BOOLEAN VARIABLE

Brewbean’s needs program code to indicate whether an amount is still due on an account when a payment is received. Create a PL/SQL block using a Boolean variable to indicate whether an amount is still due. Declare and initialize two variables to provide input for the account balance and payment amount received. A TRUE Boolean value should indicate an amount is still owed, and FALSE value should indicate the account is paid in full. Use output statements to confirm that the Boolean variable is working correctly.

USING LOOPING STATEMENTS

Create a block using a loop that determines the number of items that can be purchased based on the item prices and the total available to spend. Include one initialized variable to represent the price and another to represent the total available to spend. (You could solve it with division, but you need to practice using loop structures.). The block should include statements to display the total number of items that can be purchased and the total amount spent.

CREATING A FLOWCHART

Brewbean’s determine shipping costs based on the number of items ordered and club membership status. The applicable rates are shown in the following chart. Develop a flowchart to outline the condition-processing steps needed to handle this calculation.

QUANTITY OF ITEMS NONMEMBER SHIPPING COST MEMBER SHIPPING COST

Up to 3 $5.00 $3.00

4-6 $7.50 $5.00

7-10 $10.00 $7.00

More than 10 $12.00 $9.00

USING IF STATEMENTS

Create a block to accomplish the task outlined in (7) above. Include a variable containing a Y or N to indicate membership status and a variable to represent the number of items purchased. Test with a variety of values.

(75% of mark)

SECTION B

Create a single row subqueries using the following operators =, <, >, >=, <=, <>, using a WHERE clause, HAVING clause.

Create a multiple row subqueries using IN, ANY, ALL, EXISTS operator using a WHERE clause, HAVING clause.

Create a nested subqueries using two or more tables in the database.

(25% of mark)

Upload your file to the drop box.

This assignment is due on Wed. Oct 18th 2017 at 10 pm.