TSQL_09 Telerik

USE TSql
GO

/*1 Create a database with two tables: Persons(Id(PK), FirstName, LastName, SSN) 
and Accounts(Id(PK), PersonId(FK), Balance). Insert few records for testing.
Write a stored procedure that selects the full names of all persons.*/

CREATE TABLE Persons
(
	[Id] INT IDENTITY,
	[FirstName] NVARCHAR(50),
	[LastName] NVARCHAR(50),
	[SSN] NVARCHAR(50),
	CONSTRAINT PK_Persons_Id PRIMARY KEY(Id),
);
GO

CREATE TABLE Accounts
(
	[Id] INT IDENTITY,
	[PersonId] INT NOT NULL,
	[Balance] MONEY NOT NULL,
	CONSTRAINT PK_Accounts_Id PRIMARY KEY(Id),
	CONSTRAINT FK_Accounts_PersonId FOREIGN KEY(PersonId) REFERENCES Persons(Id)
);
GO

INSERT INTO Persons VALUES
(
	'Pesho', 'Ivanov', 'SSL Pesho'
);
GO

INSERT INTO Accounts VALUES
(
	1, 5.5
);
GO

SELECT * FROM Persons
SELECT * FROM Accounts
GO

CREATE PROCEDURE SelectPeople
AS
BEGIN
    SELECT FirstName + ' ' + LastName AS [Full Name] FROM Persons
END
GO
EXEC SelectPeople
GO

/*2 Create a stored procedure that accepts a number as a parameter
 and returns all persons who have more money in their accounts than the supplied number.*/
CREATE PROC GetPeopleWithMoreMoneyThan
	@money REAL
AS
BEGIN
	SELECT * FROM Persons p JOIN Accounts a ON p.Id = a.PersonId
	WHERE a.Balance > @money
END
GO
EXEC GetPeopleWithMoreMoneyThan 2
GO


/*3 Create a function that accepts as parameters – sum, yearly interest rate and number of months.
-It should calculate and return the new sum.
-Write a SELECT to test whether the function works as expected.*/

ALTER FUNCTION GetSum(@InitialSum INT, @YearlyInterestRate FLOAT, @MonthsKept INT)  
RETURNS MONEY 
AS    
BEGIN  
	DECLARE @monthlyAddition MONEY = (@InitialSum * @YearlyInterestRate) / 12;
	DECLARE @newSum MONEY = @InitialSum + @monthlyAddition * @MonthsKept;
	
    RETURN ROUND(@newSum, 2)
END;
GO

PRINT(dbo.GetSum(1000, 0.05, 1))
GO

/*4 Create a stored procedure that uses the function from the previous example to give an interest to a person's account for one month.
It should take the AccountId and the interest rate as parameters.*/

UPDATE Accounts
		SET Balance = 1000
		WHERE Id = 1
GO

CREATE PROC UpdateAccount(@AccountId INT, @InterestRate FLOAT)
AS
BEGIN
	UPDATE Accounts
		SET Balance = dbo.GetSum(Balance, @InterestRate, 1)
		WHERE Id = @AccountId
END
GO

EXEC UpdateAccount 1, 0.05
SELECT * FROM Accounts
GO

/*5 Add two more stored procedures WithdrawMoney(AccountId, money) and DepositMoney(AccountId, money) that operate in transactions.*/

ALTER PROC WithdrawMoney(@AccountId INT, @Amount MONEY)
AS
BEGIN
BEGIN TRANSACTION 
	DECLARE @currentBalance MONEY = 
		(SELECT Balance FROM Accounts WHERE Id = @AccountId);

	IF NOT EXISTS(SELECT * FROM Accounts WHERE Id = @AccountId)
		BEGIN
			RAISERROR('AccountId does not exist', 16,1)
			ROLLBACK
		END
	ELSE 
		BEGIN
			IF @Amount <= @currentBalance
				BEGIN
					UPDATE Accounts
						SET Balance = Balance - @Amount
						WHERE Id = @AccountId
					COMMIT
				END
			ELSE
				BEGIN
					RAISERROR('Not enough money', 16,1)
					ROLLBACK
				END
		END
END
GO

ALTER PROC DepositMoney(@AccountId INT, @Amount MONEY)
AS
BEGIN
BEGIN TRANSACTION 
	IF NOT EXISTS(SELECT * FROM Accounts WHERE Id = @AccountId)
		BEGIN
			RAISERROR('AccountId does not exist', 16,1)
			ROLLBACK
		END
	ELSE
		BEGIN
			UPDATE Accounts
				SET Balance = Balance + @Amount
				WHERE Id = @AccountId
				COMMIT
		END	
END
GO

SELECT * FROM Accounts
EXEC DepositMoney 1, 500
EXEC WithdrawMoney 1, 500
GO

/*6 Create another table – Logs(LogID, AccountID, OldSum, NewSum).
Add a trigger to the Accounts table that enters a new entry into the Logs table every time the sum on an account changes.*/

CREATE TABLE Logs
(
	[LogId] INT IDENTITY,
	[AccountId] INT NOT NULL,
	[OldSum] MONEY,--can be null in case of adding new account
	[NewSum] MONEY NOT NULL,
	CONSTRAINT PK_Logs_LogId PRIMARY KEY(LogId)
)
GO

ALTER TRIGGER Accounts_InsteadTrigger ON Accounts
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
	DECLARE @AccountId INT;
	DECLARE @PersonId INT;
	DECLARE @OldSum MONEY;
	DECLARE @NewSum MONEY;
	DECLARE @Action as char(1);
    SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                         AND EXISTS(SELECT * FROM DELETED)
                        THEN 'U'  -- Set Action to Updated.
                        WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN 'I'  -- Set Action to Insert.
                        WHEN EXISTS(SELECT * FROM DELETED)
                        THEN 'D'  -- Set Action to Deleted.
                        ELSE NULL -- Skip. It may have been a "failed delete".   
                    END)

	SELECT @AccountId = [Id] FROM inserted i;
	SELECT @PersonId = [PersonId] from inserted i;
	SELECT @NewSum = [Balance] FROM inserted i;
	SELECT @OldSum = (SELECT Balance FROM Accounts WHERE Id = @AccountId);

	IF @Action = 'U'
		BEGIN
			UPDATE Accounts
				SET Balance = @NewSum
			WHERE Id = @AccountId
		END
	ELSE IF @Action = 'I'
		BEGIN
			INSERT INTO Accounts VALUES
			(
				@PersonId, @NewSum
			);
		END	

	INSERT INTO Logs VALUES
	(
		@AccountId, @OldSum, @NewSum
	)
END;
GO

--testing
SELECT * FROM Accounts
SELECT * FROM Logs
EXEC DepositMoney 1, 500
EXEC WithdrawMoney 5, 500


INSERT INTO Accounts (PersonId, Balance) VALUES
(
	2, 5000
);
GO

/*7 Define a function in the database TelerikAcademy that returns all Employee's names
(first or middle or last name) and all town's names that are comprised of given set of letters.
Example: 'oistmiahf' will return 'Sofia', 'Smith', … but not 'Rob' and 'Guy'.*/
Use TelerikAcademy
GO
ALTER FUNCTION FindPeopleAndTownsContaining(@string NVARCHAR)
RETURNS @returnTable TABLE
(
	[People] NVARCHAR,
	[Towns] NVARCHAR
)
AS
BEGIN

	DECLARE @bla NVARCHAR = @string;
	
	--if letters from name can be found in string
	INSERT INTO @returnTable (People)
	SELECT FirstName FROM Employees WHERE LOWER(FirstName) LIKE 'oistmiahf'

	INSERT INTO @returnTable (Towns)
	SELECT Name FROM Towns WHERE LOWER(Name) LIKE 'oistmiahf'

	RETURN;
END;
GO

SELECT FirstName FROM Employees WHERE FirstName LIKE ''
SELECT FirstName FROM Employees
--TO BE CONTINUED, PROBLEM IS IN THE REGEX
SELECT * FROM FindPeopleAndTownsContaining('oistmiahf')
GO
/*8 Using database cursor write a T-SQL script that scans all employees and 
their addresses and prints all pairs of employees that live in the same town.*/
--find employees from each town(that's how I understand it)
--Skipping this task on the ground that I'm probably not going to need it
GO
/*9 Write a T-SQL script that shows for each town a list of all employees that live in it.
Sample output:
Sofia -> Martin Kulov, George Denchev
Ottawa -> Jose Saraiva
…*/

/*1 Define a .NET aggregate function StrConcat that takes as input a sequence of strings and return a single string that consists of the input strings separated by ','.
For example the following SQL statement should return a single string:
SELECT StrConcat(FirstName + ' ' + LastName)
FROM Employees*/