Basics of T-SQL: A Comprehensive Guide

Transact-SQL (T-SQL) is a powerful language used for interacting with Microsoft SQL Server databases. Here, we will cover the fundamental concepts of T-SQL, including data retrieval, data manipulation, and query optimization. We’ll provide 20 code examples along with detailed explanations to help you grasp the core concepts and get started with T-SQL.

  1. SELECT Statement:
    The SELECT statement is used to retrieve data from one or more tables.
    Example:
SQL
SELECT * FROM Customers;

Explanation: This query retrieves all columns from the “Customers” table, returning all rows.

  1. WHERE Clause:
    The WHERE clause is used to filter data based on specific conditions.
    Example:
SQL
SELECT * FROM Orders WHERE OrderDate >= '2022-01-01';

Explanation: This query selects all columns from the “Orders” table where the “OrderDate” is greater than or equal to January 1, 2022.

  1. INSERT INTO Statement:
    The INSERT INTO statement is used to add new rows to a table.
    Example:
SQL
INSERT INTO Customers (CustomerName, City) VALUES ('John Doe', 'New York');

Explanation: This query inserts a new customer with the provided name and city into the “Customers” table.

  1. UPDATE Statement:
    The UPDATE statement modifies existing data in a table.
    Example:
SQL
UPDATE Customers SET City = 'Los Angeles' WHERE CustomerID = 1;

Explanation: This query updates the “City” column of the customer with “CustomerID” 1 to ‘Los Angeles’.

  1. DELETE Statement:
    The DELETE statement removes rows from a table based on specified conditions.
    Example:
SQL
DELETE FROM Customers WHERE CustomerID = 1;

Explanation: This query deletes the customer with “CustomerID” 1 from the “Customers” table.

  1. ORDER BY Clause:
    The ORDER BY clause is used to sort the result set in ascending or descending order.
    Example:
SQL
SELECT * FROM Products ORDER BY Price DESC;

Explanation: This query selects all columns from the “Products” table and sorts the result set by the “Price” column in descending order.

  1. JOINs:
    JOINs are used to combine rows from two or more tables based on a related column.
    Example:
SQL
SELECT Orders.OrderID, Customers.CustomerName 
FROM Orders 
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Explanation: This query retrieves the “OrderID” column from the “Orders” table and the “CustomerName” column from the “Customers” table, joining them on the “CustomerID” column.

  1. GROUP BY Clause:
    The GROUP BY clause is used to group rows based on specified columns, often used with aggregate functions like SUM or COUNT.
    Example:
SQL
SELECT CategoryID, COUNT(*) 
FROM Products 
GROUP BY CategoryID;

Explanation: This query counts the number of products in each category by grouping the rows based on the “CategoryID” column.

  1. HAVING Clause:
    The HAVING clause filters grouped data based on conditions.
    Example:
SQL
SELECT CategoryID, COUNT(*) 
FROM Products 
GROUP BY CategoryID 
HAVING COUNT(*) > 10;

Explanation: This query groups the products by “CategoryID” and returns only those categories that have more than 10 products.

  1. DISTINCT Keyword:
    The DISTINCT keyword eliminates duplicate rows from the result set.
    Example:
SQL
SELECT DISTINCT Country FROM Customers;

Explanation: This query retrieves distinct countries from the “Customers” table, eliminating duplicate entries.

  1. TOP Clause:
    The TOP clause is used to limit the number of rows returned in a query.
    Example:
SQL
SELECT TOP 5 * FROM Orders;

Explanation: This query retrieves the top 5 rows from the “Orders” table.

  1. LIKE Operator:
    The LIKE operator is used to search for a specified pattern in a column.
    Example:
SQL
SELECT * FROM Customers WHERE CustomerName LIKE 'A%';

Explanation: This query selects all customers whose names start with ‘A’.

  1. IN Operator:
    The IN operator allows you to specify multiple values in a WHERE clause.
    Example:
SQL
SELECT * FROM Customers WHERE Country IN ('USA', 'Canada');

Explanation: This query selects all customers from the USA or Canada.

  1. BETWEEN Operator:
    The BETWEEN operator selects values within a specified range.
    Example:
SQL
SELECT * FROM Orders WHERE OrderDate BETWEEN '2022-01-01' AND '2022-12-31';

Explanation: This query selects orders with OrderDate between January 1, 2022, and December 31, 2022.

  1. EXISTS Operator:
    The EXISTS operator checks if a subquery returns any rows.
    Example:
SQL
SELECT * FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID);

Explanation: This query selects customers who have at least one order in the “Orders” table.

  1. NULL Values:
    You can use IS NULL or IS NOT NULL to check for NULL values in a column.
    Example:
SQL
SELECT * FROM Customers WHERE ContactName IS NULL;

Explanation: This query selects customers with a NULL ContactName.

  1. UNION Operator:
    The UNION operator combines the result of two or more SELECT statements.
    Example:
SQL
SELECT CustomerName FROM Customers WHERE Country = 'USA'
UNION
SELECT CustomerName FROM Customers WHERE Country = 'Canada';

Explanation: This query retrieves customer names from the USA and Canada, eliminating duplicate entries.

  1. EXISTS vs. IN:
    You can use EXISTS or IN to check for the existence of values returned by a subquery.
    Example:
SQL
SELECT * FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders);

Explanation: This query selects customers who have placed an order.

  1. COUNT Function:
    The COUNT function returns the number of rows that match a specified condition.
    Example:
SQL
SELECT COUNT(*) FROM Customers;

Explanation: This query returns the total number of rows in the “Customers” table.

  1. MAX and MIN Functions:
    The MAX and MIN functions return the maximum and minimum values in a column, respectively.
    Example:
SQL
SELECT MAX(Price) FROM Products;

Explanation: This query returns the maximum value in the “Price” column of the “Products” table.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top