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

  

Transcribed Image Text:

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 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

Answer rating: 100% (QA)

I see youve provided two images The first image outlines the task of creating a stored procedure nam... View the full answer