Here’s a breakdown of common T-SQL (Transact-SQL) commands with examples to help
you understand how each command works. T-SQL is an extension of SQL that adds
procedural programming capabilities to the standard SQL language.
1. SELECT: Retrieve Data from a Table
The SELECT statement is used to query data from a table.
Example:
SELECT FirstName, LastName
FROM Employees
WHERE Department = 'Sales';
• This query retrieves the FirstName and LastName of employees who work in the
"Sales" department.
2. INSERT: Insert Data into a Table
The INSERT statement adds new rows to a table.
Example:
INSERT INTO Employees (FirstName, LastName, Department, HireDate)
VALUES ('John', 'Doe', 'HR', '2025-03-01');
• This query inserts a new employee into the Employees table with specific values for
FirstName, LastName, Department, and HireDate.
3. UPDATE: Modify Existing Data
The UPDATE statement is used to modify existing records in a table.
Example:
UPDATE Employees
SET Department = 'Marketing'
WHERE EmployeeID = 101;
• This query updates the department of the employee with EmployeeID 101 to
'Marketing'.
4. DELETE: Remove Data from a Table
The DELETE statement is used to remove rows from a table.
Example:
DELETE FROM Employees
WHERE EmployeeID = 101;
• This query deletes the employee with EmployeeID 101 from the Employees table.
5. CREATE TABLE: Create a New Table
The CREATE TABLE statement is used to define a new table structure in the database.
Example:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL,
ManagerID INT
);
• This query creates a new Departments table with three columns: DepartmentID,
DepartmentName, and ManagerID.
6. ALTER TABLE: Modify an Existing Table
The ALTER TABLE statement is used to modify an existing table's structure, such as adding or
deleting columns.
Example (Add a New Column):
ALTER TABLE Employees
ADD Email VARCHAR(100);
• This query adds a new column Email to the Employees table.
Example (Modify Column Data Type):
ALTER TABLE Employees
ALTER COLUMN PhoneNumber VARCHAR(20);
• This query changes the PhoneNumber column's data type to VARCHAR(20).
7. DROP TABLE: Delete an Existing Table
The DROP TABLE statement is used to delete a table and its data permanently.
Example:
DROP TABLE Departments;
• This query deletes the Departments table from the database.
8. CREATE PROCEDURE: Define a Stored Procedure
A stored procedure is a group of SQL statements that can be executed as a single unit.
Example:
CREATE PROCEDURE GetEmployeeInfo
@EmployeeID INT
AS
BEGIN
SELECT FirstName, LastName, Department
FROM Employees
WHERE EmployeeID = @EmployeeID;
END;
• This query creates a stored procedure called GetEmployeeInfo that accepts an
EmployeeID as input and retrieves the FirstName, LastName, and Department for
that employee.
9. EXECUTE: Run a Stored Procedure
The EXECUTE command runs a stored procedure.
Example:
EXEC GetEmployeeInfo @EmployeeID = 101;
• This query executes the GetEmployeeInfo stored procedure and passes EmployeeID
101 as an argument.
10. CREATE FUNCTION: Define a User-Defined Function
A user-defined function (UDF) allows you to create custom functions to encapsulate
repetitive logic.
Example:
CREATE FUNCTION GetEmployeeFullName (@EmployeeID INT)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @FullName VARCHAR(200);
SELECT @FullName = FirstName + ' ' + LastName
FROM Employees
WHERE EmployeeID = @EmployeeID;
RETURN @FullName;
END;
• This query creates a function GetEmployeeFullName that takes an EmployeeID as
input and returns the full name of the employee.
11. IF...ELSE: Conditional Logic
The IF...ELSE statement is used to perform conditional logic.
Example:
DECLARE @Salary INT = 50000;
IF @Salary > 40000
PRINT 'High salary'
ELSE
PRINT 'Low salary';
• This query checks if @Salary is greater than 40,000 and prints 'High salary' or 'Low
salary' accordingly.
12. WHILE: Looping Structure
The WHILE loop is used to repeatedly execute a block of SQL code as long as a specified
condition is true.
Example:
DECLARE @Counter INT = 1;
WHILE @Counter <= 5
BEGIN
PRINT 'Counter value: ' + CAST(@Counter AS VARCHAR);
SET @Counter = @Counter + 1;
END;
• This query prints the counter values from 1 to 5.
13. TRY...CATCH: Error Handling
The TRY...CATCH block is used to handle errors in T-SQL.
Example:
BEGIN TRY
-- Attempt to divide by zero (error)
SELECT 10 / 0;
END TRY
BEGIN CATCH
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
• This query attempts to divide by zero, which causes an error. The error is caught in
the CATCH block, and an error message is printed.
14. TRANSACTIONS: Managing Transactions
Transactions ensure that a group of SQL statements are executed as a single unit.
Example:
• BEGIN TRANSACTION;
•
• UPDATE Employees
• SET Salary = Salary + 5000
• WHERE Department = 'HR';
•
• INSERT INTO Transactions (EmployeeID, Amount)
• VALUES (101, 5000);
•
• -- Simulate an error
• -- ROLLBACK TRANSACTION; -- Uncomment this to roll back the changes
•
• COMMIT TRANSACTION;.
15. JOIN: Combine Data from Multiple Tables
The JOIN command is used to combine rows from two or more tables based on a related
column between them.
Example (INNER JOIN):
SELECT [Link], [Link], [Link]
FROM Employees
INNER JOIN Departments ON [Link] =
[Link];
• This query retrieves the first name, last name, and department name for each
employee by joining the Employees and Departments tables.
Summary:
• T-SQL (Transact-SQL) is an extension of SQL that allows you to use procedural
programming, error handling, and advanced logic.
