ComputerScienceExpert

(11)

$18/per page/

About ComputerScienceExpert

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:
Applied Sciences,Calculus See all
Applied Sciences,Calculus,Chemistry,Computer Science,Environmental science,Information Systems,Science Hide all
Teaching Since: Apr 2017
Last Sign in: 103 Weeks Ago, 3 Days Ago
Questions Answered: 4870
Tutorials Posted: 4863

Education

  • MBA IT, Mater in Science and Technology
    Devry
    Jul-1996 - Jul-2000

Experience

  • Professor
    Devry University
    Mar-2010 - Oct-2016

Category > Programming Posted 26 Apr 2017 My Price 9.00

This iLab is due at the end of Week 3.

Using Cursors (Lab 3 of 7)

This iLab is due at the end of Week 3.

Scenario/Summary:

You will be developing SQL PL procedures that use cursors to process result sets on a record-at-a-time basis. You will also demonstrate the use of positioned updates and develop a cursor that maintains its position across transactions.

Upon completion of this lab, students will have hands-on experience using various cursor management techniques in SQL PL.

Deliverables:

Lab Steps (to be completed in Power Systems)

Points

Step 1

Create a new table.

5 Points

Step 2

Populate the table with data.

5 Points

Step 3

Create a procedure that uses a cursor to apply logic on a row-by-row basis.

15 Points

Step 4

Create a procedure that uses a cursor to perform positioned updates.

15 Points

Step 5

Create a procedure using a cursor that maintains position after a transaction.

20 Points

Total Lab Points

60 Points

You should submit a Word document to the Dropbox containing:

  • SQL PL code for each task; and
  • screenshots of the output from each task (each screenshot immediately follows the associated code).

iLab Steps

 

Step 1:  Create a new table
  • Create a table called Transactions using the following data definition parameters:

Attribute

Data Type

Restrictions

TransactionNumber

INTEGER

Primary Key

Amount

DECIMAL(6,2)

  

TransType

CHAR(1)

  

Step 2: Populate the table with data

a. Add the following data to your Transactions table. Use the COMMIT statement when you are done. (This need not be done inside of a procedure – just adding the records in straight SQL is fine.)

TransactionNumber

Amount

TransType

1

47.00

C

2

22.50

C

3

39.82

D

4

44.28

C

5

24.36

C

6

58.16

D

7

44.52

C

8

31.74

C

9

56.78

C

10

11.40

D

11

67.90

C

12

55.14

D
  •  Use a SELECT statement to display the rows you have added.

Step 3: Row-by-Row Processing

a. Write an SQL PL procedure that uses a cursor to work through these records in transaction number order (lowest to highest). Records with a TransType of ‘C’ are credits, whereas those with a type of ‘D’ are debits. Your procedure should maintain a running total based on the transactions it encounters, adding to the total for credit transactions, and subtracting from it for debit transactions. Once the running total reaches 100 or more, subsequent credits will only be added at half their stated value, whereas subsequent debits will be subtracted as usual (at their stated value). This formula will still apply even if the balance later drops below 100 again. When finished with its calculations, your procedure should output a message of the following form:

Running total is now: $<<value of running total>>

 Make sure that it would still work correctly if the data in the table were different.

b. Show the results of running your procedure.

Step 4: Positioned Updates

Create an SQL PL procedure that uses a cursor to examine all of the records in the Transactions table. If it encounters a debit transaction for less than $15, it should use a positioned update to double the amount of that transaction. Issue a COMMIT statement after the cursor has looped through all records.

Note: Make your procedure flexible so that it can accommodate whatever values are found. Don’t hard-code for records that happen to be in the table right now.

Execute your procedure, and then run a SELECT statement to show all of the values now in the Transactions table.

Step 5: Cursor that Maintains Position

a. Create an SQL PL procedure that uses a cursor to examine all of the records in the Transactions table. For each record, it should use a positioned update to add one to the amount of the transaction. Issue a COMMIT statement immediately after each update – don’t wait until the cursor has finished looping through all records, as you did for the previous step in this lab.

Note: Make your procedure flexible so that it can accommodate whatever values are found. Don’t hard-code for records that happen to be in the table right now.

b. Execute your procedure, and then run a SELECT statement to show all of the values now in the Transactions table.

END OF LAB 3

Answers

ComputerScienceExpert
(11)
Status NEW Posted 26 Apr 2017 08:04 AM My Price 9.00

-----------

Attachments

file 1493195820-Solutions file 2.docx preview (51 words )
H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------
Not Rated(0)