SQL Querying with Transact-SQL

The SELECT Statement

The SELECT statement is the fundamental command used to retrieve data from one or more tables in a database. It allows you to specify which columns and rows to return.

Syntax

SELECT [ALL | DISTINCT]
    <column_expression> [AS <alias>] [, ...]
FROM
    <table_source>
[WHERE
    <filter_condition>]
[GROUP BY
    <group_by_expression> [, ...]]
[HAVING
    <group_filter_condition>]
[ORDER BY
    <sort_expression> [ASC | DESC] [, ...]]
[OFFSET {<skip_rows>} ROWS
FETCH NEXT {<return_rows>} ROWS ONLY];

Key Components:

• SELECT: Specifies the columns to retrieve. Use * to select all columns.
• DISTINCT: Eliminates duplicate rows from the result set.
• FROM: Identifies the table(s) from which to retrieve data.
• WHERE: Filters rows based on specified conditions.
• GROUP BY: Groups rows that have the same values in specified columns into summary rows.
• HAVING: Filters groups based on specified conditions, typically used with aggregate functions.
• ORDER BY: Sorts the result set based on one or more columns.
• OFFSET/FETCH: Used for implementing pagination (supported in SQL Server 2012+).

SELECT
    ProductName,
    UnitPrice
FROM
    Production.Product
WHERE
    ListPrice > 50
ORDER BY
    ListPrice DESC;

Filtering Data with the WHERE Clause

The WHERE clause is used to extract only those records that fulfill specified criteria. You can use comparison operators, logical operators, and other conditions.

Common Operators:

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

SELECT
    SalesOrderID,
    OrderDate
FROM
    Sales.SalesOrderHeader
WHERE
    OrderDate BETWEEN '2023-01-01' AND '2023-03-31';

SELECT
    ProductName
FROM
    Production.Product
WHERE
    ProductName LIKE 'Ch%';

Joining Tables

JOIN clauses are used to combine rows from two or more tables based on a related column between them. This is crucial for relational database querying.

Types of Joins:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If no match is found, the result is NULL on the right side.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. If no match is found, the result is NULL on the left side.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either the left or the right table.
• CROSS JOIN: Returns the Cartesian product of the rows from the tables involved.

SELECT
    c.FirstName,
    c.LastName,
    soh.SalesOrderID,
    soh.OrderDate
FROM
    Sales.Customer AS c
INNER JOIN
    Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
WHERE
    c.CustomerID = 123;

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause.

Common Aggregate Functions:

• COUNT(): Counts the number of rows or non-NULL values.
• SUM(): Calculates the sum of values.
• AVG(): Computes the average of values.
• MIN(): Finds the minimum value.
• MAX(): Finds the maximum value.

SELECT
    pc.Name AS CategoryName,
    COUNT(p.ProductID) AS NumberOfProducts,
    AVG(p.ListPrice) AS AveragePrice
FROM
    Production.Product AS p
JOIN
    Production.ProductCategory AS pc ON p.ProductCategoryID = pc.ProductCategoryID
GROUP BY
    pc.Name
HAVING
    COUNT(p.ProductID) > 10
ORDER BY
    NumberOfProducts DESC;