please need help with this anyone know! You need to create COMPANY database firs

Question

please need help with this anyone know!

You need to create COMPANY database first in order to complete this assignment. Be sure to download companyDB.sql and execute it before you start.

1. Write a stored procedure named as update_superSSN. The procedure takes two input parameters: empSSN (as an employee’s SSN) and new_superSSN (as a new supervisor’s SSN). The procedure will update the supervisor’s SSN to new_superSSN for the given empSSN. The template for defining such a procedure is given as below create procedure update_superSSN(empSSN in employee.SSN%type, new_superSSN in employee.superssn%type) as … //procedure code

2. Write a stored function named as NumOfEmployee_ByDept. The function has one input parameter deptname (i.e., department name) and returns the number of employees working in that department.

Once the stored function is compiled without any errors, you can run the following SQL query Select dname, NumOfEmployee_byDept(dname) From department;

Explanation / Answer

ANSWER:

I created Database called COMPANY.

Let us consider the Table called 'EMPLOYEE'.

1.

USE [COMPANY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[update_superSSN]
(
   @empSSN    VARCHAR(20),
   @new_superSSN    VARCHAR(20)
)
AS
BEGIN
   BEGIN TRANSACTION
  
       UPDATE EMPLOYEE SET SUPERVISOR_SSN = @new_superSSN WHERE SUPERVISOR_SSN = @empSSN
      
   COMMIT TRANSACTION

END

2.

USE [COMPANY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NumOfEmployee_ByDept]
(
   @deptName    VARCHAR(20)
)
AS
BEGIN
   BEGIN TRANSACTION
  
       SELECT COUNT(EMPLOYEE_NAME) 'No. of Employee' FROM EMPLOYEE WHERE DEPARTMENT_NAME = @deptName
      
   COMMIT TRANSACTION

END

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.