T-SQL Querying Essentials

Querying is the fundamental operation in SQL. It involves retrieving specific data from one or more tables based on defined criteria. Transact-SQL (T-SQL) provides powerful and flexible ways to interact with your data.

The `SELECT` Statement

The `SELECT` statement is the cornerstone of data retrieval in T-SQL. It allows you to specify which columns to retrieve and from which table(s).

Basic Syntax

To retrieve all columns from a table:

SELECT * FROM YourTableName;

To retrieve specific columns:

SELECT Column1, Column2 FROM YourTableName;

Filtering Data with `WHERE`

The `WHERE` clause is used to filter records, allowing you to specify conditions that must be met for a record to be included in the result set. This is crucial for retrieving only the relevant data.

Using `WHERE`

Retrieve rows where a specific column matches a value:

SELECT ProductName, Price FROM Products WHERE Category = 'Electronics';

Using comparison operators:

SELECT OrderID, OrderDate FROM Orders WHERE TotalAmount > 1000;

Combining conditions with `AND` and `OR`:

                SELECT FirstName, LastName FROM Employees WHERE Department = 'Sales' AND Salary > 50000;
            

SELECT CustomerName FROM Customers WHERE City = 'London' OR City = 'Paris';

Sorting Results with `ORDER BY`

The `ORDER BY` clause is used to sort the result set in ascending (`ASC`) or descending (`DESC`) order based on one or more columns.

Sorting Examples

Sort by a single column in ascending order (default):

SELECT ProductName, Price FROM Products ORDER BY Price;

Sort by a single column in descending order:

SELECT ProductName, Price FROM Products ORDER BY Price DESC;

Sort by multiple columns:

                SELECT LastName, FirstName, HireDate FROM Employees ORDER BY Department ASC, LastName ASC;
            

Limiting Results with `TOP` (T-SQL Specific)

T-SQL uses the `TOP` clause to limit the number of rows returned by a query. This is often used for retrieving the "top N" records.

Using `TOP`

Retrieve the top 10 most expensive products:

SELECT TOP 10 ProductName, Price FROM Products ORDER BY Price DESC;

Retrieve the top 5% of employees by salary:

                SELECT TOP 5 PERCENT FirstName, LastName, Salary FROM Employees ORDER BY Salary DESC;
            

Subqueries (Nested Queries)

Subqueries are queries nested inside another SQL query. They can be used in the `WHERE`, `FROM`, or `SELECT` clauses to perform operations that require multiple steps.

Subquery Examples

Find employees whose salary is greater than the average salary:

                SELECT FirstName, LastName, Salary FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees );
            

Using subqueries in the `FROM` clause (derived tables):

                SELECT DepartmentName, AvgSalary FROM ( SELECT d.Name AS DepartmentName, AVG(e.Salary) AS AvgSalary FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID GROUP BY d.Name ) AS DepartmentAverages WHERE AvgSalary > 60000;
            

Key Querying Concepts

Common Operators

Comparison: `=`, `<>`, `<`, `>`, `<=`, `>=`
Logical: `AND`, `OR`, `NOT`, `BETWEEN`, `LIKE`, `IN`, `IS NULL`

Keywords Summary

SELECT: Specifies the columns to retrieve.
FROM: Specifies the table(s) to retrieve data from.
WHERE: Filters rows based on conditions.
ORDER BY: Sorts the result set.
TOP: Limits the number of rows (T-SQL specific).
GROUP BY: Groups rows with the same values into summary rows.
HAVING: Filters groups based on conditions.