T-SQL (Transact-SQL) beginner's tutorial( AI generated )

T-SQL (Transact-SQL) is Microsoft's extension of SQL (Structured Query Language) used in SQL Server. It adds procedural programming, error handling, and transaction control to standard SQL.

1. Introduction to T-SQL

• SQL vs. T-SQL:
  • SQL is a standard language for managing relational databases.
  • T-SQL extends SQL with procedural features like variables, loops, and error handling.

2. Basic Querying

SELECT Statement

SELECT column1, column2 FROM TableName;

• Retrieve specific columns from a table.

WHERE Clause

SELECT * FROM Employees WHERE Age > 30;

• Filters rows based on conditions.

ORDER BY Clause

SELECT * FROM Employees ORDER BY LastName ASC;

• Sorts results in ascending or descending order.

GROUP BY and HAVING

SELECT Department, COUNT(*) AS EmployeeCount 
FROM Employees 
GROUP BY Department 
HAVING COUNT(*) > 5;

• Groups data and filters grouped results.

---

3. Data Manipulation (DML)

INSERT

INSERT INTO Employees (FirstName, LastName, Age) 
VALUES ('John', 'Doe', 28);

UPDATE

UPDATE Employees 
SET Age = 29 
WHERE FirstName = 'John' AND LastName = 'Doe';

DELETE

DELETE FROM Employees WHERE Age < 25;

---

4. Data Definition (DDL)

Creating Tables

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Age INT,
    DepartmentID INT
);

Altering Tables

ALTER TABLE Employees ADD Salary DECIMAL(10,2);

Dropping Tables

DROP TABLE Employees;

---

5. Stored Procedures

CREATE PROCEDURE GetEmployeesByDepartment
    @DeptID INT
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @DeptID;
END;

• Use: EXEC GetEmployeesByDepartment @DeptID = 2;

---

6. Functions

Scalar Function

CREATE FUNCTION GetFullName(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

• Use: SELECT dbo.GetFullName('John', 'Doe');

---

7. Transactions

BEGIN TRANSACTION;
UPDATE Employees SET Age = Age + 1 WHERE EmployeeID = 1;
COMMIT TRANSACTION;

• Ensures atomic operations.

---

8. Error Handling

BEGIN TRY
    UPDATE Employees SET Age = 'ABC' WHERE EmployeeID = 1; -- This will cause an error
END TRY
BEGIN CATCH
    PRINT 'An error occurred!';
END CATCH;

---

9. Common Table Expressions (CTE)

WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName, Age FROM Employees WHERE Age > 30
)
SELECT * FROM EmployeeCTE;

• Creates temporary query results.

---

10. Advanced Topics

• Cursors: Row-by-row processing.
• Triggers: Automatic execution on DML events.
• Indexes: Optimize query performance.
• Views: Saved queries for easier access.

Would you like to focus on a particular area, or do you prefer hands-on exercises?

( AI Chatgpt generated )