SQL Query Language

Introduction to T-SQL

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the standard SQL language used by Microsoft SQL Server. It adds procedural programming capabilities, local variables, functions, and command processing logic to SQL.

T-SQL is the primary language for interacting with and managing SQL Server databases. It allows you to perform data manipulation (INSERT, UPDATE, DELETE), data retrieval (SELECT), and data definition (CREATE, ALTER, DROP) operations.

Core SQL Commands

Here are some of the fundamental SQL commands you'll use daily:

SELECT Statement

Used to query the database and retrieve records that match specified criteria.

SELECT column1, column2
FROM table_name
WHERE condition;

INSERT INTO Statement

Used to add new records into a table.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

UPDATE Statement

Used to update existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE Statement

Used to delete existing records from a table.

DELETE FROM table_name
WHERE condition;

Advanced T-SQL Concepts

Beyond basic CRUD operations, T-SQL offers powerful features for complex data management:

• JOIN Operations
• Aggregate Functions
• Subqueries
• Common Table Expressions (CTEs)
• Window Functions
• Transactions and Concurrency Control

JOIN Operations

JOIN clauses are used to combine rows from two or more tables based on a related column between them. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

SELECT o.OrderID, c.CustomerName
FROM Orders AS o
INNER JOIN Customers AS c
ON o.CustomerID = c.CustomerID;

Aggregate Functions

These functions perform a calculation on a set of values and return a single scalar value. Examples include COUNT, SUM, AVG, MIN, and MAX.

SELECT COUNT(DISTINCT Country) AS NumberOfCountries
FROM Customers;

Subqueries

A subquery is a query nested inside another query. They can be used in WHERE, FROM, and SELECT clauses.

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID
                                    FROM Orders
                                    WHERE OrderDate = '2023-10-27');

Common Table Expressions (CTEs)

CTEs are temporary, named result sets that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, DELETE). They improve readability for complex queries.

WITH CustomerOrderCounts (CustomerID, OrderCount)
AS
(
    SELECT CustomerID, COUNT(*)
    FROM Orders
    GROUP BY CustomerID
)
SELECT c.CustomerName, coc.OrderCount
FROM Customers c
JOIN CustomerOrderCounts coc ON c.CustomerID = coc.CustomerID
WHERE coc.OrderCount > 5;

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. They are similar to aggregate functions but do not cause rows to be grouped into a single output row.

SELECT ProductName,
       SalesAmount,
       ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS Rank
FROM ProductSales;

Transactions and Concurrency Control

Transactions ensure data integrity by grouping a sequence of operations into a single logical unit of work. T-SQL provides commands like BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 101;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 102;
COMMIT TRANSACTION; -- or ROLLBACK TRANSACTION to undo