Assignment 6B - Cursors

Objectives

Use a cursors in a stored procedure

Assignment

You will be creating a simple stored procedure that uses one or more Cursors  - name the stored procedure sp_DisplayCollege. You may develop multiple stored procedures to achieve this. You are going to use the data from COP4709 Assignment 2B - Import and Analyze Data

The stored procedure should accept a single argument - the name of a College, it will need to find all Colleges that match the name (including partial match).

For each College that matches the criteria - we are going to prepare a data report. This report will be a comparison of the college on each of these fields as compared to the average of the value of the field.

You output should look something like this (does not have to be this format exactly -just needs to have the data).

Average is simply an average of all the values of the field AVG(CUML_DEBT_P90). The weighted average takes into account that some schools have more students than others - and thus takes into account the size of the school SUM(CUML_DEBT_P90*CUML_DEBT_N)/SUM(CUML_DEBT_N) .

College: Alabama A&M University

Repeat for all matching colleges (that is where the cursor is important).

You will turn in the text of the stored procedure and a screen capture of the execution of this with results.

note: You may demonstrate your knowledge of use of cursors through stored procedures that utilize stored procedures other than this specific example.

Information

The video and weekly reading on cursors should be sufficient for this assignment. I recommend writing a series of stored procedures and functions to achieve the report. First I would create a stored procedure that takes the input (Name of College) and creates a cursor from this. I would then call out to multiple stored procedures and stored functions to do the queries associated with the report. This will allow you to divide and conquer each of the pieces of the report.

Estimated Completion Time

5 hours to cover cursors and write all the necessary stored procedures.

Supporting Lectures 

Topic - Stored Procedures Part 2

Questions and Answers

Q and A - Tackling the Cumulative Debt Cursor Problem

External Resources

DECLARE Cursor Syntax and Example - https://msdn.microsoft.com/en-us/library/ms180169.aspx 

How Cursors Work - good easy to read article on Cursors - http://searchsqlserver.techtarget.com/feature/Part-1-How-cursors-work 

Grading Criteria

Demonstration of a working stored procedure using a cursor by submitting the code and the results is sufficient for completion of this assignment.