TSQL

--1. Create a database with two tables
--Persons (id (PK), first name, last name, SSN)
CREATE TABLE Persons
(
Id INT PRIMARY KEY IDENTITY NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
SSN nvarchar(50) NOT NULL UNIQUE,
)
GO
--Accounts (id (PK), person id (FK), balance). Insert few records for testing.
CREATE TABLE Accounts
(
Id INT PRIMARY KEY IDENTITY NOT NULL,
PersonId INT NOT NULL,
CONSTRAINT fk_PersonId FOREIGN KEY (PersonId) REFERENCES Persons(Id),
Balance MONEY
)
GO

--Insert some data
INSERT INTO Persons VALUES ('Asen', 'Asenov', '136-426-632')
INSERT INTO Persons VALUES ('Ivan', 'Ivanov', '174-135-163')
INSERT INTO Persons VALUES ('Petkan', 'Petkanov', '306-760-158')
INSERT INTO Persons VALUES ('Pesho', 'Peshkov', '306-760-158') --Should throw error because of UNIQUE key constraint
INSERT INTO Persons VALUES ('Pesho', 'Peshkov', '307-560-158')

INSERT INTO Accounts VALUES (1, 0)
INSERT INTO Accounts VALUES (2, 1230)
INSERT INTO Accounts VALUES (3, 10640.36)
INSERT INTO Accounts VALUES (3, 5320.24)

SELECT * FROM Accounts
GO
--Write a stored procedure that selects the full names of all persons.
CREATE PROCEDURE dbo.getFullNameOfPersons
AS
   SELECT FirstName + ' ' + LastName AS [FULL Name]
   FROM Persons
GO

EXEC dbo.getFullNameOfPersons
GO
--2. Create a stored procedure
CREATE PROCEDURE dbo.getAllAccountsWithMoreMoney(@Money MONEY)
AS
    SELECT FirstName, LastName, a.Balance
    FROM Persons p
        JOIN Accounts a ON a.PersonId = p.Id
    WHERE a.Balance >= @Money
GO

DECLARE @Money MONEY = 5000;
EXEC dbo.getAllAccountsWithMoreMoney @Money

SET @Money = 10000;
EXEC dbo.getAllAccountsWithMoreMoney @Money
GO

--3. Create a function with parameters
CREATE FUNCTION dbo.udf_calculateSumAfterMonthsWithInterestRate(@Money MONEY, @InterestRate FLOAT, @Months INT)
RETURNS MONEY
AS
BEGIN
    DECLARE @InterestPerMonth MONEY = @Money * (@InterestRate / 100)
    RETURN (@InterestPerMonth * @Months) + @Money
END
GO

DECLARE @Money MONEY = 100;
DECLARE @InterestRate INT = 20;
DECLARE @Months INT = 2;

SELECT dbo.udf_calculateSumAfterMonthsWithInterestRate(@Money, @InterestRate, @Months)
GO

--4. Create a stored procedure that uses the function from the previous example.
CREATE PROCEDURE dbo.udp_calculateAccountSumAfterInterestRateForOneMonth(@AccountId INT, @InterestRate FLOAT)
AS
    SELECT dbo.udf_calculateSumAfterMonthsWithInterestRate(Balance, @InterestRate, 1)
    FROM Accounts
    WHERE Id = @AccountId
GO

EXEC dbo.udp_calculateAccountSumAfterInterestRateForOneMonth @AccountId = 4, @InterestRate = 100
GO

--5. Add two more procedures
CREATE PROCEDURE dbo.udp_withdrawMoney(@AccountId INT, @Money MONEY)
AS
    DECLARE @NewBalance MONEY = ((SELECT Balance FROM Accounts WHERE Id = @AccountId) - @Money)
    IF (@NewBalance >= 0)
    BEGIN
        UPDATE Accounts
        SET Balance = @NewBalance
        WHERE Id = @AccountId
    END
    ELSE
    BEGIN
        RAISERROR('Not enough money to withdraw', 15, 1)
    END
GO

CREATE PROCEDURE dbo.udp_depositMoney(@AccountId INT, @Money MONEY)
AS
    UPDATE Accounts
    SET Balance = Balance + @Money
    WHERE Id = @AccountId
GO

DECLARE @AccountId INT = 1
DECLARE @Money INT = 100
SELECT Balance FROM Accounts WHERE Id = @AccountId;

EXEC dbo.udp_depositMoney @AccountId = @AccountId, @Money = @Money;
SELECT Balance FROM Accounts WHERE Id = @AccountId;

EXEC dbo.udp_withdrawMoney @AccountId = @AccountId, @Money = @Money;
SELECT Balance FROM Accounts WHERE Id = @AccountId;

EXEC dbo.udp_withdrawMoney @AccountId = 1, @Money = 1000000; --Should raise error
GO

--6. Create table Logs
CREATE TABLE Logs
(
    Id INT PRIMARY KEY IDENTITY NOT NULL,
    AccountId INT NOT NULL,
    CONSTRAINT fk_AccountId FOREIGN KEY (AccountId) REFERENCES Accounts(Id),
    OldSum MONEY NOT NULL,
    NewSum MONEY NOT NULL
)
GO
--Add trigers

CREATE TRIGGER accounts_balanceUpdate ON Accounts
FOR UPDATE
AS
    DECLARE @OldBalance MONEY = (SELECT OLD.Balance FROM DELETED OLD)
    DECLARE @NewBalance MONEY = (SELECT NEW.Balance FROM INSERTED NEW)

    IF (@OldBalance != @NewBalance)
    BEGIN
        DECLARE @AccountId INT = (SELECT Id FROM INSERTED)
        INSERT INTO Logs VALUES (@AccountId, @OldBalance, @NewBalance)
    END
GO

--Test trigers

DECLARE @Money INT = 300.34

EXEC dbo.udp_depositMoney @AccountId = 2, @Money = @Money;
EXEC dbo.udp_withdrawMoney @AccountId = 3, @Money = @Money;

SELECT * FROM Logs
GO

--7. Define function in the SoftUni Database
CREATE FUNCTION dbo.udf_checkIfInputIsComposedFromSetOfLetters
(
    @INPUT NVARCHAR(MAX),
    @SetOfLetters NVARCHAR(MAX)
)
RETURNS BIT
AS BEGIN
    DECLARE @i INT = 1;
    WHILE (@i <= LEN(@INPUT))
    BEGIN
        IF ( @SetOfLetters NOT LIKE '%' + SUBSTRING(@INPUT, @i, 1) + '%' ) BEGIN
            RETURN 0
        END
        SET @i = @i + 1
    END
    RETURN 1
END
GO

--Test the function
DECLARE @SetOfLetters NVARCHAR(50) = 'oistmiahf'

SELECT FirstName
FROM Employees
WHERE dbo.udf_checkIfInputIsComposedFromSetOfLetters(FirstName, @SetOfLetters) = CAST(1 AS BIT)

SELECT MiddleName
FROM Employees
WHERE dbo.udf_checkIfInputIsComposedFromSetOfLetters(FirstName, @SetOfLetters) = CAST(1 AS BIT)

SELECT LastName
FROM Employees
WHERE dbo.udf_checkIfInputIsComposedFromSetOfLetters(LastName, @SetOfLetters) = CAST(1 AS BIT)

SELECT Name AS [Town Name]
FROM Towns
WHERE dbo.udf_checkIfInputIsComposedFromSetOfLetters(Name, @SetOfLetters) = CAST(1 AS BIT)

GO
--