Query Description 1. Change to use the database airline 1. Create stored procedure payDeposit to do...

 

Transcribed Image Text:

Query Description 1. Change to use the database airline 1. Create stored procedure payDeposit to do the following a. Set the delimiter to $$ b. Parameter list includes 3 i. IN employeeNum INT ii. INOUT deposit VARCHAR(4000) c. Declare the following variables i. v_finished integer DEFAULT 0 ii. v_lines varchar(100) DEFAULT" iii. v_empFirst varchar(100) DEFAULT"" iv. v_empLast varchar(100) DEFAULT"" v. v_empAddress varchar(100) DEFAULT vi. v_empCity varchar(100) DEFAULT"""; vii. v_empState varchar(100) DEFAULT"" viii. v_empZip varchar(100) DEFAULT"" ix. v tHours int DEFAULT 0 X. v_pHourly decimal(5,2) DEFAULT 0.0 xi. v overtime INT DEFAULT 0 xii. v_count integer DEFAULT 0 xiii. v_deposit decimal(8,2) DEFAULT 0.0 "" d. Declare cursor employee_cursor for the following join query i. Select 1. firstName from table employee 2. lastName from table employee 3. address from table employee 4. city from table cityState 5. state from table cityState 6. zipCode from table cityState 7. Sum columns sun, mon, tues, wed, thurs, fri, and sat from table timesheet as hours 8. hourly from table position ii. Where column emploveeld in table timesheet equals the value in e. ii. Where column employeeld in table timesheet equals the value in parameter employee Num Declare continue exception handler for not found set variable v_finished equal to 1 f. Open cursor employee_cursor g. Loop through the employee_cursor, using loop label get_employee i. Fetch employee_cursor into variables 1. v_empFirst 2. v_empLast 3. v_empAddress 4. v_empCity 5. v_empState 6. v_empZip 7. v tHours 8. v_pHourly ii. Check if variable v_finished equals 1; if true, leave the loop using the loop label get_employee iii. Set v_count equal to v_count plus 1 (one) 4 iv. If the variable v_count is equal to 1 (one) 1. Calculate the employee's deposit check using an IF/ELSEIF decision-making construct based on the following business logic a. If the employee's hours (i.e., v_tHours) are less than or equal to 40 then i. set v_deposit equal to the hours multiplied by the hourly rate b. Else If the employee's hours (i.e., v_tHours) are greater than 40 then i. set v_overtime equal to (v_ tHours minus 40) multiplied by (v_pHourly * 1.5) ii. set v_deposit equal to the employee's 40 hours multiplied by their hourly rate (i.e., v_pHourly) iii. set v_deposit equal to v_deposit plus v_overtime 2. Set INOUT parameter deposit equal to concatenated a. deposit 2. Set INOUT parameter deposit equal to concatenated a. deposit b. '\From: ' 3. Set INOUT parameter deposit equal to concatenated a. deposit b. CGS 2545 Regional Airlines ' 4. Set INOUT parameter deposit equal to concatenated a. deposit b. '\\UCF ' 5. Set INOUT parameter deposit equal to concatenated a. deposit b. '\MSB 260 ' 6. Set INOUT parameter deposit equal to concatenated a. deposit b. 'Pay to the order of: ' 7. Set INOUT parameter deposit equal to concatenated a. deposit b. v_empFirst C. d. v_empLast e. ' ' 8. Set INOUT parameter deposit equal to concatenated a. deposit b. v_empAddress c. ' ' 9. Set INOUT parameter deposit equal to concatenated a. deposit b. v_empCity C. " d. v_empState e. f. v_empZip g. ' ' 10. Set INOUT parameter deposit equal to concatenated 5 10. Set INOUT parameter deposit equal to concatenated a. deposit b. 'In the amount of: ' 11. Set INOUT parameter deposit equal to concatenated a. deposit b. '$' C. v_deposit d. ' ' 12. Set INOUT parameter deposit equal to concatenated a. deposit b. *** Pay will be deposited into account number provided *** ' 13. Set INOUT parameter deposit equal to concatenated a. deposit b. ' ' C. v lines d. ' ' h. End the loop using the loop label get_employee i. Close the stored cursor employee_cursor j. End stored procedure k. Set the delimiter back to ; 2. Write the source code to test the stored procedure a. Set session variable @deposit equal to """ b. Call stored procedure passing arguments a. employeeld from table timesheet b. @deposit session variable c. Select @deposit Query Description 1. Change to use the database airline 1. Create stored procedure payDeposit to do the following a. Set the delimiter to $$ b. Parameter list includes 3 i. IN employeeNum INT ii. INOUT deposit VARCHAR(4000) c. Declare the following variables i. v_finished integer DEFAULT 0 ii. v_lines varchar(100) DEFAULT" iii. v_empFirst varchar(100) DEFAULT"" iv. v_empLast varchar(100) DEFAULT"" v. v_empAddress varchar(100) DEFAULT vi. v_empCity varchar(100) DEFAULT"""; vii. v_empState varchar(100) DEFAULT"" viii. v_empZip varchar(100) DEFAULT"" ix. v tHours int DEFAULT 0 X. v_pHourly decimal(5,2) DEFAULT 0.0 xi. v overtime INT DEFAULT 0 xii. v_count integer DEFAULT 0 xiii. v_deposit decimal(8,2) DEFAULT 0.0 "" d. Declare cursor employee_cursor for the following join query i. Select 1. firstName from table employee 2. lastName from table employee 3. address from table employee 4. city from table cityState 5. state from table cityState 6. zipCode from table cityState 7. Sum columns sun, mon, tues, wed, thurs, fri, and sat from table timesheet as hours 8. hourly from table position ii. Where column emploveeld in table timesheet equals the value in e. ii. Where column employeeld in table timesheet equals the value in parameter employee Num Declare continue exception handler for not found set variable v_finished equal to 1 f. Open cursor employee_cursor g. Loop through the employee_cursor, using loop label get_employee i. Fetch employee_cursor into variables 1. v_empFirst 2. v_empLast 3. v_empAddress 4. v_empCity 5. v_empState 6. v_empZip 7. v tHours 8. v_pHourly ii. Check if variable v_finished equals 1; if true, leave the loop using the loop label get_employee iii. Set v_count equal to v_count plus 1 (one) 4 iv. If the variable v_count is equal to 1 (one) 1. Calculate the employee's deposit check using an IF/ELSEIF decision-making construct based on the following business logic a. If the employee's hours (i.e., v_tHours) are less than or equal to 40 then i. set v_deposit equal to the hours multiplied by the hourly rate b. Else If the employee's hours (i.e., v_tHours) are greater than 40 then i. set v_overtime equal to (v_ tHours minus 40) multiplied by (v_pHourly * 1.5) ii. set v_deposit equal to the employee's 40 hours multiplied by their hourly rate (i.e., v_pHourly) iii. set v_deposit equal to v_deposit plus v_overtime 2. Set INOUT parameter deposit equal to concatenated a. deposit 2. Set INOUT parameter deposit equal to concatenated a. deposit b. '\From: ' 3. Set INOUT parameter deposit equal to concatenated a. deposit b. CGS 2545 Regional Airlines ' 4. Set INOUT parameter deposit equal to concatenated a. deposit b. '\\UCF ' 5. Set INOUT parameter deposit equal to concatenated a. deposit b. '\MSB 260 ' 6. Set INOUT parameter deposit equal to concatenated a. deposit b. 'Pay to the order of: ' 7. Set INOUT parameter deposit equal to concatenated a. deposit b. v_empFirst C. d. v_empLast e. ' ' 8. Set INOUT parameter deposit equal to concatenated a. deposit b. v_empAddress c. ' ' 9. Set INOUT parameter deposit equal to concatenated a. deposit b. v_empCity C. " d. v_empState e. f. v_empZip g. ' ' 10. Set INOUT parameter deposit equal to concatenated 5 10. Set INOUT parameter deposit equal to concatenated a. deposit b. 'In the amount of: ' 11. Set INOUT parameter deposit equal to concatenated a. deposit b. '$' C. v_deposit d. ' ' 12. Set INOUT parameter deposit equal to concatenated a. deposit b. *** Pay will be deposited into account number provided *** ' 13. Set INOUT parameter deposit equal to concatenated a. deposit b. ' ' C. v lines d. ' ' h. End the loop using the loop label get_employee i. Close the stored cursor employee_cursor j. End stored procedure k. Set the delimiter back to ; 2. Write the source code to test the stored procedure a. Set session variable @deposit equal to """ b. Call stored procedure passing arguments a. employeeld from table timesheet b. @deposit session variable c. Select @deposit