SQL Server T-SQL Tutorial

Welcome to this comprehensive tutorial on SQL Server's Transact-SQL (T-SQL). T-SQL is Microsoft's proprietary extension to SQL, used for managing and querying data in SQL Server databases.

Getting Started with T-SQL

T-SQL is essential for anyone working with Microsoft SQL Server. It allows you to perform a wide range of operations, from simple data retrieval to complex database administration.

What is SQL?

SQL (Structured Query Language) is a standard language for managing and manipulating databases. T-SQL builds upon this standard.

What is T-SQL?

T-SQL adds procedural programming, local variables, various support functions for string processing, date processing, mathematical functions, and more to SQL.

Your First T-SQL Query

Let's start with a basic query to select data from a hypothetical table named Products.

                    
-- This is a comment in T-SQL
SELECT ProductName, Price
FROM Products
WHERE Price > 50;

In this query:

SELECT ProductName, Price specifies the columns you want to retrieve.
FROM Products indicates the table you are querying.
WHERE Price > 50 is a filter to only return rows where the price is greater than 50.

Creating Tables

You can define your own tables using the CREATE TABLE statement.

                    
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATE DEFAULT GETDATE()
);
                    
                

This statement creates a table named Customers with several columns, including:

CustomerID: An auto-incrementing integer, serving as the primary key.
FirstName, LastName: Required string fields.
Email: A unique string field for email addresses.
RegistrationDate: A date field that defaults to the current date.

Inserting Data

To add new records to a table, use the INSERT INTO statement.

                    
INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Doe', 'john.doe@example.com');

Updating Data

Modify existing records with the UPDATE statement.

                    
UPDATE Products
SET Price = Price * 1.10
WHERE Category = 'Electronics';

This example increases the price of all products in the 'Electronics' category by 10%.

Deleting Data

Remove records using the DELETE FROM statement.

                    
DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

This deletes all orders placed before January 1st, 2023.

Conclusion

This tutorial covered the basics of T-SQL, including querying, creating, inserting, updating, and deleting data. T-SQL is a powerful language with many more features to explore. Continue your learning journey with more advanced topics such as JOINs, subqueries, stored procedures, and functions.

For more detailed information, please refer to the official SQL Server documentation.