In T-SQL Please. I would really appreciate it!

Please fulfill the requirements stated below with the following database:

/*use master;

GO

Alter database Project3 set single_user with rollback immediate;

GO

DROP Database Project3;

GO

*/

CREATE DATABASE Project3;

GO

USE Project3;

GO

CREATE TABLE dbo.Departments (

DepartmentID INT IDENTITY PRIMARY KEY,

DepartmentName NVARCHAR(60) NOT NULL,

DepartmentDesc NVARCHAR(150) CONSTRAINT DF_DFDeptDesc DEFAULT 'Dept. Desc to be determined'

);

CREATE TABLE dbo.Employees (

EmployeeID INT IDENTITY PRIMARY KEY,

DepartmentID INT CONSTRAINT FK_Employee_Department FOREIGN KEY REFERENCES dbo.Departments ( DepartmentID ),

ManagerEmployeeID INT CONSTRAINT FK_Employee_Manager FOREIGN KEY REFERENCES dbo.Employees ( EmployeeID ),

FirstName NVARCHAR(60),

LastName NVARCHAR(60),

Salary MONEY CONSTRAINT CK_EmployeeSalary CHECK ( Salary >= 0 ),

CommissionBonus MONEY CONSTRAINT CK_EmployeeCommission CHECK ( CommissionBonus >= 0 ),

FileFolder NVARCHAR(256) CONSTRAINT DF_FileFolder DEFAULT 'ToBeCreated'

);

GO

INSERT INTO dbo.Departments ( DepartmentName, DepartmentDesc )

VALUES ( 'Management', 'Executive Management' ),

( 'HR', 'Human Resources' ),

( 'DatabaseMgmt', 'Database Management'),

( 'Support', 'Product Support' ),

( 'Software', 'Software Sales' ),

( 'Marketing', 'Digital Marketing' );

GO

SET IDENTITY_INSERT dbo.Employees ON;

GO

INSERT INTO dbo.Employees ( EmployeeID, DepartmentID, ManagerEmployeeID, FirstName, LastName, Salary, CommissionBonus, FileFolder )

VALUES ( 1, 4, NULL, 'Sarah', 'Campbell', 78000, NULL, 'SarahCampbell' ),

( 2, 3, 1, 'James', 'Donoghue', 68000 , NULL, 'JamesDonoghue'),

( 3, 1, 1, 'Hank', 'Brady', 76000 , NULL, 'HankBrady'),

( 4, 2, 1, 'Samantha', 'Jones', 72000, NULL , 'SamanthaJones'),

( 5, 3, 4, 'Fred', 'Judd', 44000, 5000, 'FredJudd'),

( 6, 3, NULL, 'Hanah', 'Grant', 65000, 4000 , 'HanahGrant'),

( 7, 3, 4, 'Dhruv', 'Patel', 66000, 2000 , 'DhruvPatel'),

( 8, 4, 3, 'Dash', 'Mansfield', 54000, 5000 , 'DashMansfield');

GO

SET IDENTITY_INSERT dbo.Employees OFF;

GO

CREATE FUNCTION dbo.GetEmployeeID (

-- Parameter datatype and scale match their targets

@FirstName NVARCHAR(60),

@LastName NVARCHAR(60) )

RETURNS INT

AS

BEGIN;

DECLARE @ID INT;

SELECT @ID = EmployeeID

FROM dbo.Employees

WHERE FirstName = @FirstName

AND LastName = @LastName;

-- Note that it is not necessary to initialize @ID or test for NULL,

-- NULL is the default, so if it is not overwritten by the select statement

-- above, NULL will be returned.

RETURN @ID;

END;

GO

Requirement 1 Basic Stored Procedure

Create a stored procedure to insert into dbo.Departments. The procedure should accept the appropriate parameters (only those required to create the record).

Requirement 2 Basic Procedure Execution

Write a script that will execute the procedure created in requirement 1. Create the following four departments:

Requirement 3 Scalar Function

Create a function to get an Department ID by name (not Desc). The function should use one parameter to be used to reference the department name. The return type should be appropriate for returning DepartmentID. The function should return the DepartmentID of the Department that is found. If it is not found, the function should return NULL.

Requirement 4 Intermediate Stored Procedure

Create a stored procedure called that will insert a record into dbo.Employees. The procedure should accept the following parameters:

DepartmentName : Infrastructure

EmployeeFirstName : Wherewolf

EmployeeLastName : Waldo

Salary

FileFolder : FirstNameLastName

ManagerFirstName : YourActualFirstName

ManagerLastName : YourActualLastName

CommissionBonus

The Salary parameter should be optional. If not specified, it should default to 46000. The CommissionBonus parameter should be optional. If not specified it should default to 3500.

The procedure should use the function created in requirement 3 to look up the department by the department Name. If the function returns null, a new department should be created.

The procedure should use the function provided in the Project3_InitialDB.SQL to look up the managers employee ID by first name and last name. If the function returns null, a new manager should be created.

The FileFolder field should be named based on concatenating the Employee First Name and Employee Last Name together, so that it can be used to store & find information for a specific employee. The procedure must be tested to insert a new employee, using the DepartmentName, EmployeeFirstName, EmployeeLastName, Salary, FileFolder, Manager Names and CommissionBonus parameters (replacing the 1st test ManagerFirstName and ManagerLastName fields with your name ... eg. if I was Waldo Donoghue then ManagerFirstName = Waldo, ManagerLastName = Donoghue). For the ManagerEmployeeID and DepartmentID column, it should use the ID that was either found or created in the step above.

Important Note : Any new departments or managers created by this procedure should not be committed to the database if the insert for the employee fails. Execute 2 Tests of this new procedure with these input parameter values. Include a simple Select * of all records in the Employees and Departments tables after the 2 tests to make sure updates are successful:

Test 1:

DepartmentName : Infrastructure

EmployeeFirstName : Wherewolf

EmployeeLastName : Waldo

FileFolder : FirstNameLastName

ManagerFirstName : YourActualFirstName

ManagerLastName : YourActualLastName

Test2:

DepartmentName : Support

EmployeeFirstName : YourActualFirstName

EmployeeLastName : YourActualLastName

Salary : 43000

FileFolder : FirstNameLastName

ManagerFirstName : Osam

ManagerLastName : Ali

CommissionBonus : 1500

Requirement 5 Table Value Function

Write a table value function that will return a table displaying all the employee and department data (without the ID values though) for employees greater than a given commission value. (Only execute the select if the commission is >= 0, but dont worry about an error message in this case...). Test this function with a commission value of 3500.

Requirement 6 Window Function

Write a window function that will rank employees by department, based on descending Salary (i.e. highest Salary should be #1). The query should also get the name and Salary of the person above them. Also include the average salary that shows how each person and department compares to each other, so that HR can decide how to manage salary levels going forward. Also add a TotalCompensation column that shows the total of Salary + CommissionBonus. Execute this Window Function to test it.

Requirement 7 Recursive CTE

Write a recursive CTE that will get employees by their manager. Include the following columns:

Employee LastName

Employee FirstName

Department ID

FileFolder

Manager LastName

Manager FirstName

The field called FileFolder is used to store the performance review for each employee. Note since managers will also have access to all the people that report to them either directly or indirectly, each managers folder will eventually be setup to contain not just their own performance review files, but also all the subfolders for each of the employees that report directly to them. To help facilitate that, also include a column called File Path that will determine and show the file path name for each employee using Windows style of \ between subfolders, ie. in the format ManagerFileFolder\EmployeeFileFolder\ etc. To illustrate how this would work, if for example I reported directly to Dev Sainani and Dev reports to Peter Devlin, then my File Path would be PeterDevlin\DevSainani\OsamAl

\begin{tabular}{|ll|} \hline DepartmentName & DepartmentDesc \\ \hline QA & Software Testing and Quality Assurance \\ \hline SysDev & Systems Design and Development \\ \hline Deployment & Deployment and Production Support \\ \hline TechSupport & Online Technical Support \\ \hline \end{tabular} \begin{tabular}{|ll|} \hline DepartmentName & DepartmentDesc \\ \hline QA & Software Testing and Quality Assurance \\ \hline SysDev & Systems Design and Development \\ \hline Deployment & Deployment and Production Support \\ \hline TechSupport & Online Technical Support \\ \hline \end{tabular}