Comprehensive T-SQL Script with Examples
Below is a large T-SQL script illustrating basic to advanced query syntax and features, organized into
sections. It uses sample tables (e.g. Users , Accounts , Transactions , etc.) and includes examples
of SELECT queries, JOINs, subqueries, CTEs, window functions, views, indexes, transactions, triggers,
functions, and stored procedures. Comments ( -- ) explain each part. Feel free to adapt table and
column names to your own schema.
--------------------------------------------------------------------------------
-- DATABASE & TABLES SETUP
-- Example schema setup based on the FinalPrep database (simplified).
-- Create a sample database and tables if they don't exist.
-- (Optional) Create a new database
-- CREATE DATABASE FinalPrep;
-- GO
-- USE FinalPrep;
-- GO
-- Create Users table
CREATE TABLE Users (
UserID INT IDENTITY(1,1) PRIMARY KEY,
UserName NVARCHAR(50) NOT NULL,
Email NVARCHAR(100) NOT NULL UNIQUE,
CreatedDate DATETIME2 DEFAULT GETDATE()
);
-- Create Accounts table
CREATE TABLE Accounts (
AccountID INT IDENTITY(1,1) PRIMARY KEY,
UserID INT NOT NULL,
AccountNumber NVARCHAR(20) NOT NULL UNIQUE,
Balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
Status CHAR(1) NOT NULL DEFAULT 'A', -- A=Active, I=Inactive
CreatedDate DATETIME2 DEFAULT GETDATE(),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
-- Create Transactions table (banking transactions)
CREATE TABLE Transactions (
TransactionID INT IDENTITY(1,1) PRIMARY KEY,
AccountID INT NOT NULL,
TransactionDate DATETIME2 NOT NULL DEFAULT GETDATE(),
Amount DECIMAL(10,2) NOT NULL,
Description NVARCHAR(200),
FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID)
1
);
-- Create Loans table
CREATE TABLE Loans (
LoanID INT IDENTITY(1,1) PRIMARY KEY,
AccountID INT NOT NULL,
LoanAmount DECIMAL(10,2) NOT NULL,
InterestRate DECIMAL(5,2) NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE,
FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID)
);
-- Create an Audit_Logs table
CREATE TABLE Audit_Logs (
LogID INT IDENTITY(1,1) PRIMARY KEY,
EventDate DATETIME2 NOT NULL DEFAULT GETDATE(),
EventType NVARCHAR(50),
EventDescription NVARCHAR(200)
);
-- Insert sample data into Users and Accounts for demonstration
INSERT INTO Users (UserName, Email) VALUES
('Alice','alice@[Link]'),
('Bob','bob@[Link]'),
('Charlie','charlie@[Link]');
INSERT INTO Accounts (UserID, AccountNumber, Balance) VALUES
(1, 'ACC1001', 1000.00),
(1, 'ACC1002', 500.00),
(2, 'ACC2001', 750.00),
(3, 'ACC3001', 300.00);
Basic Queries (SELECT, INSERT, UPDATE, DELETE)
• SELECT: Retrieve data with filters, sorting, and projection.
-- Select all columns from Users
SELECT * FROM Users;
-- Select specific columns with a WHERE clause
SELECT UserID, UserName, Email
FROM Users
WHERE UserName LIKE 'A%'; -- names starting with 'A'
-- Use ORDER BY to sort results
SELECT UserID, UserName, Email
FROM Users
ORDER BY CreatedDate DESC;
-- Use DISTINCT to remove duplicates (if any)
2
SELECT DISTINCT Status FROM Accounts;
-- LIMIT or TOP (T-SQL uses TOP) to get a subset
SELECT TOP 2 * FROM Accounts ORDER BY Balance DESC;
• INSERT: Add new rows.
-- Single-row INSERT
INSERT INTO Users (UserName, Email)
VALUES ('David', 'david@[Link]');
-- Multiple-row INSERT (T-SQL supports this form)
INSERT INTO Users (UserName, Email)
VALUES
('Eve', 'eve@[Link]'),
('Frank', 'frank@[Link]');
• UPDATE: Modify existing data.
-- Update a single row
UPDATE Accounts
SET Balance = Balance + 100.00
WHERE AccountNumber = 'ACC1001';
-- Update multiple rows with a condition
UPDATE Accounts
SET Status = 'I'
WHERE Balance = 0.00; -- mark zero-balance accounts as Inactive
• DELETE: Remove rows.
-- Delete a specific user by ID
DELETE FROM Users
WHERE UserID = 5; -- assuming user with ID 5 exists
-- Delete all transactions before a certain date
DELETE FROM Transactions
WHERE TransactionDate < '2025-01-01';
Joins and Set Operations
• INNER JOIN: Combine rows matching a condition in both tables.
-- Inner join Users and Accounts to get user's accounts
SELECT [Link], [Link], [Link]
FROM Users AS U
INNER JOIN Accounts AS A
ON [Link] = [Link];
3
-- Using table aliases for brevity
• LEFT (OUTER) JOIN: All rows from left table, with matching (or NULL) from right.
-- Left join to include users who may have no accounts
SELECT [Link], [Link]
FROM Users U
LEFT JOIN Accounts A
ON [Link] = [Link];
• RIGHT JOIN: All rows from right table, with matching (or NULL) from left.
-- Right join to include all accounts even if user is missing (rare scenario)
SELECT [Link], [Link]
FROM Users U
RIGHT JOIN Accounts A
ON [Link] = [Link];
• FULL OUTER JOIN: All rows when there is a match in one of the tables.
SELECT [Link], [Link]
FROM Users U
FULL OUTER JOIN Accounts A
ON [Link] = [Link];
• CROSS JOIN: Cartesian product (use with caution!).
SELECT [Link], [Link]
FROM Users U
CROSS JOIN Accounts A;
-- Returns every combination of user and account (useful for generating test
data)
• UNION: Combine results of two queries with the same columns.
-- Select all email addresses from Users or Accounts (assuming Accounts had
emails too)
SELECT Email FROM Users
UNION
SELECT Email FROM Accounts; -- hypothetical, if Accounts had Email field
Subqueries and Common Table Expressions (CTEs)
• Scalar Subquery in SELECT:
4
-- Get each user's total account balance using a subquery
SELECT
[Link],
(SELECT SUM(Balance) FROM Accounts WHERE UserID = [Link]) AS
TotalBalance
FROM Users U;
• Correlated Subquery in WHERE:
-- Find users with at least one account over $800
SELECT UserID, UserName
FROM Users U
WHERE EXISTS (
SELECT 1 FROM Accounts A
WHERE [Link] = [Link]
AND [Link] > 800
);
• Subquery in FROM (Derived Table):
-- Total and average balance per user using a subquery in FROM
SELECT [Link], [Link], [Link]
FROM (
SELECT UserID, SUM(Balance) AS TotalBalance, AVG(Balance) AS AvgBalance
FROM Accounts
GROUP BY UserID
) AS UA;
• Common Table Expression (CTE): Improves readability and can be recursive.
-- Example: CTE to list all accounts and their average transaction amount
WITH AccountStats AS (
SELECT
[Link],
COUNT(*) AS NumTransactions,
AVG(Amount) AS AvgTransaction
FROM Transactions T
GROUP BY [Link]
)
SELECT
[Link],
[Link],
[Link]
FROM Accounts A
LEFT JOIN AccountStats S
ON [Link] = [Link];
5
Aggregate Functions and GROUP BY
• Using GROUP BY with aggregates like SUM , COUNT , MAX , MIN , and HAVING .
-- Total balance per user
SELECT [Link], SUM([Link]) AS TotalBalance
FROM Users U
JOIN Accounts A ON [Link] = [Link]
GROUP BY [Link];
-- Count of active accounts and inactive accounts
SELECT
SUM(CASE WHEN Status='A' THEN 1 ELSE 0 END) AS ActiveAccounts,
SUM(CASE WHEN Status='I' THEN 1 ELSE 0 END) AS InactiveAccounts
FROM Accounts;
-- Use HAVING to filter groups
SELECT [Link], SUM([Link]) AS TotalBalance
FROM Users U
JOIN Accounts A ON [Link] = [Link]
GROUP BY [Link]
HAVING SUM([Link]) > 1000;
• Window Functions: Perform calculations across rows related to the current row (using OVER ).
-- ROW_NUMBER: rank transactions by amount per account
SELECT
TransactionID, AccountID, Amount,
ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY Amount DESC) AS RowNum
FROM Transactions;
-- Running total of balances for each user (ordered by AccountID)
SELECT
[Link], [Link], [Link],
SUM([Link]) OVER (PARTITION BY [Link] ORDER BY [Link]
ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Accounts A;
Views
• CREATE VIEW: Virtual table representing a stored query.
-- View: User account summary (total balance per user)
CREATE VIEW vw_UserAccountSummary AS
SELECT
[Link],
[Link],
SUM([Link]) AS TotalBalance
6
FROM Users U
LEFT JOIN Accounts A ON [Link] = [Link]
GROUP BY [Link], [Link];
-- Use the view like a table
SELECT * FROM vw_UserAccountSummary
WHERE TotalBalance > 1000;
-- Updating view: Not all views are updatable; depends on view definition.
• ALTER VIEW or DROP VIEW as needed (not shown here).
Indexes
• Improve performance by creating indexes on frequently searched columns.
-- Non-unique index on Accounts(UserID) to speed up joins/filtering
CREATE INDEX IX_Accounts_UserID ON Accounts(UserID);
-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX IX_Accounts_AccountNumber ON Accounts(AccountNumber);
-- Filtered index: only on active accounts (SQL Server specific)
CREATE INDEX IX_Accounts_UserID_Active
ON Accounts(UserID)
WHERE Status = 'A';
-- View current indexes
EXEC sp_helpindex 'Accounts';
Transactions and Error Handling
• Use transactions to ensure multiple statements commit or roll back together.
-- Example: Transfer money from one account to another
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @FromAccountID INT = 1;
DECLARE @ToAccountID INT = 2;
DECLARE @Amount DECIMAL(10,2) = 100.00;
-- Debit from source account
UPDATE Accounts
SET Balance = Balance - @Amount
WHERE AccountID = @FromAccountID;
-- Credit to destination account
UPDATE Accounts
7
SET Balance = Balance + @Amount
WHERE AccountID = @ToAccountID;
-- Insert transaction records
INSERT INTO Transactions (AccountID, Amount, Description)
VALUES
(@FromAccountID, -@Amount, 'Transfer to account ' +
CAST(@ToAccountID AS NVARCHAR(10))),
(@ToAccountID, @Amount, 'Transfer from account ' +
CAST(@FromAccountID AS NVARCHAR(10)));
COMMIT; -- If all statements succeed, commit the transaction
END TRY
BEGIN CATCH
ROLLBACK; -- If any statement fails, roll back all changes
-- Optionally, log or re-throw error
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State,
ERROR_PROCEDURE() AS ProcedureName,
ERROR_LINE() AS Line,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
• Savepoints (optional): You can use SAVE TRANSACTION for partial rollbacks within a
transaction.
Triggers
• Automatically execute code in response to table events (INSERT/UPDATE/DELETE).
-- After-insert trigger on Transactions: log activity
CREATE TRIGGER trg_AfterInsert_Transactions
ON Transactions
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Audit_Logs (EventType, EventDescription)
SELECT
'TransactionInserted',
'TransactionID=' + CAST([Link] AS NVARCHAR(10))
+ ', AccountID=' + CAST([Link] AS NVARCHAR(10))
+ ', Amount=' + CAST([Link] AS NVARCHAR(10))
FROM inserted i;
END;
GO
-- Example insert to fire the trigger
8
INSERT INTO Transactions (AccountID, Amount, Description)
VALUES (1, 50.00, 'Deposit');
-- Check audit log
SELECT * FROM Audit_Logs WHERE EventType = 'TransactionInserted';
• Update/Delete triggers can also use deleted and inserted pseudo-tables. For example,
an AFTER UPDATE trigger could log changes or enforce rules.
User-Defined Functions
• Scalar Function: Returns a single value.
-- Calculate simple interest given principal and rate
CREATE FUNCTION [Link](
@Principal DECIMAL(10,2),
@Rate DECIMAL(5,2) -- e.g., 5.5 for 5.5%
)
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Principal * @Rate / 100;
END;
GO
-- Usage:
SELECT [Link](1000.00, 5.5) AS Interest; -- returns 55.00
• Inline Table-Valued Function: Returns a table.
-- Return all accounts for a given user
CREATE FUNCTION [Link](@UserID INT)
RETURNS TABLE
AS
RETURN
(
SELECT AccountID, AccountNumber, Balance
FROM Accounts
WHERE UserID = @UserID
);
GO
-- Use the function in a query
SELECT * FROM [Link](1);
• Multi-Statement Table-Valued Function (when more complex logic needed):
CREATE FUNCTION [Link](@AccountID INT)
RETURNS @Result TABLE (
9
AccountID INT,
AccountNumber NVARCHAR(20),
UserName NVARCHAR(50),
Balance DECIMAL(10,2)
)
AS
BEGIN
INSERT INTO @Result
SELECT
[Link],
[Link],
[Link],
[Link]
FROM Accounts A
JOIN Users U ON [Link] = [Link]
WHERE [Link] = @AccountID;
RETURN;
END;
GO
-- Use it:
SELECT * FROM [Link](1);
Stored Procedures
• Encapsulate business logic, can accept parameters, and return result sets or output parameters.
-- Simple stored procedure: add a new loan for an account
CREATE PROCEDURE [Link]
@AccountID INT,
@LoanAmount DECIMAL(10,2),
@InterestRate DECIMAL(5,2)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Loans (AccountID, LoanAmount, InterestRate, StartDate)
VALUES (@AccountID, @LoanAmount, @InterestRate, GETDATE());
END;
GO
-- Execute the stored procedure
EXEC [Link] @AccountID = 1, @LoanAmount = 5000, @InterestRate = 4.5;
-- Stored procedure with output parameter and error handling
CREATE PROCEDURE [Link]
@FromAccount INT,
@ToAccount INT,
@Amount DECIMAL(10,2),
10
@Success BIT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @Success = 0; -- default to failure
BEGIN TRY
BEGIN TRANSACTION;
-- Simple balance check
DECLARE @FromBalance DECIMAL(10,2);
SELECT @FromBalance = Balance FROM Accounts WHERE AccountID =
@FromAccount;
IF @FromBalance IS NULL OR @FromBalance < @Amount
BEGIN
THROW 51000, 'Insufficient funds or account not found.', 1;
END
-- Perform updates
UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID =
@FromAccount;
UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID =
@ToAccount;
INSERT INTO Transactions (AccountID, Amount, Description)
VALUES (@FromAccount, -@Amount, 'Transfer to account ' +
CAST(@ToAccount AS NVARCHAR(10))),
(@ToAccount, @Amount, 'Transfer from account ' +
CAST(@FromAccount AS NVARCHAR(10)));
COMMIT;
SET @Success = 1; -- mark success
END TRY
BEGIN CATCH
ROLLBACK;
-- You can log the error or return the message
SELECT ERROR_MESSAGE() AS ErrorMessage;
SET @Success = 0;
END CATCH;
END;
GO
-- Execute the procedure and get the success flag
DECLARE @IsSuccess BIT;
EXEC [Link] @FromAccount=1, @ToAccount=2, @Amount=50,
@Success=@IsSuccess OUTPUT;
SELECT @IsSuccess AS TransferSucceeded;
11
This script demonstrates a wide range of T-SQL features in context. Each section can be tested and
expanded as needed. Ensure you have the necessary permissions to create tables, procedures, and
triggers before running this script.
12