Skip to content

Instantly share code, notes, and snippets.

@zenVentzi

zenVentzi/TSQL_09 Telerik

Created November 21, 2017 19:32
Show Gist options
  • Save zenVentzi/87f34107f4b611874ef2c03069ac8426 to your computer and use it in GitHub Desktop.
Save zenVentzi/87f34107f4b611874ef2c03069ac8426 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment