Question 1)

Use Brewbean database for this question. Script to build sample DB is given in the same Mid Term folder

Working with IF Statements

Brewbeans calculates shipping cost based on the quantity of items in an order. Assume the

quantity column in the BB_BASKET table contains the total number of items in a basket.

A block is needed to check the quantity provided by an initialized variable and determine the

shipping cost. Display the calculated shipping cost onscreen.

Test using the basket IDs 5 and 12, and apply the shipping rates listed in Table 3-3.

Question 2)

Work on Human Resources (HR_XXX tables)

Create a stored function to find out number of months of service for a specific employee.

Function name is CALCULATE_MONTHS_SERVICE to return the total number of months of service for employee. Function will accept employee_id as input and return number of month of service. Add error handling to account for an invalid emoployee ID in case this employee doesnt exists or can not be found.

Instructions :

In your function you need to find total number of month in Service. To find that you need to find all the years for that employee in historical jobs (job_history table) and plus add his/her current employment duration to the calculation to find overall years in service (from employees table)

Step1:Use Cursor to get all the records from job_history for that particular employee_id and then count years .

Step2: Then later on add numbers of years value from Employee table to first calculation

In each case you can use this formula with proper WHERE condition with employee_id

SELECT MONTHS_BETWEEN (end_date, start_date) INTO v_historic_mont FROM job_history;

+

SELECT MONTHS_BETWEEN (SYSDATE, hire_date) INTO v_current_mont FROM employees;

Also add EXCEPTION section for no data found situations

Question 3:

Use Brewbean (BB_XXX) database for this question.

Processing Database Data with IF Statements

The Brewbeans application needs a block to determine whether a customer is rated HIGH,

MID, or LOW based on his or her total purchases. The block needs to select the total amount of

orders for a specified customer, determine the rating, and then display the results onscreen. The

code rates the customer HIGH if total purchases are greater than $200, MID if greater than

$100, and LOW if $100 or lower. Use an initialized variable to provide the shopper ID.

Here is the partial code given to you. This is your start point. You can develop this as PL/SQL code but Professor prefer you to convert to FUNCTION

DECLARE

lv_total_num NUMBER(6,2);

.

..

BEGIN

SELECT SUM(total)

FROM bb_basket

WHERE idShopper =

AND orderplaced = 1

GROUP BY idshopper;

IF lv_total_num > 200 THEN

END IF;

DBMS_OUTPUT.PUT_LINE(.);

END;

1.Edit the block to perform the required task. Add IF THEN ELSE section

2. Run the block and verify the results. Enter and run the following SQL query to confirm that

the total for this shopper is indeed greater than $200:

SELECT SUM(total)

FROM bb_basket

WHERE idShopper = 22

AND orderplaced = 1

GROUP BY idshopper;

TABLE 3-3 Shipping Charges WHEN NO_DATA_FOUND THEN RAISE_APPLI- CATION_ERROR (-20348