MS SQL - Functions and Stored Procedures

             --Functions and Stored Procedures

--01. Employees with Salary Above 35000

CREATE PROC usp_GetEmployeesSalaryAbove35000
AS
BEGIN
    SELECT
           FirstName AS [First Name]
           ,LastName AS [Last Name]
      FROM Employees
     WHERE Salary > 35000
END

GO

EXEC usp_GetEmployeesSalaryAbove35000

GO

--02. Employees with Salary Above Number

CREATE PROC usp_GetEmployeesSalaryAboveNumber(@salary DECIMAL(18, 4))
AS
BEGIN
    SELECT
           FirstName AS [First Name]
           ,LastName AS [Last Name]
      FROM Employees
     WHERE Salary >= @salary
END

GO

EXEC usp_GetEmployeesSalaryAboveNumber 48100

GO

--03. Town Names Starting With

CREATE PROC usp_GetTownsStartingWith(@startNameTown VARCHAR(10))
AS
BEGIN
    SELECT
           [Name] AS Town
      FROM Towns
      WHERE SUBSTRING([Name], 1, LEN(@startNameTown)) = @startNameTown
END

GO

EXEC usp_GetTownsStartingWith EVE

GO

--04. Employees from Town

CREATE PROC usp_GetEmployeesFromTown(@townName VARCHAR(50))
AS
BEGIN
    SELECT
           e.FirstName
          ,e.LastName
      FROM Towns AS t
      JOIN Addresses AS a ON t.TownID = a.TownID
      RIGHT JOIN Employees AS e ON a.AddressID = e. AddressID
      WHERE t.[Name] = @townName
END

GO

EXEC usp_GetEmployeesFromTown Sofia

GO

--05. Salary Level Function

CREATE FUNCTION ufn_GetSalaryLevel(@salary DECIMAL(18,4))
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @result VARCHAR(10)
    SET @result = 'High'
    IF (@salary < 30000)
    BEGIN
        SET @result = 'Low'
    END
    ELSE IF (@salary BETWEEN 30000 AND 50000)
    BEGIN
        SET @result = 'Average'
    END
    RETURN @result
END

GO

SELECT
       Salary
       ,dbo.ufn_GetSalaryLevel(Salary) AS SalaryLevel
  FROM Employees

GO

--06. Employees by Salary Level

CREATE PROC usp_EmployeesBySalaryLevel (@salaryLevel VARCHAR(10))
AS
BEGIN
    SELECT
           FirstName AS [First Name]
           ,LastName AS [Last Name]
      FROM Employees
      WHERE dbo.ufn_GetSalaryLevel(Salary) = @salaryLevel
END

GO

EXEC usp_EmployeesBySalaryLevel 'High'
EXEC usp_EmployeesBySalaryLevel 'Low'
EXEC usp_EmployeesBySalaryLevel 'Average'

GO

--07. Define Function

CREATE FUNCTION ufn_IsWordComprised(@setOfLetters VARCHAR(50), @word VARCHAR(50))
RETURNS BIT
AS
BEGIN
    DECLARE @WordLength INT = LEN(@word)
    DECLARE @Index INT = 1

    WHILE (@Index <= @WordLength)
    BEGIN
        IF (CHARINDEX(SUBSTRING(@word, @Index, 1), @setOfLetters) = 0)
        BEGIN
            RETURN 0
        END

        SET @Index += 1
    END

    RETURN 1
END

GO

SELECT dbo.ufn_IsWordComprised('oistmiahf', 'Sofia')
SELECT dbo.ufn_IsWordComprised('oistmiahf', 'halves')

GO

--08. Delete Employees and Departments

CREATE PROC usp_DeleteEmployeesFromDepartment (@departmentId INT)
AS
BEGIN
    DELETE
      FROM EmployeesProjects
     WHERE EmployeeID IN(
                         SELECT
                                EmployeeID
                           FROM Employees
                          WHERE DepartmentID = @departmentId
                        )
    UPDATE Employees
       SET ManagerID = NULL
     WHERE ManagerID IN(
                         SELECT
                                EmployeeID
                           FROM Employees
                          WHERE DepartmentID = @departmentId
                        )
    ALTER TABLE Departments
    ALTER COLUMN ManagerID INT
    UPDATE Departments
       SET ManagerID = NULL
     WHERE ManagerID IN(
                         SELECT
                                EmployeeID
                           FROM Employees
                          WHERE DepartmentID = @departmentId
                        )

    DELETE FROM Employees
          WHERE DepartmentID = @departmentId

    DELETE FROM Departments
          WHERE DepartmentID = @departmentId

    SELECT
     COUNT(*)
      FROM Employees
     WHERE DepartmentID = @departmentId
END

--09. Find Full Name
USE Bank

GO

CREATE PROC usp_GetHoldersFullName
AS
BEGIN
    SELECT
           CONCAT(FirstName, ' ', LastName) AS [Full Name]
      FROM AccountHolders
END

GO

--10. People with Balance Higher Than

CREATE PROC usp_GetHoldersWithBalanceHigherThan(@number MONEY)
AS
BEGIN
            SELECT
                   ah.FirstName
                   ,ah.LastName
                   --,SUM(a.Balance)
              FROM AccountHolders AS ah
         LEFT JOIN Accounts AS a ON ah.Id = a.AccountHolderId
          GROUP BY ah.FirstName, ah.LastName
            HAVING SUM(a.Balance) > @number
          ORDER BY ah.FirstName, ah.LastName
END

GO

EXEC usp_GetHoldersWithBalanceHigherThan 20000

GO

--11. Future Value Function
CREATE FUNCTION ufn_CalculateFutureValue(@sum DECIMAL(16, 4), @rate FLOAT, @years INT)
RETURNS DECIMAL(16, 4)
AS
BEGIN
    DECLARE @result DECIMAL(16, 4) = @sum
    WHILE(@years > 0)
    BEGIN
        SET @result += (@sum * @rate)
        SET @years -= 1
        SET @sum = @result
    END
    RETURN @result
END

GO

--11.1 Future Value Function
CREATE FUNCTION ufn_CalculateFutureValue(@sum DECIMAL(16, 4), @rate FLOAT, @years INT)
RETURNS DECIMAL(16, 4)
AS
BEGIN
    DECLARE @result DECIMAL(16, 4) = @sum * POWER(1 + @rate, @years)
    RETURN @result
END

GO

SELECT dbo.ufn_CalculateFutureValue(123.12, 0.1, 5)

GO

--12. Calculating Interest

CREATE PROC usp_CalculateFutureValueForAccount (@accountId INT, @rate FLOAT)
AS
BEGIN
       SELECT
              ah.Id AS [Account Id]
              ,ah.FirstName AS [First Name]
              ,ah.LastName AS [Last Name]
              ,a.Balance AS [Current Balance]
              ,dbo.ufn_CalculateFutureValue(a.Balance, @rate, 5) AS [Balance in 5 years]
         FROM AccountHolders AS ah
    LEFT JOIN Accounts AS a ON ah.Id = a.AccountHolderId
        WHERE a.Id = @accountId
END

GO

EXEC usp_CalculateFutureValueForAccount 1, 0.1

GO

--13 Table-valued Function: Cash in User Games Odd Rows
USE Diablo

CREATE FUNCTION ufn_CashInUsersGames (@gameName NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT
       SUM(Cash) AS SumCash
  FROM (
       SELECT ug.Cash
              ,ROW_NUMBER() OVER (ORDER BY ug.Cash DESC) AS RowNumber
         FROM UsersGames AS ug
         JOIN Games AS g ON ug.GameId = g.Id
        WHERE g.[Name] = @gameName
        ) AS RowNumberSubquery
 WHERE RowNumber % 2 != 0
)

GO

SELECT * FROM dbo.ufn_CashInUsersGames('Love in a mist')

GO