Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Write a Stored Procedure SP_Report_NEW_Budget using the view you created in Part 1-3). Use CURSOR to write the stored Procedure for the tasks below.

Write a Stored Procedure SP_Report_NEW_Budget using the view you created in Part 1-3). Use CURSOR to write COMPANY DATABASE EMPLOYEE FNAME MINI LNAME T John B Franklin T Joyce A Ramesh K James E Jennifer S Ahmad V 

Write a Stored Procedure SP_Report_NEW_Budget using the view you created in Part 1-3). Use CURSOR to write the stored Procedure for the tasks below. does the following tasks: Your Stored Procedure SP_Report_NEW_Budget 1) It creates a new table NEW_Dept_Budget as below: NEW_Dept_Budget has 5 columns Dept No (Int) Dept Name (Char(30)) COUNT_Emp (INT) New_SUM_Salary (INT) New_AVE_Salary (INT) 2) Check if the view VDept_Budget is empty or not (by counting rows from the view). 3) If not empty, For each row of the view VDept_Budget, populate (Insert) the new table NEW_Dept_Budget from the view VDept_Budget and New_SUM_Salary, New_AVE_Salary with the newly calculated two columns as calculated below. Calculate New_SUM_Salary, New_AVE_Salary as follow: If Dept = 1, Increase SUM (Salary) by 10% If Dept = 4, Increase SUM (Salary) by 20% If Dept = 5, Increase SUM (Salary) by 30% If Dept = 7, Increase SUM (Salary) by 40% You can also do this task in your stored procedure by populating (insert) the new table directly from the view first then update two columns of the new table with the newly calculated new Sum and new Ave later. Output in screen captures for the Stored Procedure created and the execution of the Stored Procedure by showing the contents of the View and the new table in Select statements in your output. COMPANY DATABASE EMPLOYEE FNAME MINI LNAME T John B Franklin T Joyce A Ramesh K James E Jennifer S Ahmad V Alicia J DEPARTMENT DNAME Headquarters Administration Research Automation PROJECT DEPT LOCATIONS DNUMBER DEPENDENT ESSN 123456789 Alice 123456789 Elizabeth ProductX ProductY ProductZ 123456789 Michael 333445555 Alice 333445555 Joy 333445555 Theodore 987654321 Abner 4 5 PNAME 1 333445555 333445555 333445555 453453453 453453453 5 5 Computerization Reorganization Newbenefits WORKS ON ESSN 123456789 123456789 333445555 666884444 888665555 987654321 987654321 987987987 DNUMBER Smith Wong DEPENDENT_NAME English Narayan Borg Wallace 1 2 2 Jabbar Zelaya DLOCATION Houston Stafford Bellaire Sugarland Houston 3 10 20 1 2 3 20 20 30 10 987987987 30 999887777 10 999887777 30 PNUMBER PNO Hours 32.5 7.5 10 10 1 4 5 333445555 7 123456789 10 10 20 20 40 15 SSN 20 35 123456789 333445555 5 10 30 453453453 666884444 888665555 987654321 987987987 999887777 MGRSSN 888665555 987654321 1 Bellaire 2 Sugarland 3 Houston 10 Stafford 20 Houston 30 Stafford SEX F F M F F M M BDATE 09-Jan-55 08-Dec-45 31-Jul-62 15-Sep-52 10-Nov-27 20-Jun-31 29-Mar-59 19-Jul-58 MGRSTARTDATE 19-Jun-71 01-Jan-85 22-May-78 06-Oct-05 PLOCATION DNUM 5 5 BDATE 31-Dec-78 Daughter 05-May-57 Spouse 01-Jan-78 Son 05-Apr-76 Daughter 03-May-48 Spouse 25-Oct-73 Son 29-Feb-32 Spouse 5 4 I 4 ADDRESS 731 Fondren, Houston, TX 638 Voss, Houston, TX 5631 Rice, Houston, TX 975 Fire Oak, Humble, TX 450 Stone, Houston, TX 291 Berry, Bellaire, TX 980 Dallas, Houston, TX 3321 Castle, SPring, TX RELATIONSHIP SEX M M F M M F M F SALARY 30000 40000 25000 38000 55000 43000 25000 25000 SUPERSSN DNO 987654321 888665555 333445555 333445555 5 5 5 5 1 4 4 4 888665555 987654321 987654321

Step by Step Solution

3.50 Rating (157 Votes )

There are 3 Steps involved in it

Step: 1

To create a stored procedure as described lets break down the problem step by step with clear headings explanations and realtime examples Step 1 Create the Stored Procedure Structure We need to create ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Joseph J. Adamski

7th edition

978-1111825911, 1111825912, 978-1133684374, 1133684378, 978-111182591

More Books

Students also viewed these Databases questions

Question

7 In presentations, why is eye contact so critical?

Answered: 1 week ago

Question

What elements of multimedia-based instruction facilitate learning?

Answered: 1 week ago

Question

What is location transparency?

Answered: 1 week ago