Write recursive t-sql code to find NTILE / DECILE / Quintile based on Salary descending for employee in the Department.

Write a query which will give below output.

There’s a table with below data

Query:

Declare @v_Employee as table(DeptID int, EmpNo int, Salary decimal(18,2))

Declare @v_Ntile int

Set @v_Ntile = 2

Insert into @v_Employee values(1,1,1000)
Insert into @v_Employee values(1,2,2000
Insert into @v_Employee values(1,3,3000)
Insert into @v_Employee values(2,4,4000)
Insert into @v_Employee values(2,5,5000)
Insert into @v_Employee values(2,6,6000)
Insert into @v_Employee values(2,7,7000)

Select * From @v_Employee

Select 
    DeptID, 
    EmpNo, 
    Ntile(@v_Ntile) Over(Partition By DeptID Order By Salary  Desc)  NtileNo
From 
    @v_Employee

Note:

1) To find Decile value, set Ntile value to 10.
2) To find Quin-tile (Quintile) value, set Ntile value to 5.

Write recursive t-sql code to fill missing Salary for employee using Average for the Department.

There’s a table with below data

Write a query which will give below output.

Query:

Declare

@v_Employee as table(DeptID int, EmpNo int, Salary decimal(18,2))

Insert into @v_Employee values(1,1,1000)

Insert into @v_Employee values(1,2,null)

Insert into @v_Employee values(1,3,3000)

Insert into @v_Employee values(2,4,4000)

Insert into @v_Employee values(2,5,null)

Insert into @v_Employee values(2,6,6000)

Insert into @v_Employee values(2,7,7000)

Select * From @v_Employee

Select

T.DeptID,

EmpNo,

Case when Salary is not null then Salary else AvgSalary end

From

(

 select
 DeptID,
 avg(Salary) as AvgSalary
 from
 @v_Employee
 Group By
 DeptID
) T
inner join @v_Employee E on E.DeptID = T.DeptID

Write recursive t-sql code to generate reverse of given string

Create Function [dbo].[fn_Reverse](@v_Input nvarchar(max))
Returns nvarchar(max)
as
Begin
If (len(@v_Input) <> 1)
Begin
return [dbo].[fn_Reverse](substring(@v_Input, 2, len(@v_Input) - 1)) + substring(@v_Input, 1, 1)
End
return @v_Input
End
GO

select [dbo].[fn_Reverse]('acbdkkdfkdkf')
Go

--> fkdkfdkkdbca

Write recursive t-sql code to get Factorial of integer number

Create Function dbo.fn_Factorial(@v_Number bigint)

Returns bigint

as

BeginIf (@v_Number != 1)
go
Select dbo.fn_Factorial(6)

Begin

return @v_Number * dbo.fn_Factorial(@v_Number - 1)

End

return 1

End

go